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';
标签:roles,角色,管理,read,app,GRANT,ROLE,Mysql,localhost From: https://www.cnblogs.com/zmc60/p/17035589.html