MySQL验证用户权限的方法

2024-03-26 02:25:04 43

知识归纳

因为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>grantselecton*.*to''@'%'identifiedby'123';
QueryOK,0rowsaffected(0.00sec)
mysql>grantselect,createon*.*to'bruce'@'10.20.0.232'identifiedby'123';
QueryOK,0rowsaffected(0.01sec)
mysql>grantselect,create,deleteon*.*to'bruce'@'%'identifiedby'123';
QueryOK,0rowsaffected(0.00sec)

从另外一个机器登陆过来

[root@brucetest7~]#mysql-ubruce-p-h10.20.0.231
Enterpassword:
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis5
Serverversion:5.5.20-logMySQLCommunityServer(GPL)
ThissoftwarecomeswithABSOLUTELYNOWARRANTY.Thisisfreesoftware,
andyouarewelcometomodifyandredistributeitundertheGPLv2license
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MySQL[(none)]>showgrants;
+-------------------------------------------------------------------------------------------------------------------------+
|Grantsforbruce@10.20.0.232|
+-------------------------------------------------------------------------------------------------------------------------+
|GRANTSELECT,CREATEON*.*TO'bruce'@'10.20.0.232'IDENTIFIEDBYPASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'|
+-------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)
MySQL[(none)]>selectuser(),current_user();
+-------------------+-------------------+
|user()|current_user()|
+-------------------+-------------------+
|bruce@10.20.0.232|bruce@10.20.0.232|
+-------------------+-------------------+
1rowinset(0.03sec)

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

mysql>deletefrommysql.userwhereuser='bruce'andhost='10.20.0.232';
QueryOK,1rowaffected(0.00sec)
mysql>flushprivileges;
QueryOK,0rowsaffected(0.00sec)

[root@brucetest7~]#mysql-ubruce-p-h10.20.0.231
Enterpassword:
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis6
Serverversion:5.5.20-logMySQLCommunityServer(GPL)
ThissoftwarecomeswithABSOLUTELYNOWARRANTY.Thisisfreesoftware,
andyouarewelcometomodifyandredistributeitundertheGPLv2license
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

MySQL[(none)]>showgrants;
+-----------------------------------------------------------------------------------------------------------------------+
|Grantsforbruce@%|
+-----------------------------------------------------------------------------------------------------------------------+
|GRANTSELECT,DELETE,CREATEON*.*TO'bruce'@'%'IDENTIFIEDBYPASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'|
+-----------------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)
MySQL[(none)]>selectuser(),current_user();
+-------------------+----------------+
|user()|current_user()|
+-------------------+----------------+
|bruce@10.20.0.232|bruce@%|
+-------------------+----------------+
1rowinset(0.00sec)

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

[root@brucetest7~]#mysql-ubruce-p-h10.20.0.231
Enterpassword:
WelcometotheMariaDBmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis8
Serverversion:5.5.20-logMySQLCommunityServer(GPL)
ThissoftwarecomeswithABSOLUTELYNOWARRANTY.Thisisfreesoftware,
andyouarewelcometomodifyandredistributeitundertheGPLv2license
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
MySQL[(none)]>showgrants;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Grantsfor@%|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|GRANTSELECTON*.*TO''@'%'IDENTIFIEDBYPASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'|
|GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATETEMPORARYTABLES,LOCKTABLES,CREATEVIEW,SHOWVIEW,CREATEROUTINE,EVENT,TRIGGERON`test`.*TO''@'%'|
|GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATETEMPORARYTABLES,LOCKTABLES,CREATEVIEW,SHOWVIEW,CREATEROUTINE,EVENT,TRIGGERON`test\_%`.*TO''@'%'|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
MySQL[(none)]>selectuser(),current_user();
+-------------------+----------------+
|user()|current_user()|
+-------------------+----------------+
|bruce@10.20.0.232|@%|
+-------------------+----------------+
1rowinset(0.00sec)

此时匹配的是''@'%'用户

对于空用户，默认有对test或test开头的数据库有权限。

以上就是MySQL验证用户权限的方法，希望对大家的学习有所启发。

MySQL验证用户权限的方法

热门推荐

随机推荐