MySQL验证用户权限的方法

知识归纳
因为MySQL是使用User和Host两个字段来确定用户身份的，这样就带来一个问题，就是一个客户端到底属于哪个host。
如果一个客户端同时匹配几个Host，对用户的确定将按照下面的优先级来排

基本观点越精确的匹配越优先
Host列上，越是确定的Host越优先，[localhost, 192.168.1.1, wiki.yfang.cn] 优先于[192.168.%, %.yfang.cn]，优先于[192.%, %.cn]，优先于[%]
User列上，明确的username优先于空username。（空username匹配所有用户名，即匿名用户匹配所有用户）
Host列优先于User列考虑

当你登录mysql服务器之后，你可以使用user（）和current_user（）来检查你登陆的用户。

user（）返回你连接server时候指定的用户和主机
current_user（）返回在mysql.user表中匹配到的用户和主机，这将确定你在数据库中的权限

当你登录服务器并执行MySQL的命令时，系统将检查你当前的用户（current_user）是否有权限进行当前操作。

首先检查user表中的全局权限，如果满足条件，则执行操作
如果上面的失败，则检查mysql.db表中是否有满足条件的权限，如果满足，则执行操作
如果上面的失败，则检查mysql.table_priv和mysql.columns_priv（如果是存储过程操作则检查mysql.procs_priv），如果满足，则执行操作
如果以上检查均失败，则系统拒绝执行操作。

测试过程
创建3个用户名相同，HOST和权限都不同的USER

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）

从另外一个机器登陆过来

[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）

明确的user,host,进行精确匹配，找到用户为"bruce"@"10.20.0.232"
删除掉这个用户再登陆

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）

此时匹配的用户是bruce@%
然后把这个用户也删除，再登陆

[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）

此时匹配的是""@"%" 用户
对于空用户，默认有对test或test开头的数据库有权限。
以上就是MySQL验证用户权限的方法，希望对大家的学习有所启发。