MySQL 用户权限管理

发表于 分类于 阅读次数： 本文字数： 7.4k
MySQL 用户权限管理

MySQL 用户权限管理是 MySQL 中一个不常能接触的概念，但是当涉及到数据库权限管理时，这一块又非常重要。本文将介绍 MySQL 用户权限管理的基本概念和操作。帮助你更好地理解 MySQL 用户权限管理。

用户管理

用户管理原理

MySQL 的用户管理是基于 MySQL 的 user 表进行的。user 表中存储了所有 MySQL 用户的信息。 和用户管理相关的 user 表字段如下：

字段 类型 含义
Host char(60) 主机名，用于指定用户可以连接的主机，可以为具体的 IP 地址，也可以为通配符 %，表示所有主机
User char(32) 用户名
plugin char(64) 插件名，用于指定用户使用的插件，用于加密密码
authentication_string text 加密后的密码
password_expired enum(‘N’,’Y’) 密码是否过期
password_last_changed timestamp 密码最后一次修改时间
password_lifetime smallint(5) unsigned 密码过期时间， NULL 表示由 default_password_lifetime 设置决定，0 表示永不过期，>0 表示过期时间，单位为天
account_locked enum(‘N’,’Y’) 账户是否被锁定

创建用户

1
2
3
4
5
6
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user: 'username'@'host'

  • username - 用户名
  • host - 主机名，可以是域名，也可以是IP地址，也可以是通配符 %，表示所有主机
  • 如果省略 @'host', 等同于 'username'@'%'

auth_option 密码设置，可以用以下任意一种语法

  • IDENTIFIED BY 'auth_string' - 使用明文密码
  • IDENTIFIED WITH auth_plugin - 使用插件加密密码
  • IDENTIFIED WITH auth_plugin BY 'auth_string' - 使用插件加密密码
  • IDENTIFIED WITH auth_plugin AS 'auth_string' - 使用插件加密密码
  • IDENTIFIED BY PASSWORD 'auth_string' - 使用密码哈希加密密码(MySQL 8.0 中已弃用)

tls_option: SSL/TLS 相关选项

  • SSL - 使用 SSL 加密连接
  • X509 - 使用 X509 证书加密连接
  • CIPHER 'cipher' - 使用指定加密套件加密连接
  • ISSUER 'issuer' - 使用指定证书颁发机构加密连接
  • SUBJECT 'subject' - 使用指定证书主题加密连接

resource_option: 资源限制选项

  • MAX_QUERIES_PER_HOUR count - 每小时最大查询次数
  • MAX_UPDATES_PER_HOUR count - 每小时最大更新次数
  • MAX_CONNECTIONS_PER_HOUR count - 每小时最大连接次数
  • MAX_USER_CONNECTIONS count - 每用户最大连接次数

password_option: 密码过期选项

  • PASSWORD EXPIRE - 密码过期
  • PASSWORD EXPIRE DEFAULT - 密码过期时间由 default_password_lifetime 设置决定
  • PASSWORD EXPIRE NEVER - 密码永不过期
  • PASSWORD EXPIRE INTERVAL N DAY - 密码过期时间，单位为天

lock_option: 锁定选项

  • ACCOUNT LOCK - 锁定账户
  • ACCOUNT UNLOCK - 解锁账户

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'test'@'%' IDENTIFIED BY '123456';

-- 创建多个用户
CREATE USER 
'test1'@'%' IDENTIFIED BY '123456',
'test2'@'%' IDENTIFIED BY '123456';

-- 限制用户资源
CREATE USER
'test3'@'%' IDENTIFIED BY '123456'
WITH
MAX_QUERIES_PER_HOUR 100,
MAX_UPDATES_PER_HOUR 100,
MAX_CONNECTIONS_PER_HOUR 100,
MAX_USER_CONNECTIONS 100;

-- 密码过期
CREATE USER 'test4'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE;

-- 锁定账户
CREATE USER 'test5'@'%' IDENTIFIED BY '123456' ACCOUNT LOCK;

修改用户

1
2
3
4
5
6
7
8
ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string'
  • USER() - 函数仅用于修改当前用户密码的语法。

user/auth_option/tls_option/resource_option/password_option/lock_option: 同创建用户

示例

1
2
3
4
5
-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY '123456';

-- 修改用户密码
ALTER USER 'test'@'%' IDENTIFIED BY '123456';

删除用户

1
DROP USER [IF EXISTS] user [, user] ...

user 参数同创建用户

示例：

1
2
DROP USER 'test'@'%';
DROP USER 'test1'@'%', 'test2'@'%';

权限管理

权限管理原理

MySQL 权限分为以下几层：

  • global - 全局权限 (mysql.user)
  • database - 数据库权限 (mysql.db)
  • table - 数据表权限 (mysql.tables_priv)
  • column - 数据列权限 (mysql.columns_priv)
  • routine - 存储过程权限 (mysql.procs_priv)

这些权限的关系如下：

加载中...

这些权限有不同的规则

  • 对高层授权，低层会继承高层的权限，比如对 database 授予 SELECT 权限，那么 table 和 column 也会继承 SELECT 权限
  • 对于某一层级特有的权限，无法在其他层级进行授权

如下表是各个权限的详细描述

权限 授权表中的字段名 权限控制的范围 可以授权的层级 介绍
ALL [PRIVILEGES] 所有权限的别名 服务器管理 所有层级 除了 GRANT OPTION 和 PROXY 以外的所有权限
ALTER Alter_priv Table Global, Database, Table 用于使用 Alter Table 语句修改表的结构(除此权限外， ALTER TABLE 还需要 CREATE 和 INSERT 权限， ALTER TABLE RENAME 还需要有旧表的 ALTER 、 DROP 权限和新表的 CREATE 和 INSERT 权限)
ALTER ROUTINE Alter_routine_priv 存储过程或存储函数 Global, Database, Routine 允许修改或删除存储过程和存储函数
CREATE Create_priv 数据库, 数据表, 索引 Global, Database, Table 允许创建数据库和数据表
CREATE ROUTINE Create_routine_priv 存储过程或存储函数 Global, Database 允许创建存储过程和存储函数
CREATE TABLESPACE Create_tablespace_priv 表空间 Global 启用表空间和日志文件组的创建删除和修改
CREATE TEMPORARY TABLES Create_tmp_table_priv 临时表 Global, Database 允许进行创建临时表
CREATE USER Create_user_priv 服务器管理 Global 允许使用创建、删除、重命名用户，以及可以 REVOKE ALL PRIVILEGES 权限
CREATE VIEW Create_view_priv 视图 Global, Database, Table 允许创建及修改视图
DELETE Delete_priv 数据表 Global, Database, Table 允许删除数据表中的数据
DROP Drop_priv 数据库, 数据表, 视图 Global, Database, Table 允许删除数据库和数据表，以及视图，同时分区表执行 ALTER TABLE ... DROP PARTITION 也需要该权限， TRUNCATE TABLE 也需要该权限
EVENT Event_priv 事件 Global, Database 允许创建、修改、删除和查看 事件调度器 中的事件
EXECUTE Execute_priv 存储过程或存储函数 Global, Database, Routine 允许执行存储过程和存储函数
FILE File_priv 文件访问 Global 允许使用 LOAD DATA INFILE 和 SELECT … INTO OUTFILE 语句
GRANT OPTION Grant_priv 数据库, 数据表, 存储过程或存储函数 Global, Database, Table, Routine, Proxy 允许对其他用户进行授权或者撤销权限
INDEX Index_priv 数据表 Global, Database, Table 允许创建及删除索引
INSERT Insert_priv 数据表, 数据列 Global, Database, Table, Column 允许插入数据到数据表
LOCK TABLES Lock_tables_priv 数据库 Global, Database 允许使用 LOCK TABLES 语句对具有 SELECT 权限的查询进行显式加锁
PROCESS Process_priv 服务器管理 Global 允许用户使用 SHOW PROCESSLIST 语句查看所有进程，包括其他用户的进程
PROXY proxies_priv table 服务器管理 Global 允许用户模仿（伪装、代理）另一个用户
REFERENCES References_priv 数据库, 数据表 Global, Database, Table, Column 在创建外键时，需要用户具有父表的 REFERENCES 权限
RELOAD Reload_priv 服务器管理 Global 允许使用 FLUSH 语句刷新，包括 FLUSH HOSTS、FLUSH LOGS、FLUSH PRIVILEGES、FLUSH STATUS、FLUSH SLOW LOGS、FLUSH TABLES WITH READ LOCK 等
REPLICATION CLIENT Repl_client_priv 服务器管理 Global 允许用户使用 SHOW MASTER STATUS、SHOW SLAVE STATUS、SHOW BINARY LOGS 等语句查看源与副本的信息
REPLICATION SLAVE Repl_slave_priv 服务器管理 Global 该权限主要用于副本服务器访问源服务器的 Binlog 文件权限
SELECT Select_priv 数据表, 数据列 Global, Database, Table, Column 允许使用 SELECT 语句查询数据
SHOW DATABASES Show_db_priv 服务器管理 Global 允许使用 SHOW DATABASES 语句查看所有数据库
SHOW VIEW Show_view_priv 视图 Global, Database, Table 允许使用 SHOW CREATE VIEW 语句查看视图的创建语句
SHUTDOWN Shutdown_priv 服务器管理 Global 用于 SHUTDOWN 语句、 mysqladmin shutdown 语句、mysql_shutdown() 函数
SUPER Super_priv 服务器管理 Global 启用其他管理操作的使用，例如 CHANGE MASTER TO、KILL、Purge Binary Logs、SET GLOBAL 和 mysqladmin DEBUG 等命令
TRIGGER Trigger_priv 数据表 Global, Database, Table 允许创建及删除触发器
UPDATE Update_priv 数据表, 数据列 Global, Database, Table, Column 允许使用 UPDATE 语句更新数据
USAGE Synonym for “no privileges” 服务器管理 Global 没有权限，等同于没有权限

授权语法

1
2
3
4
5
6
7
8
9
10
11
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: 对象类型

  • TABLE - 数据表
  • FUNCTION - 函数
  • PROCEDURE - 存储过程

priv_level: 权限级别

  • * - 所有数据库
  • *.* - 所有数据库的所有数据表
  • db_name.* - 指定数据库的所有数据表
  • db_name.tbl_name - 指定数据库的指定数据表
  • tbl_name - 指定数据表
  • db_name.routine_name - 指定数据库的指定存储过程或函数

user、auth_option、tls_option、resource_option: 同创建用户

示例：

1
2
3
4
-- 授予用户 test1 所有权限，且允许授予其他用户权限
GRANT ALL PRIVILEGES ON *.* TO 'test1'@'%' WITH GRANT OPTION;
-- 授予用户 test2 对数据库 db1 的CRUD权限
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'test2'@'%';

撤销授权

1
2
3
4
5
6
7
8
9
10
11
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ..

示例

1
2
3
4
5
6
-- 撤销用户 test1 的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'test1'@'%';
-- 撤销用户 test2 在数据库 db1 中的所有权限
REVOKE ALL PRIVILEGES ON db1.* FROM 'test2'@'%';
-- 撤销用户 test3 在数据表 test_table 中的增删改权限
REVOKE INSERT, UPDATE, DELETE ON test_table FROM 'test3'@'%';

References