MySQL 角色

没有评论

MySQL

角色就是一个权限的集合。

角色名称

格式

  • ‘user_name’@’host_name’
    • 与账户名称类似
    • host_name 部分可以省略，默认为 %
    • user_name 和 host_name 可以不使用引号，除非包含特殊字符，如 - 或 %
    • user_name 不能为空

与账户名对比

  • 不同点
    • user 部分不能为空，所以不支持匿名角色
    • host 部分不支持通配符 %
    • host 部分不支持子网掩码
    • 账户名不允许为 CURRENT_USER()
  • 相同点
    • mysql.user 中，某行数据可以同时充当账户和角色名
      • 账户和角色信息都保存在 mysql.user 表中
    • 通常仅使用 user_name 部分指定角色名称，host_name 部分隐式的指定为 %

使用角色

功能列表

  • 创建和删除角色

    • CREATE ROLE
    • DROP ROLE
  • 授权和回收权限
    • GRANT
    • REVOKE
  • 显示账户和角色的分配情况
    • SHOW GRANTS
  • 指定默认角色
    • SET DEFAULT ROLE
  • 更改当前会话中的活动角色
    • SET ROLE
  • 显示当前会话中活动的角色
    • CURRENT_ROLE()
  • 定义强制性角色并激活
    • 系统变量 mandatory_roles 和 activate_all_roles_on_login

创建角色和授予权限

  • 创建角色 
    mysql> create role app_developer,app_read,app_writer;
    • 角色创建之后会被锁定 (角色锁定后，无法进行身份验证) ，没有密码，并且会分配一个默认的身份认证插件 
      mysql> select user,host,plugin,account_locked from mysql.user where host = '%';
+---------------+------+-----------------------+----------------+
| user          | host | plugin                | account_locked |
+---------------+------+-----------------------+----------------+
| app_developer | %    | caching_sha2_password | Y              |
| app_read      | %    | caching_sha2_password | Y              |
| app_writer    | %    | caching_sha2_password | Y              |
+---------------+------+-----------------------+----------------+
    • 角色的属性可以通过 ALTER USER 语句修改，但是需要 CREATE USER 权限

  • 分配权限

    mysql> grant all on zabbix.* to app_developer;
mysql> grant insert,update,delete on zabbix.* to app_write;
mysql> grant insert,update,delete on zabbix.* to app_writer;
    • 与账户分配权限的语法相同
  • 创建账户 
    mysql> create user dev1@localhost identified by 'dev1';
mysql> create user read_user1@localhost identified by 'read_user1';
mysql> create user read_user2@localhost identified by 'read_user2';
mysql> create user rw_user1@localhost identified by 'rw_user1';
  • 为账户分配角色 
    mysql> grant app_developer to dev1@localhost;
mysql> grant app_read to read_user1@localhost,read_user2@localhost;
mysql> grant app_writer to rw_user1@localhost;
    • 有 ON 的是分配权限
    • 没有 ON 的是分配角色
  • 检查角色权限 
    mysql> show grants for dev1@localhost;
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for dev1@localhost using app_developer;
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+
    • 使用 USING + 角色名称，可以显示角色的权限。

定义强制性角色

强制性角色，会被授予所有用户，无需明确指定账户。

  • 系统变量

    通过这两个系统变量配置的强制性角色，无法使用 show grants for 账户名 查看账户权限。

    • mandatory_roles
      • 指定强制性角色
      • 需要动态权限
        • ROLE_ADMIN
        • SYSTEM_VARIABLES_ADMIN
      • 限制
        • 此变量指定的强制性角色不能通过 REVOKE、DROP ROLE 或 DROP USER 收回
        • 此变量中的角色不能拥有 SYSTEM_USER 权限，否则会报错。
          • 这是为了避免将会话设为系统会话
        • 如果此变量指定的角色不在 mysql.user 系统表中，则不会将该角色授予用户。
          • 如果角色是后来创建的，还需要 FLUSH PRIVILEGES
    • activate_all_roles_on_login
      • 强制性角色，在激活前不会生效，需要使用此变量激活
      • 动态设置的角色是激活状态
      • 此外，还需将角色设置为默认角色
      • 默认是禁用状态。
  • 配置方法
    • 修改配置文件 /etc/my.cnf 
      [mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'
activate_all_roles_on_login                 = on
    • 持久化设置系统变量 
      SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';
set persist activate_all_roles_on_login=on;

激活角色

  • 查看角色的活动状态 
    mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
  • 设置默认激活角色
    • 设置方法
      • 动态设置连接时默认激活的角色 
        mysql> set default role all to dev1@localhost, read_user1@localhost, read_user2@localhost, rw_user1@localhost;
      • 启动时在配置文件中设置系统变量
        • activate_all_roles_on_login
    • 当使用 rw_user1 连接时，查看当前角色的活动状态 
      mysql> select current_role();
+------------------+
| current_role()   |
+------------------+
| `app_writer`@`%` |
+------------------+
  • 在会话中更改活动角色

    为 rw_user1 赋予两个角色：app_read 和 app_writer，然后激活角色。

    mysql> grant app_read, app_writer to rw_user1@localhost;
mysql> set default role all to rw_user1@localhost;

    通过 SET ROLE 语句可以修改会话中的角色状态。

    mysql> select current_role();
+---------------------------------+
| current_role()                  |
+---------------------------------+
| `app_read`@`%`,`app_writer`@`%` |
+---------------------------------+

mysql> set role none; select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+

mysql> set role all except app_writer; select current_role();
+----------------+
| current_role() |
+----------------+
| `app_read`@`%` |
+----------------+

mysql> set role default; select current_role();
+---------------------------------+
| current_role()                  |
+---------------------------------+
| `app_read`@`%`,`app_writer`@`%` |
+---------------------------------+

回收角色和权限

  • 回收角色和权限 
    mysql> revoke app_read from read_user1@localhost;
mysql> revoke select on zabbix.* from app_read;
    • 系统变量 mandatory_roles 指定的强制性角色，不能通过 REVOKE、DROP ROLE 或 DROP USER 收回。
  • 验证 
    mysql> show grants for app_read;
+--------------------------------------+
| Grants for app_read@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%` |
+--------------------------------------+
  • 恢复角色的权限 (重新授权) 
    mysql> grant select on zabbix.* to app_read;
mysql> show grants for app_read;
+----------------------------------------------+
| Grants for app_read@%                        |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%`         |
| GRANT SELECT ON `zabbix`.* TO `app_read`@`%` |
+----------------------------------------------+

删除角色

mysql> drop role app_read,app_writer;
mysql> show grants for rw_user1@localhost;
+----------------------------------------------+
| Grants for rw_user1@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost` |
+----------------------------------------------+

Note: 删除角色后，角色对应的账户的权限也恢复默认状态。

  • 系统变量 mandatory_roles 指定的强制性角色，不能通过 REVOKE、DROP ROLE 或 DROP USER 收回。

用户和角色的互换性

  • 可以将用户/角色授予用户，或将用户/角色授予角色，概括来说，就是可以将用户看作角色 
    CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';
    • 查看 
      mysql> SHOW GRANTS FOR 'u2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for u2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`%`      |
| GRANT SELECT ON `db1`.* TO `u2`@`%` |
| GRANT SELECT ON `db2`.* TO `u2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `u2`@`%` |
+-------------------------------------+
mysql> SHOW GRANTS FOR 'r2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for r2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `r2`@`%`      |
| GRANT SELECT ON `db1`.* TO `r2`@`%` |
| GRANT SELECT ON `db2`.* TO `r2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` |
+-------------------------------------+
    • 将老的账户作为角色授予给其他用户 
      -- 创建账户
CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';

-- 修改密码
ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';

-- 锁定账户
ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;

-- 将老的账户作为角色授予其他账户
CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
  • 规范
    • 角色名加 r_ 前缀
    • 账户名加 u_ 前缀
发表于：MySQL
2021-05-18
# MySQL# 安全# 角色# 访问控制和账户管理
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-05-18发表，共计6467字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
常规查询日志
使用加密连接
MySQL 命令的选项
MySQL 提供的权限
评论（没有评论）