MySQL验证用户权限的方法

时间:2021-05-23

知识归纳

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

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章