目录
mysql8.0才有角色的概念
1.创建角色并赋权限
- create role senior_dba,app_dev;
- grant all on *.* to senior_dba with grant option;
- grant select,insert,update,delete on python_mysql to app_dev;
(root@localhost) [(none)]> create role senior_dba,app_dev;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [(none)]> grant all on *.* to senior_dba with grant option;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> grant select,insert,update,delete on python_mysql.* to app_dev;
Query OK, 0 rows affected (0.00 sec)
2.角色赋给用户
- grant senior_dba to zhangsan;
(root@localhost) [(none)]> create user 'zhangsan'@'%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]> grant senior_dba to zhangsan;
Query OK, 0 rows affected (0.00 sec)
3.查看用户权限
- show grants for zhangsan;
- show grants for zhangsan using senior_dba;
(root@localhost) [(none)]> show grants for zhangsan;
+------------------------------------------+
| Grants for zhangsan@% |
+------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`%` |
| GRANT `senior_dba`@`%` TO `zhangsan`@`%` |
+------------------------------------------+
2 rows in set (0.00 sec)
(root@localhost) [(none)]> show grants for zhangsan using senior_dba;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `zhangsan`@`%` WITH GRANT OPTION |
| 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,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `zhangsan`@`%` WITH GRANT OPTION |
| GRANT `senior_dba`@`%` TO `zhangsan`@`%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)