MySQL 是一个广泛使用的关系型数据库管理系统。其权限管理功能对于保证数据库的安全性至关重要。本文将为你详细介绍 MySQL 中的权限管理。
1. MySQL 权限介绍
MySQL 中存在 4 个控制权限的表，分别为：
- user 表
- db 表
- tables_priv 表
- columns_priv 表
当前版本为 mysql 5.7.22。
MySQL 的权限验证过程如下：
- 先从 user 表中的 Host, User, Password 这 3 个字段中判断连接的 ip、用户名、密码是否存在。存在则通过验证。
- 通过身份认证后，进行权限分配，按照 user，db，tables_priv，columns_priv 的顺序进行验证。首先，会检查全局权限表 user。如果 user 中对应的权限为 Y，则此用户对所有数据库的权限都为 Y，将不再检查 db, tables_priv, columns_priv。如果为 N，则到 db 表中检查此用户对应的具体数据库，并得到 db 中为 Y 的权限；如果 db 中为 N，则检查 tables_priv 中此数据库对应的具体表，取得表中的权限 Y，以此类推。
1.1 MySQL 权限级别
权限分为以下几个级别：
- 全局性的管理权限：作用于整个 MySQL 实例级别。
- 数据库级别的权限：作用于某个指定的数据库上或者所有的数据库上。
- 数据库对象级别的权限：作用于指定的数据库对象上（如表、视图等）或者所有的数据库对象上。
权限信息存储在 mysql 库的 user, db, tables_priv, columns_priv, and procs_priv 这几个系统表中。待 MySQL 实例启动后，这些权限信息就会加载到内存中。
查看 MySQL 中存在的用户：
mysql> select user,host from mysql.user;
例如，来看 root 用户在权限系统表中的数据：
mysql> use mysql; mysql> select * from user where user='root' and host='localhost'\G; #所有权限都是Y，表示具有所有权限 mysql> select * from db where user='root' and host='localhost'\G; #没有此条记录 mysql> select * from tables_priv where user='root' and host='localhost'; #没有此条记录
2. MySQL 权限详解
2.1 常见权限概览
- All/All Privileges 权限：代表全局或者全数据库对象级别的所有权限。
- Alter 权限：允许修改表结构的权限，但必须要求有 create 和 insert 权限配合。如果是 rename 表名，则要求有 alter 和 drop 原表， create 和 insert 新表的权限。
- Alter routine 权限：允许修改或者删除存储过程、函数的权限。
- Create 权限：允许创建新的数据库和表的权限。
- Create routine 权限：允许创建存储过程、函数的权限。
- Create tablespace 权限：允许创建、修改、删除表空间和日志组的权限。
- Create temporary tables 权限：允许创建临时表的权限。
- Create user 权限：允许创建、修改、删除、重命名 user 的权限。
- Create view 权限：允许创建视图的权限。
- Delete 权限：允许删除行数据的权限。
- Drop 权限：允许删除数据库、表、视图的权限，包括 truncate table 命令。
2.2 其他权限相关的操作
2.2.1 查看用户权限
查看 root@localhost 用户的权限：
mysql> show grants for root@localhost;
2.2.2 设置用户资源限制
你可以通过设置全局变量 max_user_connections 来限制所有用户在同一时间连接 MySQL 实例的数量。但此参数无法对每个用户区别对待，所以 MySQL 提供了对每个用户的资源限制管理。
2.2.3 修改用户密码
修改用户密码的方式包括：
mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
你也可以在创建用户时指定密码：
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
2.2.4 用户账户锁定
当客户端尝试使用锁定状态的用户登录 MySQL 时，将会收到以下错误信息：
Access denied for user ‘user_name’@’host_name’. Account is locked.
苏公网安备32021302001419号