发布于 2016-03-21 07:35:53 | 163 次阅读 | 评论: 0 | 来源: 网友投递
Mysql关系型数据库管理系统
MySQL是一个开放源码的小型关联式数据库管理系统,开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
知识归纳
因为MySQL是使用User和Host两个字段来确定用户身份的,这样就带来一个问题,就是一个客户端到底属于哪个host。
如果一个客户端同时匹配几个Host,对用户的确定将按照下面的优先级来排
当你登录mysql服务器之后,你可以使用user()和current_user()来检查你登陆的用户。
当你登录服务器并执行MySQL的命令时,系统将检查你当前的用户(current_user)是否有权限进行当前操作。
测试过程
创建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验证用户权限的方法,希望对大家的学习有所启发。