How can I manage users in MySQL? _ MySQL

How can I manage users in MySQL? MySQL has an advanced but non-standard security/authorization system. mastering its authorization mechanism is the first step to start operating MySQL databases. for a person familiar with basic SQL operations, it is also a hard part of all MySQL knowledge. This article explores the operating mechanism of its authorization system and hopes that you can better operate and use this excellent database system.

This article mainly references part of chapter 6 of the user manual attached to MySQL installation. If you have any questions, please contact me (lidong@wh.027.net)

1. what is the main role of the authorization mechanism?

The basic role of the authorization mechanism is to grant users on a host the select, insert, update, and detete permissions on a database. Its additional features also include whether to allow anonymous use of databases and some specific MysQL functions, such as load data infile. Note that the username in MySQL has nothing to do with the username in Unix. Although many client programs allow you to log on with the current user name, the most standard practice is to use the -- user option.
　
2. how does the authorization mechanism work?

In MySQL, the combination of hosts and users is regarded as a unique identifier. For example, the user lee on host 1 and host 2 is actually different, and their permission to use MySQL can also be different. The core issue of the entire authorization mechanism is to grant a user logging on from a host the permission to use a database. You can use the script mysqlaccess to test the database operation permissions of users on a host. All authorization information is stored in the user, host, and db tables of the database mysql. We can connect to this database through mysql instructions, and display the content in each data table through select * from user (or db, host. The permissions granted in the user table are the basic authorization of the entire authorization mechanism. that is to say, the user definition is applicable to any user + host unless otherwise defined in the db table, for users, it is best to authorize a database. The main purpose of the host table is to maintain a list of "secure" servers. When considering the permissions of a user/host on a database, we also need to study the matching search mechanism of the authorization mechanism:

Second, you can encrypt the password of a user in the authorization mechanism, and the password must be encrypted. the encryption method is password ('password'). If you enter the password directly, the database cannot be accessed. The permissions granted in the user table are the basic authorization of the entire authorization mechanism. that is to say, the user definition is applicable to any user + host unless otherwise defined in the db table, for users, it is best to authorize a database. The main purpose of the host table is to maintain a list of "secure" servers. When considering the permissions of a user/host on a database, we also need to study the matching search mechanism of the authorization mechanism:

First, we need to introduce the concept of a wildcard. the wildcard includes "%", which is an arbitrary (host, user, or database). If a record is empty, it also represents any meaning. Second, you can encrypt the password of a user in the authorization mechanism, and the password must be encrypted. the encryption method is password ('password'). If you enter the password directly, the database cannot be accessed.

From the display of these three tables, we can see that each record in these three tables contains a description of a user's authorization, data tables of several related authorization mechanisms in the MySQL database are searched in the order of user, db, and host. That is to say, we will first retrieve the user data table and find the first matched record. we will call the first matched record in the user data table Priv. then we will search the db table and obtain the corresponding authorization. If the host field in the corresponding record of the db data table is null and the host in the Priv record is also included in the host field of the host table, for a user, you can add some "Y" permission settings to the host table in the user table. If the host field in the db table is not empty, the user/host authorization will not be affected.

After understanding this, we need to discuss the priority of record search in each data table, that is, how to determine the first matching record, this is not determined by the natural sequence of records in the data table. The priority of each record in each data table is listed as follows:

(1) user table: determined based on the sequence of first host and then user. The search rules are as follows: records that do not contain the unified character, including the unified character, and empty records. In the same host, the rules are arranged according to the user.

(2) db table: the retrieval order is determined by the host field: records that do not contain the unified character, records that contain the unified character, and null records.

(3) host table: the retrieval order is determined by the host field: records that do not contain the unified character, records that contain the unified character, and null records. We use the following example to describe the matching search rules: Remember that if you change these data tables, you must use mysqladmin reload to make them take effect.

The following shows how the system performs a search:

+ ----------- + --------- +-
| Host | User |...
+ ----------- + --------- +-
| % | Root |...
| % | Jeffrey |...
| Localhost | root |...
| Localhost |...
+ ----------- + --------- +-

The search order should be:

Localhost/root
Localhost/any
Any/jeffrey
Any/root

In this way, if the localhost user jeffrey wants to connect to the database, therefore, its authorization should be based on the permissions specified by localhost/"arbitrary" rather than the permissions specified by "arbitrary"/jeffrey. Please note that, this is because the improper configuration may make it impossible for you to use the database system normally.
Now let's look at an example of adding a user: you need to add a user named "custom", which is from the host 'localhost', 'server. domain 'and 'whitehouse. gov 'The connection to the database, his password is "stupid", for the database 'bankaccount' he only wants to access from "localhost, the "customer" database should be accessed by the above three hosts. The following SQL statement is used to complete the operation.

Shell> mysql.
Mysql> insert into user (host, user, password)
Values ('localhost', 'custom', password ('stupid '));
Mysql> insert into user (host, user, password)
Values ('server. domain ', 'custom', password ('stupid '));
Mysql> insert into user (host, user, password)
Values ('whitehouse. gov ', 'custom', password ('stupid '));