mysql角色权限和菜单怎么设计 mysql角色创建角色

转载

架构魔法之光 2023-07-06 22:28:51

文章标签 mysql 用户信息表设计 mysql 数据库服务器 文章分类 MySQL 数据库

前言

角色（Role），可以认为是一些权限的集合，一直是存在各个数据库中，比如Oracle、SQL Server、OceanBase等，MySQL 自从 8.0 release 才引入角色这个概念。

为用户赋予统一的角色，即把一个带有某些权限集合的角色分配给一个用户，那该用户就拥有了该角色所包含的所有权限，权限的修改直接通过角色来进行，无需为每个用户单独授权，大大的方便了权限管理。

一、回顾MySQL 8.0之前的用户管理

1、创建用户(默认缺省)

从 CREATE USER 的 help 语法解释中发现，其不仅可以创建新的 MySQL 帐户，还支持为新帐户建立身份验证、SSL/TLS、资源限制和密码管理属性，并控制帐户最初是锁定还是解锁。

那么，缺省选项默认创建的用户是怎么样的呢？

mysql> CREATE USER kuzma IDENTIFIED BY 'iamkuzma';
Query OK, 0 rows affected (0.07 sec)

mysql> select * from mysql.user where User="kuzma"\G
*************************** 1. row ***************************
                  Host: %
                  User: kuzma
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 0x
           x509_issuer: 0x
          x509_subject: 0x
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *E2D9547BC3A2AD2EEC6BE9636489AC973656EEB9
      password_expired: N
 password_last_changed: 2022-10-05 17:41:06
     password_lifetime: NULL
        account_locked: N
1 row in set (0.06 sec)

如上，在 CREATE USER 后会在 mysql.user 系统表中注册相关用户信息，未指定的属性设置为其默认值：

帐户名的主机名部分，如果省略，则默认为'%'，即 'kuzma'@'%'，表示不限主机连接
Authentication：系统变量定义的认证插件 default_authentication_plugin = mysql_native_password ，空凭证
SSL/TLS：NONE
资源限制：无限制，max_questions最大查询数、max_updates最大更新数等都为0（默认值），即未做限制
密码管理：PASSWORD EXPIRE DEFAULT
密码过期：NONE，未设定，即永不过期
账户锁定：ACCOUNT UNLOCK，未锁定

首次创建的帐户没有权限，xxx_priv都为NONE，默认未设置用户权限，若需要分配权限，另外的通过 GRANT 语句为用户授权。

若要删除用户，DROP USER 'kuzma'@'%'; 注意指定用户名和主机名。

2、带选项创建用户

2.1、设置密码过期

# 将密码标记为过期，用户在第一次连接到服务器时必须选择一个新密码
CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE;
# 默认过期时间，应用 default_password_lifetime 系统变量指定的全局过期策略
CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE DEFAULT;
# 禁用密码过期，永不过期
CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE NEVER;
# 设定过期时间，每 180 天选择一个新密码
CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;

2.2、身份验证及SSL/TLS

# 指定身份验证插件，及明文密码值
CREATE USER 'kuzma'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'iamkuzma';

# CREATE USER 时指定 tls_option 值
# 注：生产数据库通常不会做加密连接，减少不必要的连接限制
tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}
# 默认缺省为REQUIRE NONE，即没有 SSL 或 X.509 要求，如果用户名和密码有效，则允许未加密的连接
CREATE USER 'kuzma'@'localhost' REQUIRE NONE;
# 加密连接限制，如果客户端无法建立安全连接，则连接尝试失败
CREATE USER 'kuzma'@'localhost' REQUIRE SSL;

2.3、资源限制

resource_option: {
    MAX_QUERIES_PER_HOUR count        # 允许该用户每小时多少查询
  | MAX_UPDATES_PER_HOUR count        # 允许该用户每小时多少更新
  | MAX_CONNECTIONS_PER_HOUR count    # 允许该用户每小时多少连接到服务器
  | MAX_USER_CONNECTIONS count        # 限制该用户同时连接到服务器的最大数量
}
# 限制用户每小时的查询和更新数
CREATE USER 'kuzma'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

如果count为0（默认值），表示该用户没有限制。通常生产用户不会在数据库层做资源限制，但某些特殊用户特殊场景下，可通过进行资源限制实现限流。

2.4、账户锁定

CREATE USER 和 ALTER USER 中使用 ACCOUNT LOCK 和 ACCOUNT UNLOCK 选项支持用户的锁定和解锁；

常见场景：数据库、业务迁移，用户切新改造等，通过对用户的锁定和解锁，实现对业务连接控制。

3、授权用户

使用 GRANT 给用户授权，REVOKE 给用户撤权（需要操作者拥有 GRANT OPTION 权限）。支持用户对库、表、列、存储过程、代理用户

# 授权[kuzma_write]用户{查询、插入、更新、删除}所有库表(*表示所有，db1.*表示db1库下的所有表)
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO kuzma_write;
# 撤销[kuzma_write]用户的删除权限
REVOKE DELETE ON *.* FROM 'kuzma_write'@'%';

# 设置列权限-----虽然但是有时业务还就是有这样的需求
# 1.列权限类型有且仅包括INSERT、REFERENCES(创建外键约束)、SELECT 和 UPDATE，权限后括号内给定表中的一个或多个列
# 2.列权限存储在 mysql.columns_priv，其中的Column_priv列是一个枚举列，亦可说明为什么列权限"有且仅"4种权限
GRANT SELECT (col1), INSERT (col1, col2) ON db1.tbl2 TO 'xxx_user'@'xxx_host';

# 授权存储过程的创建和执行
GRANT CREATE ROUTINE ON db1.* TO 'xxx_user'@'xxx_host';
GRANT EXECUTE ON PROCEDURE db1.proc2 TO 'xxx_user'@'xxx_host';

# 代理用户
# 该PROXY权限使一个用户可以成为另一个用户的代理，代理用户冒充或冒用被代理用户的身份，实现权限的复制
GRANT PROXY ON 'localuser'@'localhost' TO 'xxx_user'@'xxx_host';

权限元数据存储位置：

权限类型	元数据存储-系统表
用户全局权限	mysql.user
数据库（DB）权限	mysql.db
表（table）权限	mysql.tables_priv
列（columns）权限	mysql.columns_priv
routine-level（procedures and functions）权限	mysql.procs_priv
代理（proxy）用户权限	mysql.proxies_priv

Tips：值得注意的是，在某些情况下，无论是 CREATE|ALTER USER 还是 GRANT 都可能会记录在服务器日志中或客户端的历史文件中，例如 ~/.mysql_history，这就意味着任何有权读取该信息的人都可以读取明文密码，这不就暴露了吗。

二、瞅瞅其他数据库的用户角色管理

1、SQL Server的用户角色

服务器级别角色：

全局权限，只登入名（如超级管理员的登录名是sa）拥有；
服务器角色是固定的（包括：sysadmin、setupadmin、serveradmin等），用户无法创建服务器角色，一般也不建议给用户直接分配服务器角色。

数据库级别角色：

作用域为数据库，拥有者可以是用户也可以是数据库角色本身，可以理解数据库角色就是一组相同权限的用户；
两种类型的数据库级角色：数据库中预定义的“固定数据库角色（fixed-database roles）”（包括：db_owner、db_ddladmin、db_datawriter等）和可以创建的“用户定义数据库角色（user-defined database roles）”。
请不要将用户定义的数据库角色添加为固定角色的成员，否则会导致意外的权限升级。

应用程序角色：

一个数据库级主体，使应用程序能够用其自身的、类似用户的权限来运行，可以只允许通过特定应用程序连接的用户访问特定数据；
与数据库角色不同的是，应用程序角色默认情况下不包含任何成员，而且是非活动的；
默认应用程序角色无法访问服务器级元数据，因为它们不与服务器级主体关联，通过设置全局标志 4616 可放开限制。

用户：

数据库用户必须绑定具体的登入名，也可以在新建登入名的时候绑定此登入名拥有的数据库，默认就创建了此登入名同名的数据库用户，登入名与数据库用户之间就存在关联关系；
数据库用户是架构和数据库角色的拥有者，即可以将某个架构分配给用户，那么该用户就拥有了该架构所包含的对象；也可以将某个数据库角色分配给用户，此用户就拥有该数据库角色的权限。

# 创建登录名[stu]，并为登录名指定用户[student_user]
create user student_user for login stu;
# 创建角色[student_role]并为角色授权
create role student_role;
grant select, update, insert on db_book to student_role;
# 将角色授予用户:student_role授予student_user
exec sp_addrolemember 
@rolename='student_role', 
@membername='student_user';

2、OceanBase的用户角色

角色管理：

角色是为用户赋予各种权限的一种对象，为多种权限的集合，使用角色可将一组具有相同权限的用户组织在一起；
内置角色：ODC（OceanBase Developer Center，OB开发者中心）中内置了管理员角色 system_admin ，该角色被自动赋予给管理员用户 admin ，该角色可进行系统有关的所有操作且默认可访问所有的公共资源和个人资源，该角色不支持被编辑、删除和停用；
自定义新建角色：通过 ODC 新建角色，可支持设置公共资源权限和个人资源权限。

用户管理：

内置用户：ODC 中已内置一个管理员账户 admin ，该账户被自动设置为了管理员角色 system_admin ，所以该账户可进行系统有关的所有操作且默认可访问所有的公共资源，该账户不支持被编辑、删除和停用；
自定义新建用户：新建用户操作包括指定用户信息、为新建用户指定角色等，同时可在 ODC 中对用户进行查看、编辑等相关管理操作。

三、再看MySQL 8.0的用户角色管理

1、创建角色并授权

# 1.创建角色：创建三个角色，分别作为[app_db]的owner、read、write角色
CREATE ROLE 'app_owner', 'app_read', 'app_write';

# 2.角色授权：按功能分别给不同角色授权
GRANT ALL ON app_db.* TO 'app_owner';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

# 3.创建用户：根据业务需求创建三个用户
CREATE USER admin_user IDENTIFIED BY 'xxx_pass1';
CREATE USER read_user1 IDENTIFIED BY 'xxx_pass2';
CREATE USER read_user2 IDENTIFIED BY 'xxx_pass3';
CREATE USER rw_user1 IDENTIFIED BY 'xxx_pass4';

# 4.用户分配角色：对应的为用户授予角色
GRANT 'app_owner' TO admin_user;
GRANT 'app_read' TO read_user1, read_user2;
# 授权读和写角色，即授权rw_user1用户读写权限 ---> 一个用户可以拥有多个角色，也就拥有这多个角色的权限集合
GRANT 'app_read', 'app_write' TO rw_user1;

# 'app_owner', 'app_read', 'app_write' 三个角色在 mysql.user 中 password_expired、account_locked 都是 Y ，说明角色在 user 表作为"角色"的存在；
# mysql.db 中 Db: app_db 的权限划分，是按照三个角色来的(即角色授权)，并没有因为用户分配了角色之后又给到用户
mysql> show grants for app_read;
+----------------------------------------------+
| Grants for app_read@%                        |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%`         |
| GRANT SELECT ON `app_db`.* TO `app_read`@`%` |
+----------------------------------------------+
2 rows in set (0.07 sec)

mysql> show grants for rw_user1;
+--------------------------------------------------------+
| Grants for rw_user1@%                                  |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`%`                   |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`%` |
+--------------------------------------------------------+
2 rows in set (0.06 sec)

这就结束了？

# 使用 [read_user1] 用户登录
$ mysql -u read_user1 -p
# 并没有已授权的[app_db]
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
# 当前也没有任何角色生效
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

当然，没有结束，默认情况前面创建的角色并未被激活，最后一步是还需要“激活角色”：

# 5.激活角色：即设置用户的默认角色，前提是用户有分配角色(前第4步)
# 设置用户 [admin_user] 登录的默认角色是 {app_owner}
SET DEFAULT ROLE 'app_owner' TO admin_user;
# 一步到位：SET DEFAULT ROLE ALL，指的是将用户所有的角色都设置为默认角色
SET DEFAULT ROLE ALL TO admin_user, read_user1, read_user2, rw_user1;

# 查看默认角色设置
mysql> select * from mysql.default_roles;
+------+------------+-------------------+-------------------+
| HOST | USER       | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+------------+-------------------+-------------------+
| %    | admin_user | %                 | app_owner         |
| %    | read_user1 | %                 | app_read          |
| %    | read_user2 | %                 | app_read          |
| %    | rw_user1   | %                 | app_read          |
| %    | rw_user1   | %                 | app_write         |
+------+------------+-------------------+-------------------+
5 rows in set (0.06 sec)

# 若要删除默认角色设置：SET DEFAULT ROLE none TO user_name;
# 另一种激活角色方式：通过设置 activate_all_roles_on_login = ON，实现用户登录时自动激活角色

2、会话间的角色互换

拥有多角色的用户，在当前会话里可以进行角色互换：SET ROLE role_name;

$ mysql-u rw_user1 -p
# 如前设置，激活默认是全部角色
mysql> select current_role();
+--------------------------------+
| current_role()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+
1 row in set (0.05 sec)

# 切换到角色 {app_read}
mysql> set role app_read;
Query OK, 0 rows affected (0.05 sec)
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `app_read`@`%` |
+----------------+
1 row in set (0.06 sec)
# app_read 角色是只读权限，insert操作是被拒绝的
mysql> insert into app_db.t1(id) values(1);
ERROR 1142 (42000): INSERT command denied to user 'rw_user1'@'10.99.17.133' for table 't1'

# 切换到角色 {app_write}
mysql> set role app_write;
Query OK, 0 rows affected (0.05 sec)
mysql> select current_role();
+-----------------+
| current_role()  |
+-----------------+
| `app_write`@`%` |
+-----------------+
1 row in set (0.05 sec)
# 切换到 app_write 角色是有插入权限，insert成功
mysql> insert into app_db.t1(id) values(1);
Query OK, 1 row affected (0.10 sec)

3、何为强制角色

mandatory_roles：强制所有用户默认角色，可以通过在 mandatory_roles 系统变量的值中命名角色，可以将角色指定为强制角色，服务器将强制角色视为授予所有用户，因此无需明确授予任何帐户。

# 设置强制给所有用户赋予角色{app_read}
mysql> set global mandatory_roles = 'app_read';
Query OK, 0 rows affected (0.06 sec)
# 顺便设置默认激活角色
mysql> set global activate_all_roles_on_login = on;
Query OK, 0 rows affected (0.06 sec)

# 创建新用户[kuzma]
mysql> CREATE USER kuzma IDENTIFIED BY 'xxx';
Query OK, 0 rows affected (0.06 sec)
# 用户[kuzma]登录
$ mysql -u kuzma -p
# 查看用户权限，是被挂上了"强制角色"{app_read}的
> show grants;
+-------------------------------------------+
| Grants for kuzma@%                        |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `kuzma`@`%`         |
| GRANT SELECT ON `app_db`.* TO `kuzma`@`%` |
| GRANT `app_read`@`%` TO `kuzma`@`%`       |
+-------------------------------------------+
3 rows in set (0.05 sec)

4、撤销/删除角色

# 撤销角色，从用户上拿掉某一角色，即同时的撤销该角色拥有的权限集合
REVOKE role_name FROM user_name;
# 回收角色上的授权(和旧时的用户回收权限类似)
REVOKE DELETE ON db_name.* FROM role_name;

# 删除角色
DROP ROLE role_name1, role_name2;

注意：无论是撤销，还是删除，都不能动 mandatory_roles 系统变量值中的强制角色。

5、用户也可以是个角色

没错，用户也可以是个角儿 ^_~，这是怎么回事儿呢？其实，就用户也可以当角色来用的：

# 8.0以前的MySQL GRANT语法中，用户权限授予到另一个用户上是违法的
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.06 sec)
mysql> CREATE USER user1, user2;
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT user1 TO user2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user1 TO user2' at line 1

# 8.0之后是实现了用户和角色可互换性
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.05 sec)
mysql> CREATE USER user1, user2;
Query OK, 0 rows affected (0.07 sec)
# 给用户[user1]授权
mysql> GRANT SELECT ON app_db.* TO user1;
Query OK, 0 rows affected (0.06 sec)
# 把[user1]的权限授予给[user2] ---> 未报错，语法通过的
mysql> GRANT user1 TO user2;
Query OK, 0 rows affected (0.06 sec)
# 查看[user2]的权限，可以看到是把[user1]用户作为一个角色来授予
mysql> SHOW GTANTS FOR user2;
+-----------------------------------+
| Grants for user2@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `user2`@`%` |
| GRANT `user1`@`%` TO `user2`@`%`  |
+-----------------------------------+
2 rows in set (0.05 sec)
# 查看[user2]的详细权限
mysql> SHOW GTANTS FOR user2 USING user1;
+-------------------------------------------+
| Grants for user2@%                        |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `user2`@`%`         |
| GRANT SELECT ON `app_db`.* TO `user2`@`%` |
| GRANT `user1`@`%` TO `user2`@`%`          |
+-------------------------------------------+
3 rows in set (0.05 sec)

由此可见，用户和角色是可以互换使用的。

角色和用户之间的一个区别是，CREATE ROLE 创建的授权标识符默认是锁定的（mysql.user 可查看），而 CREATE USER 创建的授权标识符默认是解锁的。但是，区别并不是不可变的，因为可以在创建角色或用户后锁定或解锁角色或用户。
权限角度来看，CREATE USER 是包含了 CREATE ROLE，也就是说，CREATE USER 即可以创建用户，也可以创建角色。