MySQL access rights system

See a lot of users about MySQL login server problems, including some in PHP call MySQL occurs when the MySQL database server can not be logged into the problem, thought is php out of the question. In fact, the issue of MySQL permissions.

MySQL's permission system in the MySQL manual is a very long chapter, I printed it out a full print more than 20 pages! Here I have a brief understanding of it, I hope to be able to just contact with the comrades in MySQL a little help, there are wrong place, also asked comrades to point out.

After I understand the MySQL authority mechanism, can't help admiring its strict and ingenious; maybe all the database systems are like this, but other large databases do not require the Super administrator to intervene in the data sheet in person.

MySQL permissions are stored in the database named MySQL, there are user, DB, host, Tables_priv, Columns_priv, and so on five tables.

First of all, the user's login is restricted, and the most commonly used are the three fields, users, host, and password. Other Select_priv, Update_priv 、...... Each of these fields indicates whether the user has a SELECT, update 、...... , these fields are set to ' Y ' to indicate that the user has the corresponding permission, ' N ' indicates that the user does not have the appropriate permissions. Note that the permissions specified here are global, and once you give a user select or UPDATE permission in the Users table, he or she has these permissions on any database, any table on this server! Of course, including MySQL database!! This means that he can illegally get greater permissions by changing the data in the user table!!! This is very scary, so it is recommended that you do not assign permissions in the user table except to the root user. In particular, you can create a user who has almost the same permissions as root (the password is not to tell others!). is used when you forget the root password. When you add a user, if you do not specify the value of the Permission field, their default value is ' N ', which is the user has no permissions-you can safely add users to the user table, because even if he can log in, there is nothing to do.

Another problem that should be noted is the password field in the user table. Just imagine, since the root can browse the MySQL database, you can see the User table password field, is not to see the other user's password? No! The password field in the user table holds the user's password encrypted with the password () function, and when the user logs on, the server will receive the user input password with the password () function, and the encrypted string and the User table password field can match, The password is considered correct. Password () has no inverse, so no one can get the plaintext of the password from an encrypted string. Also, if you manually modify the user table, do not forget to encrypt the password () function when updating the password field. For example, if you want to allow a user named Bill to log on to your server, and you set him a 12345 password, you should:

Insert into User (User,host,password) VALUES (' Bill ', '% ', password (' 12345 '));

If you directly

Insert into User (User,host,password) VALUES (' Bill ', '% ', ' 12345 ');

, I am afraid he can not login to your server, will also go to the Osso on the Internet to send a post to ask.

Note that when you manually manipulate the data table associated with the permission, you perform a flush privileges command to make it effective.

The question below is how to assign permissions to the user. If you are going to open a database for a user and have all or part of the permissions on the database open to him, this is the DB table. The value of the DB table is that when a user requests a query, detects if the user has permission to perform the query on the database to which the query is directed, and if so, if so, then consult the Tables_priv table for further enquiries. The most frequently---commonly---frequently----frequently-----, DB User, needless to say, is the username of that user, corresponding to the Users table, DB, which is the name of the database to be assigned to him. Then set the permission field corresponding to his permissions to ' Y '. Similarly, the values of these fields are ' N ' by default when unspecified. You can also assign permissions to users using the Grant/revoke command, which can be:

Grant Select,update,insert,delete,creater,alter,drop,index on bill.* to Bill;

This gives the user bill the almost all permissions on the bill's database. There is no grant permission here, and it is not recommended to be easy to give to someone because the user has grant permissions and can assign permissions to other users. Thankfully, a user with grant privileges can and only assign the permissions he already has to others.

After you use the Grant/revoke command to change permissions, you do not have to perform the flush privileges command to make the changes take effect.

The above discussion is to give a user completely open a database problem, if only want to give a user a specific table of permissions, is the Tables_priv table to play a role. The key fields Here are user, DB, table_name. Obviously, to give a user permission to specify a particular table in a particular database, three key elements are: which user, which database (DB), which table (table_name). The mechanism is similar to the DB table, and I don't have to repeat it. The only difference is that a set-type field Table_priv is used to specify the user's permissions on this table, and the members of the set have SELECT, UPDATE, INSERT, DELETE, ALTER, CREATE, DROP, GRANT, INDEX, REF Erence and so on, you can select any one or several of them to assign to the user.

In the list of permissions mentioned above and in the host table not mentioned, there is a host field that distinguishes between people who may be the same as the user name from different hosts, or gives different permissions to the same user when connecting to a different host. This usage is not very common, but for the sake of security, it is recommended that the root user, if you do not need to connect from a remote connection, set his host to localhost, while others can be set to%, that is, any host.