How to verify user permissions in MySQL _ MySQL

This article describes how to verify user permissions in MySQL. For more information, see Knowledge induction

Because MySQL uses the "User" and "Host" fields to determine the User identity, the problem is the host that a client belongs.
If a client matches several hosts at the same time, the user's priority will be determined according to the following priority.

• The more accurate the basic opinion, the higher the priority.
• In the Host column, the more determined the Host, the more priority the [localhost, 192.168.1.1, wiki.yfang.cn] has over [192.168. %, % .yfang.cn], priority over [192. %, %. cn], with priority over [%]
• In the User column, clear usernames take precedence over empty usernames. (Empty username matches all usernames, that is, anonymous users match all users)
• The Host column takes precedence over the User column.

After you log on to the mysql server, you can use user () and current_user () to check your login users.

• User () returns the specified user and host when you connect to the server.
• Current_user () returns the matched users and hosts in the mysql. user table, which will determine your permissions in the database

When you log on to the server and execute MySQL commands, the system will check whether your current user (current_user) has the permission to perform the current operation.

• First, check the global permissions in the user table. if the conditions are met, perform the operation.
• If the above failure occurs, check whether the mysql. db table has the permission to meet the conditions. If yes, perform the operation.
• If the above fails, check mysql. table_priv and mysql. columns_priv (if it is a stored procedure operation, check mysql. procs_priv). If yes, execute the operation
• If all the above checks fail, the system rejects the operation.

Test process
Create three users with the same USER name and 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)

Log in from another machine

[root@brucetest7 ~]# mysql -ubruce -p -h10.20.0.231Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.5.20-log MySQL Community Server (GPL)This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome tomodify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for 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)

Specify the user and host for exact match and find the user as 'Bruce '@ '10. 255.0.20'
Delete this user and log on again

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.231Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.5.20-log MySQL Community Server (GPL)This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome tomodify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for 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)

The matched user is bruce @ %.
Delete the user and log on again.

[root@brucetest7 ~]# mysql -ubruce -p -h10.20.0.231Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.5.20-log MySQL Community Server (GPL)This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome tomodify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for 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 ON `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 matched user is ''@ '% '.

By default, empty users have permissions on databases starting with test or test.

The above is how MySQL authenticates user permissions. I hope you can learn more.