Mysql必读MySQL验证用户权限的方法

《Mysql必读MySQL验证用户权限的方法》要点：
本文介绍了Mysql必读MySQL验证用户权限的方法，希望对您有用。如果有疑问，可以联系我们。

MYSQL入门知识归纳

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入门当你登录mysql服务器之后,你可以使用user()和current_user()来检查你登陆的用户.

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

MYSQL入门当你登录服务器并执行MySQL的命令时,系统将检查你当前的用户(current_user)是否有权限进行当前操作.

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

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

MYSQL入门
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)

MYSQL入门从另外一个机器登陆过来

MYSQL入门
[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)

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

MYSQL入门
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)

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

MYSQL入门
[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,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES,CREATE VIEW,SHOW VIEW,CREATEROUTINE,EVENT,TRIGGER ON `test`.* TO''@'%' |
| GRANT SELECT,CREATETEMPORARY TABLES,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)

MYSQL入门此时匹配的是''@'%' 用户

MYSQL入门对于空用户,默认有对test或test开头的数据库有权限.

MYSQL入门以上就是MySQL验证用户权限的方法,希望对大家的学习有所启发.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!