Mysql authentication method of user permissions _mysql

Knowledge induction

Because MySQL is using user and host two fields to determine the identity of users, this brings a problem, is a client exactly which host.
If a client matches several host at the same time, the user's determination is ranked according to the following priority

• The more accurate the basic point of view, the more preferred
• On the host column, the more specific host is preferred, [localhost, 192.168.1.1, wiki.yfang.cn] takes precedence over [192.168.%,%.yfang.cn], takes precedence over [192.%,%.cn], takes precedence over [%]
• On the user column, the clear username takes precedence over the empty username. (Null username matches all user names, that is, anonymous users match all users)
• The host column takes precedence over the user column

When you log on to the MySQL server, you can use User () and current_user () to check your login.

• User () returns the users and hosts you specified when you connected to the server
• Current_User () returns the users and hosts that are matched to the Mysql.user table, which determines your permissions in the database

When you log on to the server and execute the MySQL command, the system checks to see if your current user (Current_User) has permission to do the current operation.

• First check the global permissions in the user table and perform the action if the condition is met
• If the above fails, check that the Mysql.db table has the right to meet the criteria, and if so, perform the action
• If the above fails, check Mysql.table_priv and Mysql.columns_priv (check mysql.procs_priv if it is a stored procedure action), and if satisfied, perform the action
• If none of the above checks fail, the system refuses to perform the action.

Testing process
Create 3 user names with the same username with different host and permissions

Mysql> Grant Select on *.* to ' @ '% ' identified by ' 123 ';
Query OK, 0 rows Affected (0.00 sec)
mysql> Grant Select,createon *.* to ' Bruce ' @ ' 10.20.0.232 ' identified by ' 123 ';
Query OK, 0 rows affected (0.01 sec)
mysql> Grant Select,create,deleteon *.* to ' Bruce ' @ '% ' identified by ' 123 ' ;
Query OK, 0rows Affected (0.00 sec)

Landing from another machine.

[Root@brucetest7 ~]# mysql-ubruce-p-h10.20.0.231 Enter password:welcome to the MARIADB Monitor. Commands End With;
or \g. Your MySQL Connection ID is 5 server version:5.5.20-log mysql Community server (GPL) This software comes with absolutely NO WARRANTY. This are free software, and your are welcome tomodify and redistribute it under the GPL v2 license Type ' help; ' or ' \h ' Help.
Type ' \c ' to clear the current inputstatement.
MySQL [None]]> show grants; +-------------------------------------------------------------------------------------------------------------- -----------+
|
Grants for bruce@10.20.0.232 | +-------------------------------------------------------------------------------------------------------------- -----------+
|
GRANT SELECT, Createon *.* to ' Bruce ' @ ' 10.20.0.232 ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' | +-------------------------------------------------------------------------------------------------------------------------+ 1 Row Inset (0.00 sec) MySQL [None)]> Select User (), current_user (); +-------------------+-------------------+
| User () |
Current_User () | +-------------------+-------------------+
| bruce@10.20.0.232 |
bruce@10.20.0.232 |
 +-------------------+-------------------+ 1 row in Set (0.03 sec)

Clear User,host, exact match, find user for ' Bruce ' @ ' 10.20.0.232 '
Delete this user and then login to

mysql> Delete from mysql.userwhereuser= ' Bruce ' andhost= ' 10.20.0.232 ';
Query OK, 1row Affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) [root@brucetest7 ~]# mysql-ubruce-p-h10.20.0.231 Enter Password:welcome to the MARIADB Monitor. Commands End With;
or \g. Your MySQL Connection ID is 6 server version:5.5.20-log mysql Community server (GPL) This software comes with absolutely NO WARRANTY. This are free software, and your are welcome tomodify and redistribute it under the GPL v2 license Type ' help; ' or ' \h ' Help.

Type ' \c ' to clear the current inputstatement.
MySQL [(none)]>show grants; +-------------------------------------------------------------------------------------------------------------- ---------+
|
Grants for bruce@% | +-------------------------------------------------------------------------------------------------------------- ---------+
| GRANT SELECT, DELETE, createon*.* to ' Bruce ' @ '% ' identified BypasSword ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' | +--------------------------------------------------------------------------------------------------------------
---------+ 1 Row Inset (0.00 sec) MySQL [None)]> Select User (), current_user (); +-------------------+----------------+
| User () |
Current_User () | +-------------------+----------------+
| bruce@10.20.0.232 |
bruce@% |

 +-------------------+----------------+ 1 row in Set (0.00 sec)

At this point the user is bruce@%
and then delete the user, and then log on to the

[Root@brucetest7 ~]# mysql-ubruce-p-h10.20.0.231 Enter password:welcome to the MARIADB Monitor. Commands End With;
or \g. Your MySQL Connection ID is 8 server version:5.5.20-log mysql Community server (GPL) This software comes with absolutely NO WARRANTY. This are free software, and your are welcome tomodify and redistribute it under the GPL v2 license Type ' help; ' or ' \h ' Help.
Type ' \c ' to clear the current inputstatement.
MySQL [None]]> show grants; +-------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------+
|
Grants for @% | +-------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------+
| GRANT SELECT on*.* to ' @ '% ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' | |  GRANT SELECT, INSERT, UPDATE, DELETE, create, DROP, REFERENCES, INDEX, ALTER, create temporary tables, LOCK tables, create View, show view, Createroutine, EVENT, TRIGGER in ' test '. * to ' @ '% ' | | GRANT SELECT, INSERT, UPDATE, DELETE, create, DROP, REFERENCES, INDEX, ALTER, createtemporary tables, LOCK tables, create
View, show view, Createroutine, EVENT, TRIGGER on ' test\_% '. * to ' @ '% | +-------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------+ MySQL [(none)]>
Select User (), current_user (); +-------------------+----------------+
| User () |
Current_User () | +-------------------+----------------+
| bruce@10.20.0.232 |
@%    | +-------------------+----------------+ 1 row in Set (0.00 sec)

The ' @ '% ' user is matched at this time

For null users, the default has permissions on the database that begins with test or test.

The above is the MySQL authentication user authority method, hoped that has the inspiration to everybody's study.