33. MySQL 权限与安全

MySQL 的权限系统主要用来对连接到数据库的用户进行权限的验证，以此来判断此用户是否属于合法的用户，如果是合法用户则赋予相应的数据库权限。

1. MySQL 权限管理⚓

MySQL 权限系统的工作原理和账号管理。

1.1 权限系统的工作原理⚓

MySQL 权限系统通过下面两个阶段进行认证：

对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接；
对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作。

MySQL 的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。

当用户进行连接的时候，权限表的存取过程有以下两个阶段：

先从 user 表中的 host、user 和 password 这 3 个字段中判断连接的 IP、用户名和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。
如果通过身份验证，则按照以下权限表的顺序得到数据库权限：

user->db->tables_priv->columns_priv。在这些权限表中，权限范围依次递减，全局权限覆盖局部权限。

1.2 账号管理⚓

1.3 创建账号与分配权限⚓

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see http://dev.mysql.com/doc/refman/8.0/en/account-names.html)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

-- 账号资源限制
resource_option: {
    -- 单个账号每小时最大查询次数，隐式查询和很多非select语句也算在内
    MAX_QUERIES_PER_HOUR count
    -- 单个账号每小时最大更新次数
  | MAX_UPDATES_PER_HOUR count
    -- 单个账号每小时最大连接次数
  | MAX_CONNECTIONS_PER_HOUR count
    -- 最大用户连接数，指瞬间的并发数
  | MAX_USER_CONNECTIONS count
}
-- 如果达到限制，可以执行"flush user_resources/flush privileges/mysqladmin reload"三个命令中的任何一个清除。重启数据库也可以。
-- 如果要对资源解除限制，可以将相应参数设置为0

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

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

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

priv_type:
    (see https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html)

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user
  | role
}

user:
    (see http://dev.mysql.com/doc/refman/8.0/en/account-names.html)

role:
    (see http://dev.mysql.com/doc/refman/8.0/en/role-names.html)

MySQL 8.0.11之后移除了GRANT添加用户的功能，要先创建用户，然后再分配权限：

CREATE USER test_user@localhost IDENTIFIED BY 'MyNewPass2!';
-- 赋予所有数据库上的所有表的 select 权限，此时user表的 Select_priv = Y，而db表的 Select_priv = N
GRANT SELECT ON *.* TO test_user@localhost;
-- 收回权限
REVOKE SELECT ON *.* FROM test_user@localhost;
-- 赋予blog数据库的所有表的select权限，此时db表的 Select_priv = Y，而user表的 Select_priv = N
GRANT SELECT ON blog.* TO test_user@localhost;

举例

-- 赋予所有表的所有权限
GRANT ALL ON *.* TO test_user@localhost;
-- 查询发现除了 Grant_priv = N，其余的权限都是Y
SELECT * FROM user WHERE USER='test_user' \G;
-- 同时赋予 Grant_priv权限，此时再次查询，Grant_priv = Y
GRANT ALL ON *.* TO test_user@localhost WITH GRANT OPTION;

用户名的host部分：

可以是主机名或IP，或‘localhost’。
可以使用LIKE的通配符%和_。
'%'的host值与所有的主机名匹配，空值等价于'%'。但是不适用本地主机，必须单独为localhost赋予权限。

空的username部分表示任何用户都可以连接

当有多个匹配的规则可以匹配时，按以下原则选取：

当服务器读取表时，它首先以最具体的 Host 值排序，有相同 Host 值的条目首先以最具体的 User 值排序。

管理权限（SUPER、PROCESS、FILE ）不能够指定某个数据库，on 后面必须跟“.” ：

mysql> GRANT SUPER ON blog.* TO test_user@localhost WITH GRANT OPTION;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

1.4 查看和更改账号权限⚓

1.4.1 查看权限⚓

SHOW GRANTS FOR 'username'@'host';

SHOW GRANTS FOR test_user@localhost \G;

host 可以不写，默认是“%” (必须有'username'@'host'这个用户才可以)：

SHOW GRANTS FOR test_user;

1.4.2 更改权限⚓

可以再次使用GRANT语句更改用户的权限。注意，是直接更改，而不是增加权限。

回收权限可以使用REVOKE语句:

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

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

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

REVOKE role [, role ] ...
    FROM user_or_role [, user_or_role ] ...

user_or_role: {
    user
  | role
}

user:
    (see http://dev.mysql.com/doc/refman/8.0/en/account-names.html)

role:
    (see http://dev.mysql.com/doc/refman/8.0/en/role-names.html)

注意：REVOKE 不能回收用户的USAGE（登录权限）权限，即不能删除用户。

1.4.3 修改密码⚓

方法1：使用mysqladmin命令

$ mysqladmin -u root -p -h localhost password 'MyPass1!'

方法2：执行SET PASSWORD语句

SET PASSWORD [FOR user] = 'auth_string'
    [REPLACE 'current_auth_string']
    [RETAIN CURRENT PASSWORD]

SET PASSWORD FOR 'user3'@'www.ht.com'='xxxx';

如果是更改自己的密码，可以省略 for 语句。

方法 3：直接更改数据库的 user 表。

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey',PASSWORD('biscuit'));
mysql> FLUSH PRIVILEGES;
shell> mysql -u root mysql
mysql> UPDATE user SET Password = PASSWORD('bagel') WHERE Host='%' AND User='francis';
mysql> FLUSH PRIVILEGES;

PASSWORD(str)函数在MySQL 8.0.11被移除了。

方法4：以上方法在修改密码时使用的是明文，存在风险。可以直接使用MD5密码值对密码进行更改：

GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD 'XXXXXXXXX';
或
SET PASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxx';

1.4.4 删除账号⚓

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

2. MySQL 安全问题⚓

2.1 操作系统相关⚓

主要出现在 MySQL 的安装和启动过程中。

2.1.1 严格控制操作系统账号和权限⚓

在数据库服务器上要严格控制操作系统的账号和权限，比如：

锁定 mysql 用户；
其他任何用户都采取独立的账号登录，管理员通过 mysql 专有用户管理 MySQL，或者通过 root su 到 mysql 用户下进行管理；
mysql 用户目录下，除了数据文件目录，其他文件和目录属主都改为 root。

2.1.2 尽量避免以 root 权限运行 MySQL⚓

MySQL 安装完毕后，一般会将数据目录属主设置为 mysql 用户，而将 MySQL 软件目录的属主设置为 root，这样做的目的是当使用 mysql 用户启动数据库时，可以防止任何具有 FILE权限（例如导出数据到文件中）的用户能够用 root 创建文件。而如果使用 root 用户启动数据库，则任何具有 FILE 权限的用户都可以读写 root 用户的文件，这样会给系统造成严重的安全隐患。

对于某些 Linux 平台,从 RPM 或 Debian 软件包安装的 MySQL 包括用于管理 MySQL 服务器启动和关闭的systemd支持。这些平台上不会安装mysqld_safe，因为不需要。see Managing MySQL Server with systemd.

2.1.3 防止 DNS 欺骗⚓

创建用户时，host 可以指定域名或者 IP 地址。但是，如果指定域名，就可能带来如下安全隐患：如果域名对应的 IP 地址被恶意修改，则数据库就会被恶意的 IP 地址进行访问，导致安全隐患。

2.2 数据库相关⚓

问题大多数是由于账号的管理不当造成的。

2.2.1 删除匿名账号⚓

在某些版本中，安装完毕 MySQL 后，会自动安装一个空账号，此账号具有对 test 数据库的全部权限。普通用户只需要执行 mysql 命令即可登录 MySQL 数据库，这个时候默认使用了空用户，可以在 test 数据库里面做各种操作，比如可以创建一个大表，占用大量磁盘空间，这样将给系统造成安全隐患。

建议删除此空账号，或者对此账号加密码。

DROP USER ''@'localhost';

2.2.2 给 root 账号设置口令⚓

某些 MySQL 安装完毕后，root 默认口令为空，需要马上修改 root 口令。

2.2.3 设置安全密码⚓

使用密码期间尽量保证使用过程安全，不会被别人窃取。

使用密码一般是采用以下几种方式：

直接将密码写在命令行中。
交互式方式输入密码。
将用户名和密码写在配置文件里面，连接的时候自动读取。比如应用连接数据库或者执行一些批处理脚本。对于这种方式，MySQL 供了一种方法，在 my.cnf 里面写入连接信息。

[client]
user=username
password=password

然后对配置文件进行严格的权限限制：

chmod +600 my.cnf

很显然，第 1 种最不安全，因为它将密码写成为明文；第2 种比较安全，但是只能使用在交互式的界面下；第 3 种使用比较方便，但是需要将配置文件设置严格的存取权限，而且任何只要可以登录操作系统的用户都可以自动登录，存在一定的安全隐患。

2.2.4 只授予账号必须的权限⚓

只需要赋予普通用户必须的权限，比如：

Grant select,insert,update,delete on tablename to ‘username’@’hostname’;

赋予用户权限的时候越具体，则对数据库越安全。

2.2.5 除 root 外，任何用户不应有 mysql 库 user 表的存取权限⚓

由于 MySQL 中可以通过更改 mysql 数据库的 user 表进行权限的增加、删除、变更等操作，因此，除了 root 以外，任何用户都不应该拥有对 user 表的存取权限（SELECT、UPDATE、INSERT、DELETE 等），造成系统的安全隐患。

2.2.6 不要把 FILE、PROCESS 或 SUPER 权限授予管理员以外的账号⚓

FILE 权限主要以下作用：

将数据库的信息通过 SELECT …INTO OUTFILE…写到服务器上有写权限的目录下，作为文本格式存放。具有权限的目录也就是启动 MySQL 时的用户权限目录。
可以将有读权限的文本文件通过 LOAD DATA INFILE…命令写入数据库表，如果这些表中存放了很重要的信息，将对系统造成很大的安全隐患。

PROCESS 权限能被用来执行show processlist命令，查看当前所有用户执行的查询的明文文本，包括设定或改变密码的查询。在默认情况下，每个用户都可以执行 show processlist命令，但是只能查询本用户的进程。

SUPER 权限能执行 kill命令，终止其他用户进程。

mysql> SHOW PROCESSLIST ;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  614 | Waiting on empty queue | NULL             |
|  9 | root            | localhost | blog | Query   |    0 | starting               | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.01 sec)

mysql> KILL 4;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    10
Current database: blog

ERROR 1094 (HY000): Unknown thread id: 4
mysql>

2.2.7 LOAD DATA LOCAL 带来的安全问题⚓

LOAD DATA 默认读的是服务器上的文件，但是加上 LOCAL 参数后，就可以将本地具有访问权限的文件加载到数据库中。这在带来方便的同时，也带来了以下安全问题。

可以任意加载本地文件到数据库。
在 Web 环境中，客户从 Web 服务器连接，用户可以使用 LOAD DATA LOCAL 语句来读取 Web 服务器进程有读访问权限的任何文件（假定用户可以运行 SQL 服务器的任何命令）。在这种环境中，MySQL 服务器的客户实际上是 Web 服务器，而不是连接 Web 服务器的用户运行的程序。

解决方法是，可以用--local-infile=0 选项启动 mysqld 从服务器端禁用所有 LOAD DATA LOCAL 命令。对于 mysql 命令行客户端，可以通过指定--local-infile[=1]选项启用 LOAD DATA LOCAL，或通过--local-infile=0 选项禁用。类似地，对于 mysqlimport，--local or -L 选项启用本地数据文件装载。在任何情况下，成功进行本地装载需要服务器启用相关选项。

2.2.8 使用 MERGE 存储引擎潜藏的安全漏洞⚓

MERGE 存储引擎的表在某些版本中可能存在以下安全漏洞：

用户 A 赋予表 T 的权限给用户 B；
用户 B 创建一个包含 T 的 MERGE 表，做各种操作；
用户 A 收回对 T 的权限。

存在的安全隐患是用户 B 通过 merge 表仍然可以访问表 A 中的数据。

2.2.9 DROP TABLE 命令并不收回以前的相关访问授权⚓

DROP 表的时候，其他用户对此表的权限并没有被收回，这样导致重新创建同名的表时，以前其他用户对此表的权限会自动赋予，进而产生权限外流。因此，在删除表时，要同时取消其他用户在此表上的相应权限

2.2.10 使用 SSL⚓

SSL（Secure Socket Layer，安全套接字层）是一种安全网络传输协议。

SSL 协议提供的服务主要有：（1）认证用户和服务器，确保数据发送到正确的客户机和服务器；（2）加密数据以防止数据中途被窃取；（3）维护数据的完整性，确保数据在传输过程中不被改变。

在 MySQL 中，要想使用 SSL 进行安全传输，需要在命令行中或选项文件中设置--ssl选项：

对于服务器，“--ssl”选项规定该服务器允许 SSL 连接。
对于客户端程序，它允许客户使用SSL 连接服务器。

单单该选项不足以使用 SSL 连接。还必须指定--ssl-ca、--ssl-cert、--ssl-key选项。

--ssl-ca=file_name 含可信 SSL CA 的清单的文件的路径。
--ssl-cert=file_name SSL 证书文件名，用于建立安全连接。
--ssl-key=file_name SSL 密钥文件名，用于建立安全连接。

确保使用 SSL 连接的安全方式是，使用含 REQUIRE SSL 子句的 GRANT 语句在服务器上创建一个账户，然后使用该账户来连接服务器，服务器和客户端均应启用 SSL 支持。

mysql> grant select on *.* to z4 identified by '123' REQUIRE ssl;

2.2.11 如果可能，给所有用户加上访问 IP 限制⚓

2.2.12 REVOKE 命令的漏洞⚓

当用户对多次赋予权限后，由于各种原因，需要将此用户的权限全部取消，此时，REVOKE 命令可能并不会按照我们的意愿执行。

在一个数据库上多次赋予权限，权限会自动合并；但是在多个数据库上多次赋予权限，每个数据库上都会认为是单独的一组权限，必须在此数据库上用 REVOKE 命令来单独进行权限收回，而 REVOKE ALL PRIVILEGES ON *.* 并不会替用户自动完成这个过程。

3. 其他安全设置选项⚓

除了上面介绍的那些需要注意的安全隐患外，MySQL 本身还带着一些选项，适当地使用这些选项将会使数据库更加安全。

3.1 old-passwords⚓

在 MySQL 4.1 版本之前，PASSWORD 函数生成的密码是 16 位。4.1 以后，MySQL 改进了密码算法，生成的函数值变成了 41 位。在MySQL 8.0.11后被移除。

这样就会出现一个问题，当 4.1 以后的客户端连接 4.1 以前的客户端时，没有问题，因为新客户端可以理解新旧两种加密算法。但是反过来，当 4.1 以前的客户端需要连接 4.1 以后的服务器时候，由于无法理解新的密码算法，发到服务器端的密码还是旧的算法加密后的结果，于是导致在新的服务器上出现下面无法认证的情况。

对于这个问题，可以采用以下两种办法解决：（1）在服务器端用 OLD_PASSWORD 函数更改密码为旧密码格式，客户端先可以进行正常连接：

mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

（2）在 my.cnf 的[mysqld]中增加 old-passwords 参数并重启服务器，这样新的数据库连接成功之后做的 set password、grant、password() 操作后，生成的新密码全部变成旧的密码格式。

3.2 safe-user-create⚓

mysqld_safe （一些系统托管给systemd管理）的选项--safe-user-create如果启用，用户将不能用 GRANT 语句创建新用户，除非用户有 mysql 数据库中user 表的 INSERT 权限。如果想让用户具有授权权限来创建新用户，应给用户授予下面的权限：

mysql> GRANT INSERT(user) ON mysql.user TO 'user_name'@'host_name';

这样确保用户不能直接更改权限列，必须使用 GRANT 语句给其他用户授予该权限。

$ mysqld_safe --safe-user-create

3.3 secure-auth⚓

作用是让 MySQL 4.1 以前客户端无法进行用户认证。即使使用了old-passwords 参数也不能认证。

This option was removed in MySQL 8.0.3。

3.4 skip-grant-tables⚓

mysqld_safe 的选项 --skip-grant-tables 这个选项导致服务器根本不使用权限系统，从而给每个人以完全访问所有数据库的权力。通过执行 mysqladmin flush-privileges 或 mysqladmin reload 或 flush privileges 语句，都可以让一个正在运行的服务器再次开始使用授权表。

$ mysqld_safe --skip-grant-tables

3.5 skip-networking⚓

--skip-networking在网络上不允许 TCP/IP 连接，所有到数据库的连接必须经由命名管道（Named Pipes）或共享内存（Shared Memory）或 UNIX 套接字（SOCKET）文件进行。

这个选项适合应用和数据库共用一台服务器的情况，其他客户端将无法通过网络远程访问数据库，大大增强了数据库的安全性，但管理不便。

3.6 skip-show-database⚓

使用--skip-show-database选项，只允许有 show databases 权限的用户执行 show databases 语句，该语句显示所有数据库名。