system_role
简介
在开启三权分立的模式下，在系统初始化时，创建三个用户分别为：系统管理员(DBA)，安全管理员(DSA)，审计管理员(DSA)，并默认为管理员分配对应的权限。使用caching_sha2_password密码策略，但是并没有密码，需要登录对应的用户设置密码。
| 用户 | 系统管理员 |
|---|---|
| SYSTEM_DBA@% | 系统管理员(DBA) |
| SYSTEM_DSA@% | 安全管理员(DSA) |
| SYSTEM_DAA@% | 审计管理员(DSA) |
开关
使用参数enable_system_role控制三权分立的生效，默认为OFF。
权限划分
| 权限 | 默认模式 | 三权 分立 -DBA | 三权 分立 -DSA | 三权 分立 -DAA | 说明 |
|---|---|---|---|---|---|
| 用户数据DML | 是 | 否 | 否 | 否 | I/U/D/R/M |
| 用户数据DQL | 是 | 否 | 否 | 否 | select |
| 用户数据DAL | 是 | 否 | 否 | 否 | explain/show/desc |
| DDL | 是 | 是 | 否 | 否 | Create/alter/drop table/view/index/trigger/function /procedure/package/pacage body |
| 账号管理 | 是 | 否 | 是 | 否 | create/alter/drop user/role、grant/revoke |
| 查看系统表和视图 | 是 | 是 | 否 | 否 | mysql/i_s/p_s/sys等 |
| 查看审计日志 | 是 | 否 | 否 | 是 | |
| Server和 process管理 | 是 | 是 | 否 | 否 | startup/shutdown show processlist/kill session |
| 备份/复制 | 是 | 是 | 否 | 否 | Clone，backup, replication |
| 管理系统变量 | 是 | 是 | 否 | 否 | SYSTEM_VARIABLES_ADMIN |
| 强制访问控制 | 是 | 否 | 是 | 否 | sys_mac |
权限限制
在三权分立的模式下
- 不能更改，系统管理员，安全管理员，审计管理员的默认权限。
- 不能对系统管理员，安全管理员，审计管理员进行设置角色。
- 不能将系统管理员，安全管理员，审计管理员进行授予。
测试
限制测试
-- login root@localhost
mysql> grant all on *.* to SYSTEM_DBA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> grant all on *.* to SYSTEM_DSA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> grant all on *.* to SYSTEM_DAA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> revoke all on *.* from SYSTEM_DBA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> revoke all on *.* from SYSTEM_DSA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> revoke all on *.* from SYSTEM_DAA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> drop user SYSTEM_DBA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> drop user SYSTEM_DSA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> drop user SYSTEM_DAA@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> set password for 'SYSTEM_DBA'@'%'= 'aaaaa';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> set password for 'SYSTEM_DSA'@'%'= 'aaaaa';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> set password for 'SYSTEM_DAA'@'%'= 'aaaaa';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> rename user 'SYSTEM_DBA'@'%' to 'SYSTEM_DDD'@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> rename user 'SYSTEM_DSA'@'%' to 'SYSTEM_DDD'@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> rename user 'SYSTEM_DAA'@'%' to 'SYSTEM_DDD'@'%';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> alter user 'SYSTEM_DBA'@'%' identified by 'aaaaaa';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> alter user 'SYSTEM_DSA'@'%' identified by 'aaaaaa';
ERROR 7634 (HY000): Access denied for cross system roles
mysql> alter user 'SYSTEM_DAA'@'%' identified by 'aaaaaa';
ERROR 7634 (HY000): Access denied for cross system roles
SYSTEM_DBA
-- login SYSTEM_DBA@%
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| SYSTEM_DBA@% |
+----------------+
1 row in set (0.00 sec)
mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for SYSTEM_DBA@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO `SYSTEM_DBA`@`%` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `SYSTEM_DBA`@`%` |
| GRANT SELECT ON `mysql`.* TO `SYSTEM_DBA`@`%` |
| GRANT SELECT ON `sys`.* TO `SYSTEM_DBA`@`%` |
| GRANT SELECT ON `information_schema`.* TO `SYSTEM_DBA`@`%` |
| GRANT SELECT ON `performance_schema`.* TO `SYSTEM_DBA`@`%` |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> create user user1;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql> call sys_mac.mac_create_policy('P1');
ERROR 1370 (42000): execute command denied to user 'SYSTEM_DBA'@'%' for routine 'sys_mac.mac_create_policy'
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> create table test.t1(c1 int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test.t1 values(1);
ERROR 1142 (42000): INSERT command denied to user 'SYSTEM_DBA'@'localhost' for table 't1'
mysql> select * from test.t1;
ERROR 1142 (42000): SELECT command denied to user 'SYSTEM_DBA'@'localhost' for table 't1'
mysql> delete from test.t1;
ERROR 1142 (42000): DELETE command denied to user 'SYSTEM_DBA'@'localhost' for table 't1'
mysql> update test.t1 set c1 = 10;
ERROR 1142 (42000): UPDATE command denied to user 'SYSTEM_DBA'@'localhost' for table 't1'
mysql> drop table test.t1;
Query OK, 0 rows affected (0.06 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.03 sec)
mysql> select count(*) from mysql.user limit 1;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from sys.innodb_lock_waits limit 1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from performance_schema.data_locks limit 1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from information_schema.innodb_trx limit 1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
SYSTEM_DSA
SYSTEM_DSA虽然没有很多权限，但可以直接授权任何权限。
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| SYSTEM_DSA@% |
+----------------+
1 row in set (0.00 sec)
mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for SYSTEM_DSA@% |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER, CREATE ROLE, DROP ROLE ON *.* TO `SYSTEM_DSA`@`%` |
| GRANT ROLE_ADMIN ON *.* TO `SYSTEM_DSA`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `sys_mac`.* TO `SYSTEM_DSA`@`%` |
+------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> create user user1;
Query OK, 0 rows affected (0.02 sec)
mysql> grant all on *.* to user1;
Query OK, 0 rows affected (0.03 sec)
mysql> revoke all on *.* from user1;
Query OK, 0 rows affected (0.02 sec)
mysql> drop user user1;
Query OK, 0 rows affected (0.02 sec)
mysql> call sys_mac.mac_create_policy('P10');
Query OK, 0 rows affected (0.03 sec)
mysql> select count(*) from mysql.user limit 1;
ERROR 1142 (42000): SELECT command denied to user 'SYSTEM_DSA'@'localhost' for table 'user'
mysql> select count(*) from sys.innodb_lock_waits limit 1;
ERROR 1142 (42000): SELECT command denied to user 'SYSTEM_DSA'@'localhost' for table 'innodb_lock_waits'
mysql> select count(*) from performance_schema.data_locks limit 1;
ERROR 1142 (42000): SELECT command denied to user 'SYSTEM_DSA'@'localhost' for table 'data_locks'
mysql> select count(*) from information_schema.innodb_trx limit 1;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
SYSTEM_DAA
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| SYSTEM_DAA@% |
+----------------+
1 row in set (0.01 sec)
mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for SYSTEM_DAA@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `SYSTEM_DAA`@`%` |
| GRANT SELECT, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `sys_audit`.* TO `SYSTEM_DAA`@`%` |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> create user user1;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql> create database test;
ERROR 1044 (42000): Access denied for user 'SYSTEM_DAA'@'%' to database 'test'
mysql> select * from sys_audit.audit_log limit 1;
Empty set (0.00 sec)
mysql> call sys_mac.mac_create_policy('P11');
ERROR 1370 (42000): execute command denied to user 'SYSTEM_DAA'@'%' for routine 'sys_mac.mac_create_policy'
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
