MariaDB 权限管理

权限管理概述

权限管理是 MariaDB 安全管理的核心，通过合理分配和管理用户权限，可以有效控制用户对数据库资源的访问，防止未授权访问和数据泄露。MariaDB 提供了细粒度的权限控制，支持在不同级别（全局、数据库、表、列、存储过程等）授予和撤销权限。

权限级别

MariaDB 支持以下权限级别：

1. 全局权限

作用于所有数据库和表，存储在 mysql.user 表中。

常用全局权限：

ALL PRIVILEGES：所有权限
CREATE USER：创建用户
DROP USER：删除用户
GRANT OPTION：授予权限给其他用户
PROCESS：查看进程列表
SUPER：超级权限，用于执行管理操作
RELOAD：重新加载配置
SHUTDOWN：关闭服务器
FILE：读写服务器文件
SHOW DATABASES：查看所有数据库

2. 数据库权限

作用于特定数据库，存储在 mysql.db 表中。

常用数据库权限：

ALL PRIVILEGES：数据库级别的所有权限
CREATE：创建表和视图
DROP：删除表和视图
ALTER：修改表结构
SELECT：查询表数据
INSERT：插入数据
UPDATE：更新数据
DELETE：删除数据
CREATE VIEW：创建视图
SHOW VIEW：查看视图

3. 表权限

作用于特定表，存储在 mysql.tables_priv 表中。

常用表权限：

SELECT：查询表数据
INSERT：插入数据
UPDATE：更新数据
DELETE：删除数据
ALTER：修改表结构
INDEX：创建和删除索引
CREATE VIEW：创建视图
SHOW VIEW：查看视图
TRIGGER：创建和使用触发器

4. 列权限

作用于特定表的特定列，存储在 mysql.columns_priv 表中。

常用列权限：

SELECT：查询列数据
INSERT：插入列数据
UPDATE：更新列数据

5. 存储过程和函数权限

作用于存储过程和函数，存储在 mysql.procs_priv 表中。

常用存储过程和函数权限：

EXECUTE：执行存储过程和函数
ALTER ROUTINE：修改存储过程和函数
CREATE ROUTINE：创建存储过程和函数
GRANT：授予存储过程和函数的权限

授予权限

基本语法

sql

GRANT privileges ON level TO 'user'@'host' [WITH GRANT OPTION];

授予全局权限

sql

-- 授予用户全局所有权限（谨慎操作）
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

-- 授予用户创建用户和授予权限的权限
GRANT CREATE USER, GRANT OPTION ON *.* TO 'grant_user'@'localhost';

-- 授予用户查看进程和重新加载配置的权限
GRANT PROCESS, RELOAD ON *.* TO 'monitor_user'@'localhost';

授予数据库权限

sql

-- 授予用户对特定数据库的所有权限
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'%';

-- 授予用户对特定数据库的读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'rw_user'@'%';

-- 授予用户对特定数据库的只读权限
GRANT SELECT ON app_db.* TO 'read_user'@'%';

授予表权限

sql

-- 授予用户对特定表的所有权限
GRANT ALL PRIVILEGES ON app_db.users TO 'user'@'%';

-- 授予用户对特定表的查询和插入权限
GRANT SELECT, INSERT ON app_db.orders TO 'user'@'%';

-- 授予用户对特定表的更新权限，并限制只能更新特定列
GRANT UPDATE (status, updated_at) ON app_db.orders TO 'user'@'%';

授予列权限

sql

-- 授予用户对特定表特定列的查询权限
GRANT SELECT (id, name, email) ON app_db.users TO 'user'@'%';

-- 授予用户对特定表特定列的更新权限
GRANT UPDATE (email, phone) ON app_db.users TO 'user'@'%';

授予存储过程和函数权限

sql

-- 授予用户执行所有存储过程和函数的权限
GRANT EXECUTE ON *.* TO 'user'@'%';

-- 授予用户执行特定存储过程的权限
GRANT EXECUTE ON PROCEDURE app_db.get_user TO 'user'@'%';

-- 授予用户创建和执行存储过程的权限
GRANT CREATE ROUTINE, EXECUTE ON app_db.* TO 'dev_user'@'%';

撤销权限

基本语法

sql

REVOKE privileges ON level FROM 'user'@'host';

撤销全局权限

sql

-- 撤销用户的所有全局权限
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';

-- 撤销用户的 SUPER 权限
REVOKE SUPER ON *.* FROM 'user'@'localhost';

撤销数据库权限

sql

-- 撤销用户对特定数据库的所有权限
REVOKE ALL PRIVILEGES ON app_db.* FROM 'user'@'%';

-- 撤销用户对特定数据库的删除权限
REVOKE DELETE ON app_db.* FROM 'user'@'%';

撤销表权限

sql

-- 撤销用户对特定表的所有权限
REVOKE ALL PRIVILEGES ON app_db.users FROM 'user'@'%';

-- 撤销用户对特定表的更新权限
REVOKE UPDATE ON app_db.orders FROM 'user'@'%';

撤销列权限

sql

-- 撤销用户对特定表特定列的更新权限
REVOKE UPDATE (email) ON app_db.users FROM 'user'@'%';

撤销存储过程和函数权限

sql

-- 撤销用户执行存储过程的权限
REVOKE EXECUTE ON PROCEDURE app_db.get_user FROM 'user'@'%';

查看权限

查看用户权限

sql

-- 查看用户的所有权限
SHOW GRANTS FOR 'user'@'host';

-- 查看用户的全局权限
SHOW GRANTS FOR 'user'@'host' USING '*.*';

-- 查看用户的数据库级权限
SHOW GRANTS FOR 'user'@'host' USING 'app_db';

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

从系统表查看权限

sql

-- 查看全局权限
SELECT * FROM mysql.user WHERE User = 'user' AND Host = 'host';

-- 查看数据库权限
SELECT * FROM mysql.db WHERE User = 'user' AND Host = 'host';

-- 查看表权限
SELECT * FROM mysql.tables_priv WHERE User = 'user' AND Host = 'host';

-- 查看列权限
SELECT * FROM mysql.columns_priv WHERE User = 'user' AND Host = 'host';

-- 查看存储过程和函数权限
SELECT * FROM mysql.procs_priv WHERE User = 'user' AND Host = 'host';

刷新权限

在修改权限后，需要刷新权限才能生效：

sql

-- 刷新权限
FLUSH PRIVILEGES;

权限继承与角色管理

角色管理

MariaDB 10.0+ 支持角色管理，可以将权限分配给角色，然后将角色分配给用户，简化权限管理。

sql

-- 创建角色
CREATE ROLE 'read_only', 'read_write', 'admin';

-- 为角色授予权限
GRANT SELECT ON *.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'read_write';
GRANT ALL PRIVILEGES ON *.* TO 'admin' WITH GRANT OPTION;

-- 将角色分配给用户
GRANT 'read_only' TO 'report_user'@'%';
GRANT 'read_write' TO 'app_user'@'%';
GRANT 'admin' TO 'admin_user'@'localhost';

-- 激活角色
-- 方法 1：为用户设置默认角色
SET DEFAULT ROLE 'read_only' TO 'report_user'@'%';
SET DEFAULT ROLE 'read_write' TO 'app_user'@'%';
SET DEFAULT ROLE 'admin' TO 'admin_user'@'localhost';

-- 方法 2：用户登录后手动激活角色
SET ROLE 'read_only';

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

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

撤销角色

sql

-- 从用户撤销角色
REVOKE 'read_write' FROM 'app_user'@'%';

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

权限审计

审计权限变更

启用审计日志，记录权限变更操作
定期审查权限变更日志
使用版本控制系统管理权限变更脚本

sql

-- 安装审计插件
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

-- 配置审计日志
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';

定期审查权限

定期查看用户权限，识别不必要的权限
定期撤销不再需要的权限
文档化所有权限变更

sql

-- 查看所有用户及其权限
SELECT User, Host, Grant_priv, Super_priv FROM mysql.user;

-- 查看具有 SUPER 权限的用户
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

-- 查看具有 GRANT OPTION 权限的用户
SELECT User, Host FROM mysql.user WHERE Grant_priv = 'Y';

权限管理的最佳实践

1. 遵循最小权限原则

只授予用户完成工作所需的最小权限
避免授予 ALL PRIVILEGES 权限
定期审查和回收不必要的权限

2. 按功能分配权限

根据用户的功能角色分配权限
例如：
- 只读用户：用于报表和数据分析
- 读写用户：用于应用程序
- 管理员用户：用于数据库管理

3. 限制权限范围

尽量限制权限的作用范围
例如：
- 避免授予全局权限，尽量授予数据库级或表级权限
- 对于敏感列，只授予必要的列权限

4. 谨慎使用 GRANT OPTION

只授予信任用户 GRANT OPTION 权限
定期审查具有 GRANT OPTION 权限的用户
避免在生产环境中广泛使用 GRANT OPTION

5. 定期审计权限

每季度或半年进行一次权限审计
审查用户权限，回收不必要的权限
记录权限变更日志

6. 使用角色管理权限

对于复杂系统，使用角色管理权限
可以将权限分配给角色，然后将角色分配给用户
简化权限管理，提高安全性

7. 保护敏感权限

敏感权限（如 SUPER、FILE、SHUTDOWN 等）只授予必要的用户
限制敏感权限的使用范围
监控敏感权限的使用情况

8. 分离管理权限和应用权限

管理员用户和应用用户使用不同的账户
应用用户只授予必要的业务权限
管理员用户只用于管理操作

9. 实现权限变更的审批流程

所有权限变更必须经过审批
使用脚本或工具管理权限变更
记录权限变更的原因和审批人

10. 定期备份权限

定期备份 mysql 系统数据库
备份权限变更脚本
确保在灾难恢复时能够快速恢复权限

常见问题及解决方案

问题 1：用户无法访问数据库

现象：用户连接数据库后，无法访问特定数据库原因：

没有授予用户对该数据库的权限
用户使用了错误的数据库名
权限没有刷新

解决方案：

授予用户对该数据库的权限

sql

GRANT SELECT ON db_name.* TO 'user'@'%';
FLUSH PRIVILEGES;

检查用户使用的数据库名是否正确
执行 FLUSH PRIVILEGES 刷新权限

问题 2：用户无法执行特定操作

现象：用户连接数据库后，无法执行特定操作（如创建表、更新数据等）原因：

没有授予用户执行该操作的权限
权限作用范围不正确

解决方案：

授予用户执行该操作的权限

sql

GRANT CREATE ON db_name.* TO 'user'@'%';

检查权限的作用范围是否正确

问题 3：无法撤销用户权限

现象：执行 REVOKE 命令后，用户仍然具有该权限原因：

权限是从多个级别授予的（如全局和数据库级别）
没有刷新权限

解决方案：

从所有级别撤销权限

sql

REVOKE SELECT ON *.* FROM 'user'@'%';
REVOKE SELECT ON db_name.* FROM 'user'@'%';
FLUSH PRIVILEGES;

执行 FLUSH PRIVILEGES 刷新权限

问题 4：角色权限不生效

现象：将角色分配给用户后，用户无法使用角色的权限原因：

角色没有被激活
没有为用户设置默认角色

解决方案：

为用户设置默认角色

sql

SET DEFAULT ROLE 'role_name' TO 'user'@'%';

或用户登录后手动激活角色
sql
```
SET ROLE 'role_name';
```

常见问题 (FAQ)

Q1：如何查看用户具有哪些权限？

A：使用 SHOW GRANTS FOR 'user'@'host' 命令查看用户权限。

Q2：如何授予用户所有权限？

A：使用 GRANT ALL PRIVILEGES ON level TO 'user'@'host' 命令，但应谨慎使用，遵循最小权限原则。

Q3：如何撤销用户的所有权限？

A：使用 REVOKE ALL PRIVILEGES ON level FROM 'user'@'host' 命令。

Q4：权限变更后需要刷新吗？

A：是的，使用 FLUSH PRIVILEGES 命令刷新权限，使其生效。

Q5：什么是最小权限原则？

A：最小权限原则是指只授予用户完成工作所需的最小权限，避免授予不必要的权限。

Q6：如何使用角色管理权限？

A：创建角色，为角色授予权限，然后将角色分配给用户，简化权限管理。

Q7：如何审计权限变更？

A：启用审计日志，记录权限变更操作，定期审查权限变更日志。

Q8：如何定期审查权限？

A：定期查看用户权限，识别不必要的权限，定期撤销不再需要的权限。

MariaDB 权限管理 ​

权限管理概述 ​

权限级别 ​

1. 全局权限 ​

2. 数据库权限 ​

3. 表权限 ​

4. 列权限 ​

5. 存储过程和函数权限 ​

授予权限 ​

基本语法 ​

授予全局权限 ​

授予数据库权限 ​

授予表权限 ​

授予列权限 ​

授予存储过程和函数权限 ​

撤销权限 ​

基本语法 ​

撤销全局权限 ​

撤销数据库权限 ​

撤销表权限 ​

撤销列权限 ​

撤销存储过程和函数权限 ​

查看权限 ​

查看用户权限 ​

从系统表查看权限 ​

刷新权限 ​

权限继承与角色管理 ​

角色管理 ​

撤销角色 ​

权限审计 ​

审计权限变更 ​

定期审查权限 ​

权限管理的最佳实践 ​

1. 遵循最小权限原则 ​

2. 按功能分配权限 ​

3. 限制权限范围 ​

4. 谨慎使用 GRANT OPTION ​

5. 定期审计权限 ​

6. 使用角色管理权限 ​

7. 保护敏感权限 ​

8. 分离管理权限和应用权限 ​

9. 实现权限变更的审批流程 ​

10. 定期备份权限 ​

常见问题及解决方案 ​

问题 1：用户无法访问数据库 ​

问题 2：用户无法执行特定操作 ​

问题 3：无法撤销用户权限 ​

问题 4：角色权限不生效 ​

常见问题 (FAQ) ​

Q1：如何查看用户具有哪些权限？ ​

Q2：如何授予用户所有权限？ ​

Q3：如何撤销用户的所有权限？ ​

Q4：权限变更后需要刷新吗？ ​

Q5：什么是最小权限原则？ ​

Q6：如何使用角色管理权限？ ​

Q7：如何审计权限变更？ ​

Q8：如何定期审查权限？ ​

MariaDB 权限管理

权限管理概述

权限级别

1. 全局权限

2. 数据库权限

3. 表权限

4. 列权限

5. 存储过程和函数权限

授予权限

基本语法

授予全局权限

授予数据库权限

授予表权限

授予列权限

授予存储过程和函数权限

撤销权限

基本语法

撤销全局权限

撤销数据库权限

撤销表权限

撤销列权限

撤销存储过程和函数权限

查看权限

查看用户权限

从系统表查看权限

刷新权限

权限继承与角色管理

角色管理

撤销角色

权限审计

审计权限变更

定期审查权限

权限管理的最佳实践

1. 遵循最小权限原则

2. 按功能分配权限

3. 限制权限范围

4. 谨慎使用 GRANT OPTION

5. 定期审计权限

6. 使用角色管理权限

7. 保护敏感权限

8. 分离管理权限和应用权限

9. 实现权限变更的审批流程

10. 定期备份权限

常见问题及解决方案

问题 1：用户无法访问数据库

问题 2：用户无法执行特定操作

问题 3：无法撤销用户权限

问题 4：角色权限不生效

常见问题 (FAQ)

Q1：如何查看用户具有哪些权限？

Q2：如何授予用户所有权限？

Q3：如何撤销用户的所有权限？

Q4：权限变更后需要刷新吗？

Q5：什么是最小权限原则？

Q6：如何使用角色管理权限？

Q7：如何审计权限变更？

Q8：如何定期审查权限？