知识归纳
因为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验证用户权限的方法，希望对大家的学习有所启发。