MySQL-角色管理

• 角色就是权限的集合
• 方便管理相同权限的用户
• 恰当的权限设定，可以确保数据的安全性

1、创建角色

• 用户数量较多时，避免单独给每个用户授予多个权限，则可将权限集合放入角色中，再赋予用户相应的角色
• 语法：create role 'role_name'@'host_name/host_IP'; 如创建一个管理员角色：

mysql> create role 'manager'@'%';
Query OK, 0 rows affected (15.73 sec)

2、给角色赋予权限

• 创建角色后，默认当前创建的角色没有任何权限，需要手动给角色授权
• 语法：grant 权限.. on 数据库名.表名 to 'role_name'@'host_name/host_IP'; 例如下述将所有权限授予管理员角色

mysql> grant all privileges on *.* to 'manager'@'%';
Query OK, 0 rows affected (7.37 sec)

3、查看角色的权限

• 语法：show grants for 'role_name'@'host_name/host_IP',例如查看管理员角色所授予的权限：

mysql> show grants for 'manager'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for manager@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `manager`@`%`                                                                                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `manager`@`%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (1.95 sec)

4、回收角色的权限

• 角色授权后，可以对角色的权限进行维护，添加或撤销
• 撤销相关权限使用关键字 revoke
• 语法格式： revoke 权限... on '数据库'.'表名' from 'role_name'@'host_name/hostIP 例如：

mysql> revoke all privileges on *.* from 'manager';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
mysql> show grants for 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> GRANT SYSTEM_USER ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.61 sec)

mysql> revoke all privileges on *.* from 'manager';
Query OK, 0 rows affected (0.17 sec)

mysql> show grants for 'manager'@'%';
+-------------------------------------+
| Grants for manager@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

5、删除角色

• 对业务重新整合，可能涉及到对创建角色的清理，删除不在使用的角色
• 语法 ： drop role 'role_name' @'host_name/host_IP' 例如：

mysql> drop role 'manager'@'%';
Query OK, 0 rows affected (1.91 sec)

6、给用户赋予角色

• 创建角色并授予权限后，要将角色赋予用户并处于激活状态才会发挥作用
• 语法 ： grant role_name to user; 例如：

mysql> grant 'manager' to 'rqtanc1'@'192.168.%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.20 sec)
mysql> show grants for 'rqtanc1'@'192.168.%';
+----------------------------------------------+
| Grants for rqtanc1@192.168.%                 |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `rqtanc1`@`192.168.%`  |
| GRANT `manager`@`%` TO `rqtanc1`@`192.168.%` |
+----------------------------------------------+
2 rows in set (0.17 sec)

7、激活角色

• 方式1：使用set default role 'role_name'@'host_name/hostIP' to 'user_name'@'host_name/host_IP' 命令激活角色

mysql> set default role 'manager' to 'rqtanc1';
Query OK, 0 rows affected (0.05 sec)
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `manager`@`%`  |
+----------------+
1 row in set (0.00 sec)

• 方式2：将activate_all_roles_on_login 设置为 on

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (1.83 sec)

mysql> set global activate_all_roles_on_login =  on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

9、撤销用户的角色

• 语法：revoke 'role_name'@'host_name/host_IP' from 'user_name'@'host_name/host_IP 例如：

mysql> revoke 'manager' from 'rqtanc1';
Query OK, 0 rows affected (0.23 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.88 sec)

10、设置强制角色

• 强制角色是给每个创建的用户一个或多个默认角色，不需要手动设置。
• 强制角色无法被 revoke 和 drop
• 方式1：修改配置文件

[root@localhost ~]# vim /etc/my.cnf
#配置以下信息
[mysqld]
mandatory_roles = 'role_name'@'host_name/host_IP'

• 通过设置持久化的服务器参数的方式

# 系统重启后仍然有效
set persist mandatory_roles = 'role_name'@'host_name/host_IP'

• 通过设置全局的服务器参数的方式

#系统重启后失效
set global mandatory_roles = 'role_name'@'host_name/host_IP'