192.168.1.1-路由器设置 | 192.168.0.1-无线路由器设置

Linux学习笔记:MySQL的授权

发布时间:2016-01-18 09:17

 授权,当创建用户后,则需要对用户进行授权,MySQL有意思的地方在于,它是针对用户与主机成对进行授权。也就是说,同一个用户来自不同主机,权限也可能不同。

1
GRANT SELECT, INSERT ON `mydb`.`student` TO 'coosh'@'192.168.5.0/255.255.255.0' identified by 'coosh';
注意,SELECT,与INSERT之间有空格,另外IP网段的写法也要注意,不能使用/24这种写法。这种写法与192.168.5.%的区别在于,%匹配任意,也就是说192.168.5.%等同于192.168.5.0/255.255.255.0,不过如果子网掩码不是标准的,例如255.255.255.128,那么整个192.168.5.0就会被分割成两个网段,这时就要使用192.168.5.0/255.255.255.128这种写法,可以匹配到192.168.5.1-192.168.5.126
 
经过上面的授权命令后,查看一下mysql表
 
mysql> select user,host,password from mysql.user where user='coosh';
+-------+---------------------------+-------------------------------------------+
| user  | host                      | password                                  |
+-------+---------------------------+-------------------------------------------+
| coosh | 127.0.0.1                 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| coosh | 192.168.5.%               | *91A66B6C7D4FABDB9225374DC006BB98E6C6F0CB |
| coosh | 192.168.5.0/255.255.255.0 | *4747E0BBC01404D0D09BD8A9BA1AE4F5FC5FDC94 |
+-------+---------------------------+-------------------------------------------+
1
3 rows in set (0.00 sec)
可以看出coosh这个用户有多个不同的密码;而且经过测试,MySQL会优先使用指定了子网掩码的行。
 
透过LAN内另外一台机器用coosh这个用户去连接MySQL
 
mysql> insert into student
    -> values(1,'Mr Coosh',100);
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from student;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | Mr Coosh |   100 |
+----+----------+-------+
1 row in set (0.00 sec)
 
mysql> delete from student where id=1;
ERROR 1142 (42000): DELETE command denied to user 'coosh'@'192.168.5.141' for table 'student'
可以看到coosh可以插入一条新记录,也可以查询,却不能删除(提示命令被拒绝)
 
回到服务器上
 
[root@vmtest ~]# mysql -u root -p -h 127.0.0.1 -P 3308
Enter password: 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants for coosh@127.0.0.1;
+--------------------------------------------------------------------------------------------------------------+
| Grants for coosh@127.0.0.1                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'coosh'@'127.0.0.1' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'coosh'@'127.0.0.1'                                                      |
| GRANT ALL PRIVILEGES ON `test`.* TO 'coosh'@'127.0.0.1'                                                      |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
可以看到,如果在服务器本机使用coosh来登录,是具有全部的命令权限
测试一下
 
[root@vmtest ~]# mysql -ucoosh -p -h 127.0.0.1 -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> delete from mydb.student where id=1;
Query OK, 1 row affected (0.00 sec)
在服务器上登录则可以成功删除。
 
 
查看授权
 
mysql> show grants for coosh@127.0.0.1;
+--------------------------------------------------------------------------------------------------------------+
| Grants for coosh@127.0.0.1                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'coosh'@'127.0.0.1' IDENTIFIED BY PASSWORD '*1981B3D87F8E4997E444AC2F469A74D5145EEB75' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'coosh'@'127.0.0.1'                                                      |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> show grants for coosh@'192.168.5.0/255.255.255.0';
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for coosh@192.168.5.0/255.255.255.0                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'coosh'@'192.168.5.0/255.255.255.0' IDENTIFIED BY PASSWORD '*1981B3D87F8E4997E444AC2F469A74D5145EEB75' |
| GRANT SELECT, INSERT ON `mydb`.* TO 'coosh'@'192.168.5.0/255.255.255.0'                                                      |
+------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
关于我们 - RSS地图 - 最近更新 - 友情链接 - 网站地图 - 版权声明