1.Mysql角色
Mysql从8.0开始就支持roles,这个在Oracle可是一直存在的,然后Mysql终于在8.0上开始支持了。
2.什么是Mysql的roles?
Mysql的roles说直白就是一堆权限的集合,然后给这个集合称为roles,roles可以对账户分配一组特权,并提供一种方便授权个人特权的替代方案,然后roles特点:
- Roles可以被创建也可以被删除
- Roles可以被授权权限,也可以被撤销权限
- 可以向用户账号授权角色,可以从用户账户撤销角色
- 一个账户的active roles 可以从授予该账户的角色中选择,并且可以在该账户的会话期间更改
3. Roles的语法
3.1 创建和删除role
1.CREATE ROLE [IF NOT EXISTS] role [, role ] ...
e.g:
CREATE ROLE 'admin', 'developer';
CREATE ROLE 'webapp'@'localhost';
2. DROP ROLE [IF EXISTS] role [, role ] ...
e.g:
DROP ROLE 'admin', 'developer';
DROP ROLE 'webapp'@'localhost';
3.2 给role授权和撤销权限
1.GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';
2.REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';
3.3 SET DEFAULT ROLE
specifies which account roles are active by default.
SET DEFAULT ROLE 'admin', 'developer' TO 'joe'@'10.0.0.1';
3.4 SET ROLE
changes the active roles within the current session.
SET ROLE DEFAULT;
SET ROLE 'role1', 'role2';
SET ROLE ALL;
SET ROLE ALL EXCEPT 'role1', 'role2';
3.5 The CURRENT_ROLE() function displays the active roles within the current session
mysql> SELECT CURRENT_ROLE();
+-------------------+
| CURRENT_ROLE() |
+-------------------+
| `r1`@`%`,`r2`@`%` |
+-------------------+
mysql> SET ROLE 'r1'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `r1`@`%` |
+----------------+
4. 创建Role模版
1. 创建role
CREATE ROLE 'app_developer', 'app_read', 'app_write';
2. 为Role授权一些权限(集合)
GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
3.创建用户
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
4.为用户授权role
GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';