印刻数据库

外观

MySQL 角色和权限模型 ​

什么是角色和权限模型 ​

MySQL 角色和权限模型是用于管理数据库访问权限的系统，它定义了：

  • 用户：能够连接到数据库服务器的实体
  • 角色：一组权限的集合，可以授予用户或其他角色
  • 权限：允许用户或角色执行的操作，如 SELECT、INSERT、UPDATE 等
  • 对象：权限作用的目标，如表、视图、存储过程等
  • 权限检查：在执行操作前验证用户是否具有相应的权限

权限的分类 ​

1. 全局权限 ​

作用于整个 MySQL 服务器的权限，如：

  • ALL PRIVILEGES：所有权限
  • CREATE USER：创建用户的权限
  • SUPER：超级用户权限
  • RELOAD：重新加载配置的权限
  • SHUTDOWN：关闭服务器的权限
  • PROCESS：查看进程的权限
  • FILE：读写文件的权限

2. 数据库级权限 ​

作用于特定数据库的权限，如：

  • CREATE：创建数据库或表的权限
  • ALTER：修改数据库或表结构的权限
  • DROP：删除数据库或表的权限
  • INDEX：创建或删除索引的权限
  • LOCK TABLES：锁定表的权限
  • REFERENCES：创建外键的权限
  • CREATE TEMPORARY TABLES：创建临时表的权限
  • SELECT：查询数据的权限
  • INSERT：插入数据的权限
  • UPDATE：更新数据的权限
  • DELETE：删除数据的权限
  • TRIGGER：创建或使用触发器的权限
  • CREATE ROUTINE：创建存储过程或函数的权限
  • ALTER ROUTINE：修改存储过程或函数的权限
  • EXECUTE：执行存储过程或函数的权限

3. 表级权限 ​

作用于特定表的权限，如：

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • ALTER
  • INDEX
  • CREATE VIEW
  • SHOW VIEW
  • TRIGGER
  • LOCK TABLES

4. 列级权限 ​

作用于表中特定列的权限，如：

  • SELECT(col1, col2)
  • INSERT(col1, col2)
  • UPDATE(col1, col2)

5. 子程序级权限 ​

作用于存储过程或函数的权限，如：

  • EXECUTE
  • ALTER ROUTINE

角色的概念和优势 ​

什么是角色 ​

角色是一组权限的集合，可以授予用户或其他角色。角色可以简化权限管理，特别是在需要管理大量用户和复杂权限的情况下。

角色的优势 ​

  1. 简化权限管理：通过将权限分配给角色，然后将角色分配给用户，简化了权限管理
  2. 一致性：确保具有相同职责的用户具有相同的权限
  3. 可维护性：当权限需求变化时，只需修改角色的权限，所有分配了该角色的用户都会自动获得更新后的权限
  4. 最小权限原则：更容易实现最小权限原则，只授予用户完成其工作所需的最小权限
  5. 审计友好：更容易审计用户的权限，因为可以通过角色了解用户的权限

角色和权限的实现 ​

1. 用户管理 ​

创建用户 ​

sql
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

-- MySQL 8.0+ 使用 caching_sha2_password 认证插件
CREATE USER 'username'@'hostname' IDENTIFIED WITH caching_sha2_password BY 'password';

-- 创建用户并限制连接数
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 10;

修改用户 ​

sql
-- 修改用户密码
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

-- 修改用户的认证插件
ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';

-- 限制用户的连接数
ALTER USER 'username'@'hostname' WITH MAX_USER_CONNECTIONS 5;

删除用户 ​

sql
DROP USER 'username'@'hostname';

-- 如果用户存在则删除
DROP USER IF EXISTS 'username'@'hostname';

2. 角色管理 ​

创建角色 ​

sql
CREATE ROLE 'role_name';

-- 创建多个角色
CREATE ROLE 'role1', 'role2', 'role3';

授予权限给角色 ​

sql
-- 授予数据库级权限
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'role_name';

-- 授予表级权限
GRANT SELECT, INSERT ON database_name.table_name TO 'role_name';

-- 授予列级权限
GRANT SELECT (col1, col2), UPDATE (col1) ON database_name.table_name TO 'role_name';

-- 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'role_name';

授予角色给用户 ​

sql
-- 授予角色给用户
GRANT 'role_name' TO 'username'@'hostname';

-- 授予多个角色给用户
GRANT 'role1', 'role2' TO 'username'@'hostname';

激活角色 ​

sql
-- 激活用户的所有角色
SET DEFAULT ROLE ALL TO 'username'@'hostname';

-- 激活特定角色
SET DEFAULT ROLE 'role1', 'role2' TO 'username'@'hostname';

-- 在当前会话中激活角色
SET ROLE 'role_name';

-- 激活所有角色
SET ROLE ALL;

撤销角色 ​

sql
-- 从用户撤销角色
REVOKE 'role_name' FROM 'username'@'hostname';

-- 撤销角色的权限
REVOKE SELECT ON database_name.* FROM 'role_name';

-- 删除角色
DROP ROLE 'role_name';

3. 直接授予权限给用户 ​

sql
-- 授予全局权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' WITH GRANT OPTION;

-- 授予数据库级权限
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'hostname';

-- 授予表级权限
GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'hostname';

-- 授予列级权限
GRANT SELECT (col1, col2), UPDATE (col1) ON database_name.table_name TO 'username'@'hostname';

4. 撤销权限 ​

sql
-- 撤销全局权限
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'hostname';

-- 撤销数据库级权限
REVOKE SELECT, INSERT, UPDATE, DELETE ON database_name.* FROM 'username'@'hostname';

-- 撤销表级权限
REVOKE SELECT, INSERT ON database_name.table_name FROM 'username'@'hostname';

-- 撤销列级权限
REVOKE SELECT (col1, col2), UPDATE (col1) ON database_name.table_name FROM 'username'@'hostname';

5. 查看权限 ​

sql
-- 查看用户的权限
SHOW GRANTS FOR 'username'@'hostname';

-- 查看角色的权限
SHOW GRANTS FOR 'role_name';

-- 查看当前用户的权限
SHOW GRANTS;

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

权限检查流程 ​

当用户执行操作时，MySQL 会按照以下流程检查权限：

  1. 连接验证：验证用户的身份和密码
  2. 全局权限检查：如果操作需要全局权限，检查用户是否具有相应的全局权限
  3. 数据库级权限检查：如果操作需要数据库级权限，检查用户是否具有相应的数据库级权限
  4. 表级权限检查：如果操作需要表级权限，检查用户是否具有相应的表级权限
  5. 列级权限检查：如果操作需要列级权限，检查用户是否具有相应的列级权限
  6. 子程序级权限检查：如果操作需要子程序级权限，检查用户是否具有相应的子程序级权限

最佳实践 ​

1. 设计原则 ​

  • 最小权限原则：只授予用户完成其工作所需的最小权限
  • 角色分离：为不同的职责创建不同的角色
  • 层次化设计：创建层次化的角色结构，从基础角色到高级角色
  • 定期审查：定期审查用户和角色的权限，确保符合最小权限原则
  • 命名规范：使用清晰的命名规范，便于理解角色的用途

2. 角色设计示例 ​

基础角色 ​

  • read_only：只具有 SELECT 权限
  • read_write：具有 SELECT、INSERT、UPDATE、DELETE 权限
  • data_admin：具有数据操作的所有权限
  • schema_admin：具有数据库和表结构修改的权限

应用角色 ​

  • app_read：应用程序的只读用户角色
  • app_write：应用程序的读写用户角色
  • app_admin：应用程序的管理员角色

管理角色 ​

  • db_admin：数据库管理员角色
  • repl_admin：复制管理员角色
  • backup_admin：备份管理员角色

3. 实现步骤 ​

  1. 设计角色层次结构：根据组织的结构和需求，设计角色的层次结构
  2. 创建基础角色：创建基础角色，如 read_only、read_write 等
  3. 创建应用角色：根据应用程序的需求，创建应用角色
  4. 创建管理角色：创建管理角色，如 db_admin、repl_admin 等
  5. 授予权限给角色：将相应的权限授予给角色
  6. 创建用户：创建用户并设置强密码
  7. 授予角色给用户：将相应的角色授予给用户
  8. 激活角色：确保用户的角色被正确激活
  9. 测试权限：测试用户的权限，确保只具有所需的权限
  10. 定期审查：定期审查用户和角色的权限

4. 安全性最佳实践 ​

  • 使用强密码：为用户设置强密码
  • 限制远程访问：只允许必要的 IP 地址访问 MySQL 服务器
  • 禁用匿名用户：删除或禁用匿名用户
  • 限制 root 用户访问：限制 root 用户只能从本地访问
  • 定期更新密码：定期更新用户密码
  • 启用审计：启用审计日志，记录用户的操作
  • 使用 SSL/TLS：配置 MySQL 使用 SSL/TLS 加密连接

5. 审计和监控 ​

  • 启用审计日志：

    sql
    -- MySQL Enterprise Edition
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';

  • 使用 Performance Schema 监控权限：

    sql
    SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%privilege%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%privilege%';

  • 定期审查权限：

    sql
    -- 查看所有用户的权限
SELECT user, host FROM mysql.user;

-- 查看用户的权限
SHOW GRANTS FOR 'username'@'hostname';

案例分析 ​

案例1：电商系统的角色和权限设计 ​

问题：一家电商公司需要设计 MySQL 数据库的角色和权限模型，支持以下用户：

  • 应用程序用户：需要读写产品、订单、用户等数据
  • 数据分析用户：需要只读访问产品、订单、用户等数据
  • 数据库管理员：需要管理数据库的所有权限
  • 备份管理员：只需要备份相关的权限

解决方案：

  1. 设计角色：

    • app_read：应用程序只读角色，具有 SELECT 权限
    • app_write：应用程序读写角色，具有 SELECT、INSERT、UPDATE、DELETE 权限
    • app_admin：应用程序管理员角色，具有所有数据操作权限
    • analyst：数据分析角色，具有只读权限
    • db_admin：数据库管理员角色，具有所有权限
    • backup_admin：备份管理员角色，具有备份相关的权限

  2. 创建角色：

    sql
    CREATE ROLE 'app_read', 'app_write', 'app_admin', 'analyst', 'db_admin', 'backup_admin';

  3. 授予权限给角色：

    sql
    -- 应用程序只读角色
GRANT SELECT ON ecommerce.* TO 'app_read';

-- 应用程序读写角色
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* TO 'app_write';

-- 应用程序管理员角色
GRANT ALL PRIVILEGES ON ecommerce.* TO 'app_admin';

-- 数据分析角色
GRANT SELECT ON ecommerce.* TO 'analyst';

-- 数据库管理员角色
GRANT ALL PRIVILEGES ON *.* TO 'db_admin' WITH GRANT OPTION;

-- 备份管理员角色
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* TO 'backup_admin';
GRANT SELECT ON mysql.* TO 'backup_admin';
GRANT SELECT, LOCK TABLES ON ecommerce.* TO 'backup_admin';

  4. 创建用户并授予角色：

    sql
    -- 应用程序用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'secure_password';
GRANT 'app_write' TO 'app_user'@'192.168.1.%';
SET DEFAULT ROLE 'app_write' TO 'app_user'@'192.168.1.%';

-- 数据分析用户
CREATE USER 'analyst_user'@'192.168.1.%' IDENTIFIED BY 'secure_password';
GRANT 'analyst' TO 'analyst_user'@'192.168.1.%';
SET DEFAULT ROLE 'analyst' TO 'analyst_user'@'192.168.1.%';

-- 数据库管理员
CREATE USER 'db_admin_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT 'db_admin' TO 'db_admin_user'@'localhost';
SET DEFAULT ROLE 'db_admin' TO 'db_admin_user'@'localhost';

-- 备份管理员
CREATE USER 'backup_admin_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT 'backup_admin' TO 'backup_admin_user'@'localhost';
SET DEFAULT ROLE 'backup_admin' TO 'backup_admin_user'@'localhost';

结果：

  • 成功设计了电商系统的角色和权限模型
  • 实现了最小权限原则，只授予用户完成其工作所需的最小权限
  • 简化了权限管理，通过角色管理用户的权限
  • 便于审计和监控用户的权限

案例2：多租户系统的角色和权限设计 ​

问题：一家 SaaS 公司需要设计多租户系统的角色和权限模型，每个租户有自己的数据库，需要隔离不同租户的数据，同时支持不同角色的用户。

解决方案：

  1. 设计角色：

    • tenant_read：租户只读角色，具有 SELECT 权限
    • tenant_write：租户读写角色，具有 SELECT、INSERT、UPDATE、DELETE 权限
    • tenant_admin：租户管理员角色，具有租户数据库的所有权限
    • saas_admin：SaaS 管理员角色，具有所有租户数据库的权限

  2. 创建角色：

    sql
    CREATE ROLE 'tenant_read', 'tenant_write', 'tenant_admin', 'saas_admin';

  3. 授予权限给角色：

    sql
    -- SaaS 管理员角色
GRANT ALL PRIVILEGES ON *.* TO 'saas_admin' WITH GRANT OPTION;

  4. 为每个租户创建数据库和用户：

    sql
    -- 创建租户数据库
CREATE DATABASE tenant1_db;
CREATE DATABASE tenant2_db;

-- 为租户1创建用户和权限
CREATE USER 'tenant1_read'@'192.168.1.%' IDENTIFIED BY 'secure_password';
CREATE USER 'tenant1_write'@'192.168.1.%' IDENTIFIED BY 'secure_password';
CREATE USER 'tenant1_admin'@'192.168.1.%' IDENTIFIED BY 'secure_password';

GRANT SELECT ON tenant1_db.* TO 'tenant1_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON tenant1_db.* TO 'tenant1_write';
GRANT ALL PRIVILEGES ON tenant1_db.* TO 'tenant1_admin';

-- 为租户2创建用户和权限
CREATE USER 'tenant2_read'@'192.168.1.%' IDENTIFIED BY 'secure_password';
CREATE USER 'tenant2_write'@'192.168.1.%' IDENTIFIED BY 'secure_password';
CREATE USER 'tenant2_admin'@'192.168.1.%' IDENTIFIED BY 'secure_password';

GRANT SELECT ON tenant2_db.* TO 'tenant2_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON tenant2_db.* TO 'tenant2_write';
GRANT ALL PRIVILEGES ON tenant2_db.* TO 'tenant2_admin';

结果：

  • 成功设计了多租户系统的角色和权限模型
  • 实现了租户数据的隔离
  • 支持不同角色的用户
  • 便于管理和审计

不同版本的差异 ​

MySQL 5.6 ​

  • 不支持角色功能
  • 只能直接授予权限给用户
  • 权限管理相对复杂
  • 不支持 caching_sha2_password 认证插件

MySQL 5.7 ​

  • 支持角色功能（MySQL 5.7.6+）
  • 可以创建和管理角色
  • 可以将角色授予给用户
  • 支持 SET DEFAULT ROLE 语句（MySQL 5.7.17+）

MySQL 8.0 ​

  • 增强了角色功能
  • 支持更复杂的角色层次结构
  • 支持 caching_sha2_password 认证插件
  • 增强了权限管理功能
  • 支持角色的密码管理
  • 增强了审计功能

常见问题（FAQ） ​

Q1: 如何查看用户的权限？ ​

A1: 使用 SHOW GRANTS 语句查看用户的权限：

sql
SHOW GRANTS FOR 'username'@'hostname';

Q2: 如何查看角色的权限？ ​

A2: 使用 SHOW GRANTS 语句查看角色的权限：

sql
SHOW GRANTS FOR 'role_name';

Q3: 如何激活用户的角色？ ​

A3: 使用 SET DEFAULT ROLE 语句激活用户的角色：

sql
SET DEFAULT ROLE ALL TO 'username'@'hostname';

Q4: 如何撤销用户的权限？ ​

A4: 使用 REVOKE 语句撤销用户的权限：

sql
REVOKE SELECT ON database_name.* FROM 'username'@'hostname';

Q5: 如何撤销用户的角色？ ​

A5: 使用 REVOKE 语句撤销用户的角色：

sql
REVOKE 'role_name' FROM 'username'@'hostname';

Q6: 如何删除角色？ ​

A6: 使用 DROP ROLE 语句删除角色：

sql
DROP ROLE 'role_name';

Q7: 如何处理权限冲突？ ​

A7: 权限冲突通常是由于用户被授予了多个角色，而这些角色具有不同的权限。在这种情况下，MySQL 会使用权限的并集，即用户具有所有角色的权限之和。

Q8: 如何实现行级权限？ ​

A8: MySQL 本身不直接支持行级权限，但可以通过以下方法实现：

  • 使用视图限制用户可以访问的行
  • 使用存储过程限制用户可以访问的行
  • 使用第三方工具如 ProxySQL 实现行级权限

Q9: 如何备份和恢复用户和权限？ ​

A9: 可以使用以下方法备份和恢复用户和权限：

  • 使用 mysqldump 备份 mysql 数据库：

    bash
    mysqldump -u root -p mysql > mysql_backup.sql

  • 恢复 mysql 数据库：

    bash
    mysql -u root -p mysql < mysql_backup.sql

Q10: 如何审计用户的权限？ ​

A10: 可以使用以下方法审计用户的权限：

  • 使用 SHOW GRANTS 语句查看用户的权限
  • 启用审计日志，记录用户的操作
  • 使用 Performance Schema 监控权限的使用
  • 使用第三方审计工具

未来发展趋势 ​

  1. 更细粒度的权限：支持更细粒度的权限，如行级权限
  2. 动态权限：支持基于上下文的动态权限
  3. 集成外部身份验证：更好地集成外部身份验证系统，如 LDAP、OAuth 等
  4. 更强大的审计功能：更强大的审计功能，便于合规和安全审计
  5. AI 辅助权限管理：使用 AI 辅助权限管理，自动识别过度权限和权限不足
  6. 云原生集成：更好地支持云原生环境，如 Kubernetes 上的 MySQL
  7. 零信任架构：支持零信任架构，每次访问都验证权限
  8. 更简化的权限管理：更简化的权限管理界面和工具
  9. 更强大的角色管理：支持更复杂的角色层次结构和继承
  10. 更好的可视化工具：更好的可视化工具，便于管理和审计权限