建立角色
文法:
CREATE ROLE role [, role]...
role同user一樣也由Name和Host這兩部分組成,其中:
- Name不能為空;
- Host需滿足如下規則:
-
- 必須是純IP位址,可以包含下劃線(_)和百分号(%),但這兩個符号僅代表2個普通字元,并不具備通配符意義;
- Host留白等于%,但也是精準比對,不具備通配符意義。
示例:
mysql> CREATE ROLE 'role_ro'@'%', 'role_write';
删除角色
DROP ROLE role [, role] ...
mysql> DROP ROLE 'role_ro'@'%';
授予角色
将權限授予角色
GRANT priv_type [, priv_type] ... ON priv_level TO role [, role]... [WITH GRANT OPTION]
mysql> GRANT ALL PRIVILEGES ON db1.* TO 'role_write';
将角色授予使用者
GRANT role [, role] ...
TO user_or_role [, user_or_role] ...
[WITH ADMIN OPTION]
說明:
- 執行該指令必須滿足如下條件的其中之一:
-
- 目前使用者有CREATE_USER權限;
- 目前使用者對Role有admin權限;
- 如果包含WITH ADMIN OPTION選項,則目标使用者對該Role擁有admin權限;
- 将角色授予使用者并不代表此使用者已擁有該角色下的權限,您還需要通過
語句和SET DEFAULT ROLE
語句為使用者設定需要激活的角色。SET ROLE
mysql> GRANT 'role_write' TO 'user1'@'127.0.0.1';
設定預設角色
SET DEFAULT ROLE
{NONE | ALL | role [, role ] ...}
TO user [, user ] ...
- 語句中所提到的Role已認證GRANT指令授予給目标使用者;
- 目前使用者為目标使用者,或目前使用者有CREATE_USER權限。
mysql> SET DEFAULT ROLE 'role_write' TO 'user1'@'127.0.0.1';
設定目前連接配接角色
SET ROLE {
DEFAULT
| NONE
| ALL
| ALL EXCEPT role [, role ] ...
| role [, role ] ...
}
說明
- 若選擇執行
,則目前激活的角色為SET ROLE DEFAULT
指令中選擇的角色;SET DEFAULT ROLE
- 通過該文法激活的角色僅對使用目前連接配接的使用者生效。
mysql> SET ROLE 'role_write';;
檢視角色權限
SHOW GRANTS
[FOR user_or_role
[USING role [, role] ...]]
mysql> SHOW GRANTS FOR 'role_write'@'%';
+---------------------------------------------------+
| GRANTS FOR 'ROLE_WRITE'@'%' |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'role_write'@'%' |
| GRANT ALL PRIVILEGES ON db1.* TO 'role_write'@'%' |
+---------------------------------------------------+
mysql> SHOW GRANTS FOR 'user1'@'127.0.0.1' USING 'role_write';
+------------------------------------------------------+
| GRANTS FOR 'USER1'@'127.0.0.1' |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'127.0.0.1' |
| GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'127.0.0.1' |
| GRANT 'role_write'@'%' TO 'user1'@'127.0.0.1' |
+------------------------------------------------------+
-- 以user1的會話執行
mysql> SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE() |
+------------------+
| 'role_write'@'%' |
+------------------+
回收角色
回收角色的權限
REVOKE priv_type [, priv_type] ... ON priv_level FROM role [, role]...
mysql> REVOKE ALL PRIVILEGES ON db1.* FROM 'role_write';
mysql> SHOW GRANTS FOR 'role_write'@'%';
+----------------------------------------+
| GRANTS FOR 'ROLE_WRITE'@'%' |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'role_write'@'%' |
+----------------------------------------+
回收使用者的權限
REVOKE role [, role ] ... FROM user_or_role [, user_or_role ] ...
mysql> SHOW GRANTS FOR 'user1'@'127.0.0.1';
+-----------------------------------------------+
| GRANTS FOR 'USER1'@'127.0.0.1' |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'127.0.0.1' |
| GRANT SELECT ON db1.* TO 'user1'@'127.0.0.1' |
| GRANT 'role_write'@'%' TO 'user1'@'127.0.0.1' |
+-----------------------------------------------+
mysql> REVOKE 'role_write' FROM 'user1'@'127.0.0.1';
mysql> SHOW GRANTS FOR 'user1'@'127.0.0.1';
+----------------------------------------------+
| GRANTS FOR 'USER1'@'127.0.0.1' |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'127.0.0.1' |
| GRANT SELECT ON db1.* TO 'user1'@'127.0.0.1' |
+----------------------------------------------+