圆月山庄资源网 Design By www.vgjia.com
知识归纳
因为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.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 is free software, and you are welcome tomodify and redistribute it under the GPL v2 license Type '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.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 is free software, and you are welcome tomodify and redistribute it under the GPL v2 license Type '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.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 is free software, and you are welcome tomodify and redistribute it under the GPL v2 license Type '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验证用户权限的方法,希望对大家的学习有所启发。
圆月山庄资源网 Design By www.vgjia.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
圆月山庄资源网 Design By www.vgjia.com
暂无评论...
更新日志
2024年12月23日
2024年12月23日
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]