MySQL Permissions control

1, MySQL permissions are, from somewhere to the user's permission to an object.

2, MySQL permissions to use the White list policy, specify what users can do, not specified can not do.

3, the permission check is divided into two steps:

A, can connect, check from where, user name and password, common error 1045 (28000): Access denied for user ...

B, can perform operations, granularity from coarse to fine, respectively: Mysql.user, Mysql.db, Msql.tables_priv, Mysql.columns_priv, Mysql.proc_priv. It is important to note that these tables have a division of labor, but there is a certain overlap in the allocation of permissions. It can be understood that MySQL first checks whether there is permission on a wide range, if it does not go to the small scope to check. For example: first check whether there is a SELECT permission on this database, if any, it is allowed to execute. If not, then check whether the table has SELECT permission, until the most granular, and no permissions, will refuse to execute. For example: To check whether Zhang San can control a regiment, I just need to check whether Zhang San can control an army, if possible, is the right, if not, then check Zhang San can control a division .... Therefore, the finer the granularity control, the more steps of the permission check, the worse the performance, need to consider.

4, after the start of the MySQL service, will be the permissions related to the table data read into memory, changes to the permissions will be effective immediately? To see the situation, manually modifying the table data requires flush privileges

5. Create user, modify password set password, notice that alter user just set password expires, can log in, but can not do anything, you must set a new password, delete user drop users

6, Host+user marked the only one user, that is, called Zhang San, from different places to Zhang San is two users, they have different permissions.

7, then the problem came, the table has two records: ' Root ' @ ' 192.168.1.101 ' and ' root ' @ '% ', now root to login, MySQL how to match it? Which root do you think it is?

MySQL sort the user, first sort the host, then the user sort, small range in front, large range in the back, from the top down to match.

8. Permission grant, grant permission on object to user @ Where to come identified by password

9, the revoke permission on the object from the user @ where to come, note that revoke must be corresponding to grant, that is, can only reclaim the permissions granted.

10, then the problem came, I grant Zhang San Select permissions, and now revoke all privileges can not recover the permissions of Zhang San Select, because there is no Zhang San grant all privileges, how to solve this problem?

Use revoke all privileges,grant option from user

11, permission level: from a host to a user, some of a table in a database some of the columns of the record, whether there is permission.

12, Global: The object is the MySQL service all the database, contains the service level management permission, for example showdown

13. Database: Object is a database

14. Table: Object is a table in the database

15. Column: Object is a column in a table, for example: Grant Select (name) on XXX to XXX

16, Program: objects are stored procedures and methods.

17, Information_scheme, databases and tables are stored data, then who will store the database and table this information? Information_scheme is the record database and table, it should be noted that Infromation_scheme does not have a corresponding physical file, it is maintained in memory MySQL.

18. Permission Setting principle:

A. Minimize permissions

b, according to business, separation of users, different business corresponding to different users

C, to avoid permission granularity is too thin, because the MySQL permission check, will affect performance.

19, file leaks, Linux under the MySQL client to perform operations recorded in the file ~/.mysql_history, output redirection/dev/null

20. What if I lose my password?

A, MySQL boot, add option to reset password

B, MySQL start, add options do not check permissions, login after the password change, quit restarting the boot.

MySQL Permissions control