MySQL权限详解

在MySQL中，权限管理是保障数据安全和合理使用的重要手段。MySQL提供了丰富的权限控制机制，允许管理员对不同用户授予不同级别的操作权限。本文将会对MySQL中的权限管理，以及内核如何实现权限控制进行介绍。

一、权限级别

MySQL 的权限是分层的，权限可以授予不同级别的对象：
全局权限： 作用于整个 MySQL 服务器实例的所有数据库，授予全局权限的用户可以对服务器上的任何数据库和对象进行操作，存储在 mysql.user 表。

• ALL PRIVILEGES：授予用户所有可用的权限。
• CREATE：允许用户创建新的数据库和表。
• DROP：允许用户删除数据库和表。
• ALTER：允许用户修改表结构，如添加、删除列等。
• INDEX：允许用户创建和删除索引。
• GRANT OPTION：允许用户将自己拥有的权限授予其他用户。

数据库权限： 作用于特定数据库（或匹配模式的所有数据库）中的所有对象，存储在 mysql.db 和 mysql.host 表（后者很少用）。

• CREATE TEMPORARY TABLES：允许用户在指定数据库中创建临时表。
• SHOW VIEW：允许用户查看指定数据库中视图的定义。
• CREATE ROUTINE：允许用户在指定数据库中创建存储过程和函数。
• ALTER ROUTINE：允许用户修改指定数据库中存储过程和函数的定义。
• EXECUTE：允许用户执行指定数据库中的存储过程和函数。

表权限： 作用于特定表（或匹配模式的所有表）中的所有列，存储在 mysql.tables_priv 表。

• SELECT：允许用户从表中查询数据。
• INSERT：允许用户向表中插入新数据。
• UPDATE：允许用户修改表中的数据。
• DELETE：允许用户删除表中的数据。
• REFERENCES：允许用户创建外键关联。

列权限： 作用于特定表中的特定列，存储在 mysql.columns_priv 表。
可以针对表中的特定列授予 SELECT、INSERT、UPDATE 等权限，限制用户只能对指定列进行操作。
程序权限： 作用于存储过程、存储函数，存储在 mysql.procs_priv 表。

• PROCESS：允许用户查看所有正在执行的线程信息。
• SHOW DATABASES：允许用户查看所有数据库的列表。
• RELOAD：允许用户重新加载权限表和刷新日志。
• SHUTDOWN：允许用户关闭 MySQL 服务器。

global、database、table、column、routines，控制层级示意图：

权限管理操作

创建用户：

-- 创建一个新用户，用户名为 'test_user'，密码为 'test_password'
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_password';

授予权限：

GRANT privilege_type [(column_list)] [, privilege_type [(column_list)] ...]
ON [object_type] privilege_level
TO user_or_role [, user_or_role ...]
[WITH GRANT OPTION]; -- 谨慎使用

查看权限：

SHOW GRANTS; -- 查看当前用户权限
SHOW GRANTS FOR 'username'@'host'; -- 查看指定用户权限
SELECT * FROM mysql.user WHERE User='username' AND Host='host'\G -- 查看 user 表信息 (谨慎)
SELECT * FROM mysql.db WHERE User='username' AND Host='host'\G -- 查看 db 表权限
-- 类似可以查看 tables_priv, columns_priv, procs_priv

撤销权限：

REVOKE [GRANT OPTION FOR] privilege_type [(column_list)] [, privilege_type [(column_list)] ...]
ON [object_type] privilege_level
FROM user_or_role [, user_or_role ...];

参考文章：MySQL权限详解