MySQL access permission system

Many netizens asked questions about the failure to log on to the MySQL server, including the failure to log on to the MySQL database server when MySQL is called in PHP. It is actually a problem of MySQL permissions.

The MySQL permission system is a long chapter in the MySQL manual. I printed more than 20 pages! Here, I will briefly describe my understanding of it, hoping that it will be helpful to the comrades who have just been in contact with MySQL. If something is wrong, please point out it.

After I learned about MySQL's permission mechanism, I couldn't help but admire its closeness and cleverness. Maybe all database systems are like this, only other large databases do not need the super administrator to manually intervene in the data table.

MySQL permissions are stored in a database named mysql. There are five tables, including user, db, host, tables_priv, and columns_priv.

First, only the user table is restricted for user login. The most common fields are user, host, and password. Other select_priv, update_priv ,...... These fields indicate whether the user has select, update ,...... If these fields are set to 'y', the user has the corresponding permissions, and 'n' indicates that the user does not have the corresponding permissions. Note: The permissions specified here are global. Once you grant the select or update permission to a user in the user table, the user has the preceding permissions on any databases and tables on this server! Of course, this includes the mysql database !! This means that he can obtain more permissions illegally by changing the data in the user table !!! This is terrible, so we recommend that you do not assign permissions to the user table except for the root user. In particular, you can create a user with almost the same permissions as the root user (do not tell others the password !) You can use it when you forget the root password. When adding a user, if the value of the permission field is not specified, their default values are 'n ', -- that is, this user has no permissions -- you can add a user to the user table with peace of mind, because he cannot do anything even if he can log in.

Another issue worth attention is the password field in the user table. Imagine that since the root user can browse the mysql database and see the password field in the user table, does it mean to see the passwords of other users? No! The password field in the user table stores the user password encrypted by using the password () function. When a user logs on, the server encrypts the password entered by the user using the password () function, if the encrypted string matches the password field in the user table, the password is considered correct. Password () is not reversed, so no one can obtain the plaintext of the password from an encrypted string. At the same time, if you manually modify the user table, do not forget to use the password () function for encryption when updating the password field. For example, if you want to allow a user named bill to log on to your server and set a 12345 password for him, you should:

Insert into user (user, host, password) values ('bill ',' % ', password ('123 '));

If you directly

Insert into user (user, host, password) values ('bill ',' % ', '123 ');

I'm afraid he won't be able to log on to your server, and will post a question on Osso.

Note: After you manually operate a data table related to permissions, you need to run the flush privileges command to make it take effect.

The following question is how to assign permissions to users. If you want to open a database for a user and open all or part of the permissions for the database to the user, the db table is used. The significance of a db table is that when a user requests a query, it checks whether the user has the permission to perform the query operation on the database for which the query is targeted; if no, contact tables_priv. The most common fields in a db table are user, db, and the many permissions related fields. User, needless to say, is the user name, corresponding to the user table; db is the name of the database to be assigned. Then, set the permission field corresponding to the permission to be granted to him to 'y '. Similarly, when these field values are not specified, the default value is 'n '. You can also use the GRANT/REVOKE command to assign permissions to users, which can be as follows:

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

In this way, almost all permissions for the bill database are granted to the user bill. The grant permission is not given here. We recommend that you do not grant the permission to others because the user has the grant permission and can assign the permission to other users. Fortunately, a user with the grant permission can only assign his or her own permissions to others.

After you use the GRANT/REVOKE command to change the permission, you do not need to execute the flush privileges command to make the change take effect.

The above discussion is about completely opening a database for a user. If you only want to grant a user a specific table permission, it is time for the tables_priv table to play a role. The key fields here are user, db, and table_name. Obviously, to assign permissions to a specific table in a specific database to a user, the three key elements are: user and database) and the table (table_name ). Its mechanism is similar to that of db tables, so I don't have to repeat it. The only difference is that a SET field table_priv is used to specify the user's permissions to this table. The SET members include SELECT, UPDATE, INSERT, DELETE, ALTER, CREATE, DROP, GRANT, INDEX, REFERENCE, etc. You can select any one or more of them to be allocated to the user.

There is a host field in the several permission tables mentioned above and the host tables not mentioned above. It is used to distinguish persons with the same user names from different hosts, you can also grant different permissions to the same user when connecting from different hosts. 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 remotely connect, please set his host to localhost, others can be set to %, that is, any host.

Useful to me (3) useless to me (3)