《MySQL》数据定义语言DDL管理用户

权限表

可以通过全局（对应 user 表）、数据库（对应 db 表）、数据表（对应 tables_priv 表）、字段（对应 columns_priv 表）四个层面对用户权限做出限定。MySQL 是从左到右依次检查权限的的，有权限则直接执行，无权限则进行下一层检查。所以，在任意一个层面上有权限，即为有权限。

user 表

记录允许连接到服务器的账号信息，以及对数据和数据库的全局的操作权限

user 表的字段分为以下四类：

1. 用户列

   其中 Host User 是 user 表的联合主键，旧版 Password 记录对应的用户密码，新版本记录在 authentication_string

2. 权限列

   _priv 后缀的列为权限列，这些字段的类型为 ENUM，可以取的值只有 Y 和 N。

3. 安全列

   安全列只有 6 个字段，2 个与 ssl 相关的，用于加密；2 个与 x509 相关的，用于标识用户；plugin 字段标识可以用于验证用户身份的插件；

4. 资源控制列用来限制用户使用的资源：

   • max_questions： 每小时允许执行的查询操作次数

   • max_updates： 每小时允许执行的更新操作次数

   • max_connections： 每小时允许执行的连接操作次数

   • max_use_connections：允许同时建立的连接次数

db 表

记录特定用户在特定主机上对特定数据库的操作权限。

db 表的字段分为以下两类：

1. 用户列

   Host User Db 三个字段组成联合主键

2. 权限列

   _priv 后缀的列为权限列，这些字段的类型为 ENUM，可以取的值只有 Y 和 N。

tables_priv

记录特定用户在特定主机上对特定数据表的操作权限

1. Host User Db Table_name 四个字段组成联合主键

2. Grantor 表示修改该记录的用户

3. Timestamp 表示修改该记录的时间

4. Table_priv 表示对表的操作权限，字段的类型为 SET

5. column_priv 表示对字段的操作权限，字段的类型为 SET

columns_priv 表

记录特定用户在特定主机上对特定数据表的特定字段的操作权限。

1. Host User Db Table_name column_name 五个字段组成联合主键

2. Timestamp 表示修改该记录的时间

3. column_priv 表示对字段的操作权限，字段的类型为 SET

procs_priv 表

记录特定用户在特定主机上对特定存储过程（或存储函数）的操作权限

1. Host User Db Routine_name Routine_type 五个字段组成联合主键， Routine_name 表示存储过程（或存储函数）的名称； Routine_type 表示类型 FUNCTION 或 PROCEDURE

2. Grantor 表示修改该记录的用户

3. Timestamp 表示修改该记录的时间

4. Proc_priv 表示拥有的操作权限，字段的类型为 SET

访问控制

访问控制分为两个阶段，连接核实阶段和请求核实阶段：

1. 连接核实阶段，核实用户名、主机地址和密码，核实通过建立连接，否则拒绝访问。

2. 请求核实阶段，核实当前连接上的所有请求检查当前用户是否有足够的权限来执行。

   

   MySQL请求核实过程

创建用户

CREATE 语句创建用户

CREATE USER [IF NOT EXISTS] <用户名>@<主机名>
[{IDENTIFIED BY [PASSWORD] '<密码>' | IDENTIFIED WITH '<身份验证插件名称>' BY '<密码>'}]
[WITH <资源限制>]

• 主机名设置为通配符 %，在所有主机均可访问

• 登录用户不需要密码，则可以省略 IDENTIFIED BY 部分

• 设置的密码默认为明文密码，[PASSWORD] 表示使用哈希值设置密码，密码的哈希值可以通过 password() 获取。

• 资源限制可用的选项有：

  1. MAX_QUERIES_PER_HOUR 每小时最大查询次数

  2. MAX_UPDATES_PER_HOUR 每小时最大更新次数

  3. MAX_CONNECTIONS_PER_HOUR 每小时最大连接次数

  4. MAX_USER_CONNECTIONS 同时建立的连接个数

• 使用 CREATE 语句创建的用户没有任何权限

GRANT 语句创建用户

GRANT 语句不但可以创建用户，同时赋予用户权限，也可以单独对用户授权或修改用户密码。MySQL8起 GRANT 只用于授权，必须先 CREATE 创建用户，再 GRANT 授权。

下文授权管理中详细介绍 GRANT 语句的用法。

INSERT 语句操作 mysql.user 表

INSERT INTO `mysql`.`user` .....

修改用户

ALTER 语句

ALTER USER <用户名>@<主机名>
[{IDENTIFIED BY [PASSWORD] '<密码>' | IDENTIFIED WITH '<身份验证插件名称>' BY '<密码>'}]
[WITH <资源限制>]

UPDATE 语句操作 mysql.user 表

UPDATE `mysql`.`user` .....

删除用户

DROP 语句

DROP USER [IF EXISTS] <用户1>,[<用户2>...]

删除一个或多个用户

DELETE 语句操作 mysql.user 表

DELETE FROM `mysql`.`user` .....

修改密码

通过 mysqladmin

mysqladmin -u <用户> -h <主机> -p password <新密码>

由于新密码在命令行以明文形式出现，系统会提示警告，建议使用 SSL 连接

通过 SET（MySQL8 以前）

SET PASSWORD [FOR <用户名>@<主机名>] = PASSWORD('<密码>')

使用 FOR 指定要修改密码的用户，省略时表示修改自身的密码。用于 root 或普通用户修改自身密码，或者 root 用户修改普通用户的密码。

此命令在 MySQL8 中不再支持

通过 ALTER （MySQL8以后）

详见上文 ALTER 修改用户的语法说明

通过 UPDATE 修改 mysql.user 表的密码字段

UPDATE `mysql`.`user` .....

root 用户密码丢失的解决办法

1. 先停止 MySQL服务

2. 在命令行界面（一）使用 --skip-grant-tables 选项临时启动 MySQL 进程。MySQL 将不加载权限判断，任何用户都可以访问数据库

   # MySQL8之前
   mysqld --skip-grant-tables
   # MySQL8
   mysqld --skip-grant-tables --shared-memory

3. 在另一个命令行界面（二），不使用密码登录 MySQL

   mysql -uroot

4. 执行命令 flush privileges; 刷新权限后才有权限修改密码

5. 使用 ALTER USER 修改密码

6. 关闭命令行界面（一），停止临时的 MySQL 进程

7. 启动 MySQL 服务

授权管理

为指定用户授权

GRANT <权限类型>[(<字段列表>)] [,<权限类型>[(<字段列表>)]...]
ON <数据库>.<数据表> [,<数据库>.<数据表>...]
TO <用户名>@<主机名> [IDENTIFIED BY '<密码>'] [,<用户名>@<主机名> [IDENTIFIED BY '<密码>']...]

1. 部分权限类型

   • CREATE 、ALTER 、DROP

     创建、修改、删除数据库和数据表，以及删除视图

   • INDEX

     创建和删除索引

   • INSERT 、DELETE、UPDATE 、SELECT

     操作数据表中的数据

   • CREATE ROUTINE

     创建存储过程和存储函数

   • ALTER ROUTINE

     修改、删除存储过程和存储函数

   • EXECUTE

     执行存储过程和存储函数

   • CREATE VIEW

     创建视图

   • SHOW VIEW

     使用 SHOW CREATE VIEW <视图名> 查看视图，不限制 DESC <视图名>

   • SHOW DATABASES

     查看数据库

   • GRANT OPTION

     允许将自身拥有的权限授权给其他用户

   • ALL

     除 GRANT OPTION 外的所有权限

2. 权限层级

   • 全局层级

     数据库和数据表均指定为 *

   • 数据库层级

     指定特定数据库，数据表指定为 *

   • 数据表层级

     指定特定的数据库和特定的数据表

   • 列层级

     指定特定的数据库和特定的数据表，且在<权限类型>后指定字段列表

[IDENTIFIED BY '<密码>'] 为可选参数，在 MySQL8 之前可用，用来创建或修改用户时指定密码

收回授权

1. 收回所有层级的权限

   REVOKE ALL,GRANT OPTION FROM <用户名>@<主机名> [,<用户名>@<主机名>...]

2. 收回指定权限

   REVOKE <权限类型>[(<字段列表>)] [,<权限类型>[(<字段列表>)]...]
   ON <数据库>.<数据表> [,<数据库>.<数据表>...]
   FROM <用户名>@<主机名> [,<用户名>@<主机名>...]

查看权限

SHOW GRANTS FOR <用户名>@<主机名>

刷新权限

使用 INSERT UPDATE DELETE 直接操作 mysql.user 表（或其他权限表）后，权限设置并没有生效，要想生效必须从 mysql 库提取到内存中。

有两种方法：

1. 重启服务器

2. 使用 flush privileges 命令

CREATE 和 DROP 语句的使用对比