数据库的权限管理一直是很麻烦的一件事情,因为权限是很分散的,不容易查看,也不容易修改的完善。但是MySQL的权限管理让人眼前一亮的感觉。因为什么都是那么一目了然。
1# 查看权限
比如,我们想要看看MySQL的root用户,拥有什么权限:
(root@localhost)[(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
(root@localhost)[(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
一条show grants解决了问题,并且连grant语句都给我们了。这样就是说,我们稍微修修改改就可用重造出另一个和root一样的超级用户了。
或者使用:
show grants for 'xxx'@'xxxxx';
其中第一条:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION
不光光grant 了 . 上的ALL PRIVILEGES 给'root'@'localhost' ,甚至还有验证密码和 WITH 信息。实际上这条语句是可用拿来创建这个用户的。这也是一个另类的创建用户的方法。
查看他人的权限:
(root@localhost)[(none)]> show grants for test1
-> ;
+------------------------------------------------------------------------------------------------------+
| Grants for test1@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这里看到用户'test1'@'%'只有一条权限,这条权限也是默认的创建用户语句。
2# 授予权限:
用户必然是需要使用数据库的。所以如果用户只有usage这个没用的权限的话,这个用户就不需要存在了。
语法:
(root@localhost)[(none)]> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user_specification #这个代理也是语句也是单独存在
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type: { #对象类型
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: { #权限的等级分类
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user_specificati