是不是史上最全的MySQL用户和权限原理实战你说了算

前言

在学习这一章节的时候，我们可以先了解一下SQL语言,SQL语言共分为四大类：

• 数据查询语言DQL（Data QueryLanguage）：select 等
• 数据操纵语言DML（database manage language）：insert update delete 等
• 数据定义语言DDL（Data Definition Language）： create drop alter runcate 等
• 数据控制语言DCL（Data Control Language）： grant revoke commit rollback 等

本文会频繁使用到DCL 数据控制语言

点击此处可以了解关系数据库标准语言SQL的概念和发展历史

一、访问控制

1.1 连接验证（阶段1）

当我们连接MySQL的时候,MySQL会以下面2点进行连接验证

• 通过提供的账号和密码来验证身份
• 验证账号是否锁定

服务首先检查凭据，然后检查账户的锁定状态。任一步骤失败都会拒绝访问，没有问题就接收连接。锁定状态记录在user表account_locked列中。

mysql> select user,account_locked from user;
+--------+----------------+
| user   | account_locked |
+--------+----------------+
| root   | N              |
| multis | N              |
| multis | N              |
| root   | N              |
+--------+----------------+
4 rows in set (0.00 sec)

1.2 允许的连接

下表展示了User和Host值在各种的组合下， user表适用于传入的连接

1.3 连接的优先级

当有多个匹配项时，需要确认使用哪个匹配项，按照以下的方式解决问题

• 当服务器将user表读入内存，它会对user表进行排序
• 客户端尝试连接时，服务器将按排序顺序浏览各行
• 服务器使用与客户端主机名和用户名匹配的第一行

排序顺序

• 优先Host进行排序，其次User
• 有具体文字比%更具有高优先级（127.0.0.1比localhost高）

示例一
要查看其工作原理，假设user 表如下所示：

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-

读入到内存时，自动排序后的结果如下所示：

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-

挡localhost 上的root用户连接mysql时候，这个时候会匹配| localhost | root |这个条目对应的权限。 其他条目虽然也满足， 但不会匹配它们对应的权限。

示例二

我们创建'test'@'%'和''@'192.168.79.140’两个用户，进行连接的优先级的测试

[root@mysql2 ~]# mysql -root-p123456
## 忽略部分登陆代码 ##
mysql> create user ''@'192.168.79.140' identified by '123456';
mysql> create user 'test'@'%' identified by '123456';
mysql> select host,user from user;
+----------------+------+
| host           | user |
+----------------+------+
| %              | test |
| 192.168.79.140 |      |
| localhost      | root |
+----------------+------+
3 rows in set (0.00 sec)
mysql> exit;

使用我们刚才创建的'test'@'%'用户登录mysql

[root@mysql2 ~]# mysql -utest -p123456 -h192.168.79.140

按照mysql连接的优先级，用户在登陆mysql时，mysql读入到内存自动排序后的结果应如下所示：

+----------------+------+
| host           | user |
+----------------+------+
| 192.168.79.140 |      |
| localhost      | root |
| %              | test |
+----------------+------+

当前登录的'test'@'%'用户匹配连接是| 192.168.79.140 | |，我们可以通过CURRENT_USER()函数查验证当前用户是否匹配| 192.168.79.140 | |

mysql> select current_user();
+-----------------+
| current_user()  |
+-----------------+
| @192.168.79.140 |
+-----------------+
1 row in set (0.00 sec)

验证结果如我们推测一致，正式坏境中建议不要创建有不带用户名只有主机的用户。

1.4 请求验证（阶段二）

MySQL建立连接后，服务器进入访问控制的第二阶段。该连接发出的每个请求，会检查是否具有足够的权限，这些权限对应着如下这些表：

• user 用户（全局）
• db 数据库
• tables_priv 表
• columns_priv 列
• procs_priv 存储过程
• proxies_priv代理用户权限

二、用户管理

在这里我们需要知道的是mysql的用户名和主机名在一起才是一个有效的用户

2.1 新增用户

MySQL的语法和Oracle是相似的，如下

create user 'user'@'localhost' identified by 'passwd' [ACCOUNT UNLOCK]

ACCOUNT UNLOCK 用户默认是没有锁定的，如果用户锁定，我们登录的用户不会有第二阶段的请求验证。

我们创建一个本地可以登陆的multis用户，该用户默认是未锁定的状态。

mysql> create user 'multis'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

2.2 用户修改

MySQL是可以通过数据操纵语言DML和数据定义语言DDL来进行修改的

2.2.1 使用DDL语言修改用户

我们查询当前数据库所有的用户名、主机和用户的状态

mysql> select user,host,account_locked from user;
+--------+-----------+----------------+
| user   | host      | account_locked |
+--------+-----------+----------------+
| root   | localhost | N              |
| multis | localhost | N              |
+--------+-----------+----------------+
2 rows in set (0.00 sec)

1、我们使用alter修改'multis'@'localhost'用户状态为锁定状态

mysql> alter user 'multis'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,account_locked from user;
+--------+-----------+----------------+
| user   | host      | account_locked |
+--------+-----------+----------------+
| root   | localhost | N              |
| multis | localhost | Y              |
+--------+-----------+----------------+
2 rows in set (0.00 sec)

重新打开一个窗口进行mysql登陆，登陆的时候，MySQL报错ERROR 3118 (HY000): Access denied for user 'multis'@'localhost'. Account is locked.帐号被锁定,用户被拒绝访问。

[root@mysql2 ~]# mysql -umultis -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'multis'@'localhost'. Account is locked.

2、我们使用alter修改'test'@'localhost'用户的密码

mysql> alter user 'test'@'localhost' identified by '654321';
Query OK, 0 rows affected (0.01 sec)

我们登陆'test'@'localhost'用户验证

[root@mysql2 ~]# mysql -utest -p654321
## 省略部分登陆提示 ##
mysql> 

'test'@'localhost'用户使用修改后密码登陆成功

2.2.2 DML语句管理mysql用户

我们也可以直接更新mysql.user表的信息，进行用户的管理，但是需要flush privileges，将内存和磁盘中的数据保持一致。

1、修改'multis' @'localhost'用户用户名为multis1

mysql> update mysql.user set user = 'multis1' where user = 'multis' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询mysql.user表,进行校验

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| multis1 | localhost |
| root    | localhost |
+---------+-----------+
2 rows in set (0.00 sec)

2、修改'multis1' @'localhost'用户host为127.0.0.1

mysql> update mysql.user set host = '127.0.0.1' where user = 'multis1' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

我们不刷新flush privileges，查询'multis1' @'localhost'用户信息

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| multis1 | localhost |
| root    | localhost |
+---------+-----------+
2 rows in set (0.00 sec)

发现虽然更改了host,但是还以前的host,我们flush privileges后，继续查询'multis1' @'localhost'用户信息

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| multis1 | 127.0.0.1 |
| root    | localhost |
+---------+-----------+
2 rows in set (0.00 sec)

2.3 删除用户

方法一：drop user

mysql> create user 'test'@'localhost' identified by '123456' account unlock;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec

方案二：delete

mysql> create user 'test'@'localhost' identified by '123456' account unlock;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user = 'test' and host='localhost';
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

某个用户已经被我们删除了，但是已经通过该用户连接进来的是不受影响的。那么如何清理掉这些用户,断开连接呢？

查询已经删除用户id，生成SQL

mysql> select concat('kill ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/kill.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

报错原因： 没有指定secure_file_priv参数
解决方法： secure_file_priv是MySQL中的一个系统变量，用于限制LOAD DATA INFILE和SELECT … INTO OUTFILE语句的文件读写权限。它指定了允许从哪个目录中读取或写入文件。开启secure_file_priv参数，须在配置文件中修改，并重启数据库。

查询secure_file_priv参数，当前参数值为null

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL            |
+--------------------+
1 row in set (0.00 sec)

我们可以在my.cnf文件中指定：

[mysqld]
secure_file_priv=/tmp/

也可以通过命令来实时修改，可以随时生效而且永久生效（MySQL 8.0 推荐使用）：

mysql> set persist secure_file_priv=/tmp/;
Query OK, 0 rows affected (0.00 sec)

重新查询secure_file_priv参数，当前参数值为/tmp/

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/              |
+--------------------+
1 row in set (0.00 sec)

继续查询清理的用户id，生成SQL

mysql> select concat('kill ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/kill.sql';
Query OK, 1 row affected (0.00 sec)

mysql> source /tmp/kill.sql

OK，已删除的用户就已断开

2.4 查看用户

2.4.1 show create user 显示用户非权限属性

mysql> create user test@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)

ERROR: 
No query specified

2.4.2 select * from mysql.user 查看已经创建的用户

mysql> select * from mysql.user where user='test' and host='localhost' \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: test
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      password_expired: N
 password_last_changed: 2023-04-18 22:00:36
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

ERROR: 
No query specified

2.4.3 select current_user() 查看当前登录用户

mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

三、密码管理

3.1 修改密码（最终版）

3.1.1 alter修改密码（推荐使用）

mysql> alter user 'test'@'localhost' identified by '654321';
Query OK, 0 rows affected (0.01 sec)

3.1.2 mysqladmin修改密码

[root@mysql2 ~]# mysqladmin -utest -p654321 password
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

3.1.3 set password修改密码

1 、使用默认加密

mysql> set password for 'test'@'%' ='abcdefg';
Query OK, 0 rows affected (0.01 sec)

2、使用PASSWORD()函数加密（mysql使用该方式进行密码加密）

mysql> set password for 'test'@'%' =password('123456');
Query OK, 0 rows affected (0.01 sec)

3.1.4 update(不推荐使用)

mysql> update mysql.user set authentication_string=password('Aroot123#') where user='dba';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.1.5 修改当前登录用户密码

mysql> alter user current_user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

3.2 密码策略

在设置密码策略时 identified by 'password' 可以忽略

3.2.1 密码过期策略

MySQL版本5.7.4版本起，可以通过全局变default_password_lifetime来设置全局的密码过期策略。主要有以下四种策略

1、密码立即过期策略 PASSWORD EXPIRE

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
重置密码后才能正常登陆

我们修改'test'@'localhost'用户密码立即过期，查询改用户的非权限属性

mysql> alter user 'test'@'localhost' identified by '123456' password expire;
Query OK, 0 rows affected (0.00 sec)

mysql> show create user 'test'@localhost \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK
1 row in set (0.00 sec)

ERROR: 
No query specified

我们可以发现密码策略从PASSWORD EXPIRE DEFAULT变为PASSWORD EXPIRE，我们重新登陆mysql，发现使用sql语句时无法使用，提示在执行该语句之前，必须使用ALTER USER语句重置密码

mysql> select current_user();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

使用root用户修改'test'@'localhost'用户密码

mysql> alter user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

密码修改完成后，可以正常登陆mysql

[root@mysql2 ~]# mysql -utest -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

OK，测试完成

2、密码从不过期策略 PASSWOD EXPIRE NEVER

mysql> alter user 'test'@'localhost' identified by '123456' password expire;
Query OK, 0 rows affected (0.00 sec)

当前 'test'@'localhost' 用户永不过期。
3、指定过期时间策略 PASSWORD EXPIRE INTERVAL 90 DAY

mysql> alter user 'test'@'localhost' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)

当前 'test'@'localhost' 用户90天以后过期，过期后需要我们修改 'test'@'localhost' 密码。

4、默认过期时间策略 PASSWORD EXPIRE DEFAULT

创建用户时，mysql默认的是该策略PASSWORD EXPIRE DEFAULT ，由default_password_lifetime参数决定密码的有效期。
可以使用 select @@default_password_lifetime或者show variables like '%default_password_lifetime%'进行查询

mysql> select @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

我们修改 'test'@'localhost'为默认时间过期

mysql> alter user 'test'@'localhost' password expire default;
Query OK, 0 rows affected (0.01 sec)

如果需要修改全局参数，我们可以在my.cnf文件中指定：

[mysqld]
default_password_lifetime=180

default_password_lifetime=180 密码有效期为6个月，最大值65535
default_password_lifetime=0 默认是0，密码永不过期

也可以通过命令来实时修改，可以随时生效而且永久生效（mysql 8.0版本支持，推荐使用）：

mysql> set default_password_lifetime=180;
Query OK, 0 rows affected (0.00 sec)

我们现在要求每90天更改一次密码，我们可以create user和alter user...password expire interval 90 day

mysql> create user 'test1'@'localhost' identified by '123456' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)

我们需要注意的是，当我们指定了用户的是密码立即失效、永久生效和指定过期间隔策略都会覆盖该语句命名的用户的全局策略。当指定用户默认过期时间，用户将会遵循全局密码策略。

当密码过期，我们可以通过修改密码，处理密码过期；下面进行测试

我们创建一个用户，默认策略是全局参数

mysql> create user 'test3'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> show create user 'test3'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for test3@localhost                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'test3'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改当前用户立即失效

mysql> alter user 'test3'@'localhost' password expire;
Query OK, 0 rows affected (0.00 sec)

打开新的窗口登陆mysql,查询数据

[root@mysql2 ~]# mysql -utest3 -p123456
## 忽略登陆部分 ##
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 

系统提示修改密码

mysql> alter user 'test3'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

重新登陆mysql

[root@mysql2 ~]# mysql -utest3 -p123456
## 忽略登陆部分 ##
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

经过上面测试，我们得知修改密码，可以处理密码过期

3.2.2 密码复杂度策略

MySQL5.6.6版本之后增加了密码强度验证插件validate_password，相关参数设置的较为严格。使用了该插件会检查设置的密码是否符合当前设置的强度规则，若不满足则拒绝设置。默认没有安装这个插件，我们可以通过 select * from mysql.plugin 或者使用show variables like '%validate_password%'查看列表为空

mysql> select * from mysql.plugin;
Empty set (0.00 sec)

mysql> show variables like '%validate_password%';
Empty set (0.00 sec)

我们可以使用命令install plugin validate_password soname 'validate_password.so'进行安装

mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'vali%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

而关闭密码复杂度使用改命令uninstall plugin validate_password

mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'vali%';
Empty set (0.00 sec)

也可以在my.cnf配置文件中指定plugin-load=validate_password.so开启密码复杂度

[mysqld]
plugin-load=validate_password.so

关闭密码复杂度在my.cnf配置文件中指定validate-password=off重启服务即可。

[mysqld]
plugin-load=validate_password.so
validate-password=off

建议使用install plugin validate_password soname 'validate_password.so'和uninstall plugin validate_password进行密码复杂度的开启和关闭

我们通过查询，知道密码复杂度有

• validate_password_check_user_name 用户名检测，检测是否重名,默认关闭

• validate_password_dictionary_file 字典文件，字典文件中存在的密码不得使用

• validate_password_length 密码的最小长度，此处为8

• validate_password_mixed_case_count 至少要包含小写或大写字母的个数，此处为1

• validate_password_number_count 至少要包含的数字的个数，此处为1

• validate_password_policy 密码安全策略 0~2,默认MEDIUM（2）

  LOW OR 1: 只限制长度
  MEDIUM OR 2： 限制长度、数字、字母、特殊字符
  STRONG OR 3： 限制长度、数字、字母、特殊字符、字典

• validate_password_special_char_count 至少要包含的字符的个数，此处为1

我们使用set global 进行全局变量设置密码复杂度策略调为STRONG(3)，从8开始，可通过SET PERSIST命令将全局变量的修改持久化到配置文件中。我们开始测试

mysql> set global validate_password_policy=2;
Query OK, 0 rows affected (0.00 sec)

首先我们创建密码字典文件

[root@mysql2 ~]# echo 'admin'> /tmp/dic.file
[root@mysql2 ~]# cat /tmp/dic.file 
admin

使用set global指定字典文件

mysql> set global validate_password_dictionary_file ='/tmp/dic.file';
Query OK, 0 rows affected (0.00 sec)

我们查询密码复杂度策略

mysql> SHOW VARIABLES LIKE 'vali%';
+--------------------------------------+---------------+
| Variable_name                        | Value         |
+--------------------------------------+---------------+
| validate_password_check_user_name    | OFF           |
| validate_password_dictionary_file    | /tmp/dic.file |
| validate_password_length             | 8             |
| validate_password_mixed_case_count   | 1             |
| validate_password_number_count       | 1             |
| validate_password_policy             | STRONG        |
| validate_password_special_char_count | 1             |
+--------------------------------------+---------------+

我们进行密码的最小长度，大小写字母，符号，字典等方面进行测试，如下

--密码长度6位
mysql> alter user 'test3'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
--密码长度8位
mysql> alter user 'test3'@'localhost' identified by '12345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
--密码长度10位，包含大小写字母各1位
mysql> alter user 'test3'@'localhost' identified by 'A12345678a';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
--密码长度11位，包含大小写字母符号各1位，成功
mysql> alter user 'test3'@'localhost' identified by 'A12345678a#';
Query OK, 0 rows affected (0.00 sec)
--密码字母包含在字典里面，失败
mysql> alter user 'test3'@'localhost' identified by 'Admin123456#';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

OK，测试成功，我们关闭密码复杂度策略

mysql> uninstall plugin validate_password ;
Query OK, 0 rows affected (0.00 sec)

3.2.3 密码必要验证策略（MySQL 8.0 新特性）

在MySQL 8.0 之前的版本，普通用户可以直接更改自己密码，不需要旧密码验证，也不需要知会管理员，比如用户test 需要更改密码，在MySQL 5.7 下直接敲alter user 命令即可,假设test在服务器上登陆数据库过后，忘记退出，test2用户刚好到服务器上修改密码。为了防止这类不安全事件的生，MySQL 8.0 发布了一系列密码验证策略。

密码必要验证策略password_require_current 也就是修改密码前必须验证密码

我们可以在my.cnf文件中指定：

[mysqld]
password_require_current =on

也可以通过命令来实时修改，可以随时生效而且永久生效（推荐使用）：

mysql> set persist password_require_current=on;
Query OK, 0 rows affected (0.00 sec)

我们通过show variables like 'password%'命令来查询参数是否生效：

mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_require_current | ON    |
+--------------------------+-------+
1 rows in set (0.01 sec)

可以在通过create user 和 alter user...password require current optional或者 password require current default 来覆盖全局参数的值，但是注意的是password require current default参数取决于require current optional参数的值

mysql> create user 'test'@'localhost' identified by '123456' password expire never password require current;
Query OK, 0 rows affected (0.01 sec)

登陆刚才创建的'test'@'localhost'用户登录，并修尝试的是该改密码

[root@mysql2 ~]# mysql -utest -p123456 -S /data/mysql/my3307/mysql.sock
## 省略部分登陆代码 ##
Server version: 8.0.32 MySQL Community Server - GPL
mysql> alter user user() identified by '654321';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

当前密码需要在REPLACE子句中指定，以便更改它。接下来，alter user 跟上子句replace 来让'test'@'localhost'用户输入旧密码，成功更改新密码

mysql> alter user user() identified by '123456' replace '123456';
Query OK, 0 rows affected (0.01 sec)

我们使用alter user...password require current optional来关闭'test1'@'localhost'用户的密码必要验证策略

mysql>alter 'test1'@'localhost' password require current optional;
Query OK, 0 rows affected (0.01 sec)

接下来 'test1'@'localhost'用户又恢复为MySQL旧版本的安全行为

mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.01 sec)

OK，测试完成

3.2.4 密码的重复使用策略（MySQL 8.0 新特性）

MySQL 8.0允许对重用以前的密码进行限制。 可以根据密码更改次数，已用时间或两者来确定重用限制。 可以在全局建立重用策略，并且可以将个人帐户设置为遵循全局策略或使用指定的某个帐户行为来覆盖全局策略。

帐户的密码历史记录包含着过去分配的密码。 MySQL可以限制从此历史记录中选择新密码，我们可以查询mysql.password_history，记录的密码更改的历史

如果根据密码更改次数限制帐户，则无法从指定数量的最新密码中选择新密码。 例如，如果密码更改的最小数量设置为3，则新密码不能与最近最新的3个密码相同。

如果根据已用时间限制帐户，则无法从历史记录中比指定天数更新的密码中选择新密码。 例如，如果密码重用间隔设置为60，则新密码不能和过去60天内的密码相同。

如果根据已用时间限制帐户和密码更改次数限制，则两个参数都需要满足才可以更改密码

注：空密码不会算在密码历史记录中。想要设置密码重用策略，可以通过设置password_history 和password_reuse_interval系统变量来实现： 默认都是0，即不启用重用密码策略。

最近使用的密码保留次数 password_history
最近使用的密码保留天数 password_reuse_interval

我们可以在my.cnf文件中指定：

[mysqld]
password_history=3
password_reuse_interval=30

也可以通过命令来实时修改，可以随时生效而且永久生效（推荐使用）：

mysql> set persist password_history=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set persist password_reuse_interval=30;
Query OK, 0 rows affected (0.00 sec)

我们通过show variables like 'password%'命令来查询参数是否生效：

mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_history         | 3     |
| password_require_current | ON    |
| password_reuse_interval  | 30    |
+--------------------------+-------+
3 rows in set (0.01 sec)

可以在通过create user 和 alter user...password history 3 password reuse interval 30 来覆盖全局参数的值

mysql> create user 'test4'@'localhost' identified  by '123456' password history 3 password reuse interval 30 day;
Query OK, 0 rows affected (0.00 sec)

我们可以使用命令show create user命令查询'test4'@'localhost'用户的非权限属性，可以发现PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 30 DAY，用户策略覆盖了全局策略

mysql> show create user 'test4'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test4@localhost: CREATE USER `test4`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$IPDZ.elS9pULZ|sjDgdLpKcyNUdLk2LZ7VfI9T6y56gc79nD2zOTB9xF5' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 30 DAY PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

还原到全局配置：

mysql> alter user 'test4'@'localhost' password history default password reuse interval default;
Query OK, 0 rows affected (0.00 sec)

password history default 和password reuse interval default是由password_history 和password_reuse_interval的全局参数决定。

3.2.5 双密码策略（MySQL 8.0 新特性）

MySQL 8.0.14版本中，用户支持双密码（主密码和副密码），双密码策略就是在日常运维中，需要定期更改指定用户密码，同时又需要旧密码暂时保留一定时长的一种策略。其作用是延迟应用与数据库之间的用户新旧密码对接时间。

双密码机制其实就是主密码与备密码，当备密码不再使用时，告知管理员丢弃备密码，此时用户的主密码即是唯一密码，用法如下：

管理员先创建一个新用户 'test1'@'localhost' ，密码是 test1old ，完了更改他的密码为 test1new，并且retain current password。此时 rootnew 即为主密码，而 rootold 即为备密码

mysql> create user 'test1'@'localhost' identified by 'test1old';
Query OK, 0 rows affected (0.01 sec)

mysql> alter user  'test1'@'localhost' identified by 'test1new' retain current password;
Query OK, 0 rows affected (0.01 sec)

接下来用户'test1'@'localhost' 分别使用备密码与主密码连接 MySQL 并且执行一条简单的 SQL 语句：

[root@mysql2 ~]# mysql -utest1 -ptest1new -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+

[root@mysql2 ~]# mysql -utest1 -ptest1old -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+

可以发现在两个密码都能正常使用，相关业务更改完成后，即可告知管理员丢弃备密码discard old password：

mysql> alter user  'test1'@'localhost' discard old password;
Query OK, 0 rows affected (0.01 sec)

双密码策略有以下需要注意的事项：

1、如果用户本身已经有双密码策略，再次更改新密码时没有带 retain current password 子句，那之前的主密码被替换成新改的密码，但是备密码不会被替换。还有一点需要注意的细节，如果不带 retain current password 子句，并且更改新密码为空串，那么主备密码则会统一更改为空串。下面直接8个空格

mysql> alter user 'test1'@'localhost' identified by '        ';
Query OK, 0 rows affected (0.02 sec)

[root@mysql2 ~]# mysql -utest1 -ptest1old -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: YES)

2、使用双密码策略时，不能更改用户的认证插件。

mysql> alter user 'test1'@'localhost' identified with sha256_password by 'testtest' retain current password;
ERROR 3894 (HY000): Current password can not be retained for user 'test1'@'localhost' because authentication plugin is being changed.

3、新密码为空，不允许使用备用密码

mysql> alter user  'test1'@'localhost' identified by '' retain current password;
ERROR 3895 (HY000): Current password can not be retained for user 'test1'@'localhost' because new password is empty.

3.2.5 随机密码策略（MySQL 8.0 新特性）

以往旧版本有生成随机密码的需求，在 MySQL 端无法直接设定，MySQL 8.0 直接可以在create user

mysql> create user 'test2'@'localhost' identified by random password;
+-------+-----------+----------------------+-------------+
| user  | host      | generated password   | auth_factor |
+-------+-----------+----------------------+-------------+
| test2 | localhost | 7HcEGYi6:K.*sURH-n.C |           1 |
+-------+-----------+----------------------+-------------+
1 row in set (0.00 sec)

也可以用 set password 子句来设置随机密码

mysql> set password for 'test2'@'localhost' to random;
+-------+-----------+----------------------+-------------+
| user  | host      | generated password   | auth_factor |
+-------+-----------+----------------------+-------------+
| test2 | localhost | tJKznYxoL1u+J3YS<U-T |           1 |
+-------+-----------+----------------------+-------------+
1 row in set (0.00 sec)

也可以用 alter user来设置随机密码

mysql> alter user 'test2'@'localhost' identified by random password;
+-------+-----------+----------------------+-------------+
| user  | host      | generated password   | auth_factor |
+-------+-----------+----------------------+-------------+
| test2 | localhost | vZ-q{OqQqDnaT]gbhCy+ |           1 |
+-------+-----------+----------------------+-------------+
1 row in set (0.00 sec)

3.2.6 失败登录追踪和临时密码锁定（MySQL 8.0 新特性）

失败登录追踪和临时密码锁定，后面我们简称为：FLTTAL 。
和之前几个密码策略不同，FLTTAL 没有全局参数匹配，只能在创建用户或者是更改用户属性时被匹配。 有两个选项
failed_login_attempts 密码错误次数和 password_lock_time 锁定天数

需要注意的是：
1、failed_login_attempts 和 password_lock_time 必须同时不为 0 ，FLTTAL 才能生效。

mysql> create user 'test3'@'localhost' identified by 'test' failed_login_attempts 2 password_lock_time 0;
Query OK, 0 rows affected (0.01 sec)

[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+

2、创建新用户不指定 failed_login_attempts 和 password_lock_time ，则默认关闭 FLTTAL 。
3、已使用FLTTAL的用户，管理员对其 alter user 后可改变原有密码验证策略。

mysql> alter user 'test3'@'localhost' identified by 'test' failed_login_attempts 4 password_lock_time 1;
Query OK, 0 rows affected (0.00 sec)

[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test3'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.

4、一旦账户被锁定，即使输入正确密码也无法登录。

[root@mysql2 ~]#  mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test3'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.

管理员解锁账户方能正常使用：（或者忘记密码，让管理员解锁账号并且重置新密码）

mysql> alter user 'test3'@'localhost' account unlock;
Query OK, 0 rows affected (0.01 sec)

[root@mysql2 ~]#  mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+

还有最重要的一点：由于 FLTTAL 对密码验证正确与否的连续性，任意一次成功登录，FLTTAL 计数器重置

[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]#  mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test3'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.
[root@mysql2 ~]# 

3.4 密码插件

3.4.1 mysql 5.7 版本默认使用的密码插件

mysql_native_password

mysql> show variables like '%authentication%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password | 
+-------------------------------+-----------------------+
1 row in set (0.78 sec)

3.4.2 mysql 8.0 版本默认使用的密码插件

caching_sha2_password

mysql> select @@default_authentication_plugin ;
+---------------------------------+
| @@default_authentication_plugin |
+---------------------------------+
| caching_sha2_password           |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

3.4.3 mysql 8 要兼容之前版本

方法一：alter 修改用户指定密码插件

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

方法二：我们可以在my.cnf文件中指定：

[mysqld]
default_authentication_plugin=mysql_native_password

也可以通过命令来实时修改，可以随时生效而且永久生效（MySQL 8.0 推荐使用）：

mysql> set persist default_authentication_plugin=mysql_native_password;
Query OK, 0 rows affected (0.00 sec)

3.4.4 MySQL 8.0 多因素身份认证（了解）

MySQL 8.0.27 增加了多因素身份认证功能，可以为一个用户指定多重的身份校验。为此还引入了新的系统变量 authentication_policy ，用于管理多因素身份认证功能。在 MySQL 8.0.27 之前，create user 的时候可以指定一种认证插件，在未明确指定的情况下会取系统变量 default_authentication_plugin的值。default_authentication_plugin 的有效值有3个，分别是 mysql_native_password ，sha256_password ，caching_sha2_password 。

在 MySQL 8.0.27 中由 authentication_policy 来管理用户的身份认证，同时查看下 authentication_policy 和 default_authentication_plugin 的值

mysql> show variables like '%authentication_%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| authentication_policy         | *,,                   |
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
2 rows in set (0.06 sec)';

们看到 authentication_policy 的默认值是*,,

第1个元素值是*，表示可以是任意插件，默认值取 default_authentication_plugin 的值。如果该元素值不是星号，则必须设置为 mysql_native_password ，sha256_password ，caching_sha2_password 中的一个。

第2，3个元素值为空，这两个位置不能设置成内部存储的插件。如果元素值为空，代表插件是可选的。

我们创建一个用户，不指定插件名称时，自动使用默认插件caching_sha2_password

mysql> create user 'test'@'localhost' identified by'123456';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,plugin from mysql.user where user='test';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| test | localhost | caching_sha2_password |
+------+-----------+-----------------------+
1 row in set (0.00 sec)

指定插件名称时，会使用到对应的插件

mysql> create user 'test4'@'localhost' identified  with mysql_native_password by'123456';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,plugin from mysql.user where user='test4';
+-------+-----------+-----------------------+
| user  | host      | plugin                |
+-------+-----------+-----------------------+
| test4 | localhost | mysql_native_password |
+-------+-----------+-----------------------+
1 row in set (0.00 sec)

尝试变更一下 authentication_policy 第一个元素的值，设置为 sha256_password

mysql> set persist authentication_policy='sha256_password,,';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| authentication_policy | *,,   |
+-----------------------+-------+
1 row in set (0.01 sec)

再次创建一个用户，不指定插件的名称

mysql> create user 'test5'@'localhost' identified by'123456';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,plugin from mysql.user where user='test5';
+-------+-----------+-----------------+
| user  | host      | plugin          |
+-------+-----------+-----------------+
| test5 | localhost | sha256_password |
+-------+-----------+-----------------+
1 row in set (0.00 sec)

可以看到默认使用的插件是 sha256_password ，说明当 authentication_policy 第一个元素指定插件名称时，default_authentication_plugin 被弃用了

我们现在进行多重身份验证的用户，首先我们恢复 authentication_policy 至默认值

mysql> set persist  authentication_policy='*,,';
Query OK, 0 rows affected (0.01 sec)
 
mysql> show variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| authentication_policy | *,,   |
+-----------------------+-------+
1 row in set (0.01 sec)

创建一个双重认证的用户，如下创建失败了，因为我们同时用2种内部存储插件。

mysql> create user 'test7'@'localhost' identified by '123456' and identified with mysql_native_password by '123456';
ERROR 4052 (HY000): Invalid plugin "mysql_native_password" specified as 2 factor during "CREATE USER".

那我们来装一个可插拔插件 Socket Peer-Credential

mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%socket%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| auth_socket | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)

再创建一个双重认证的用户

mysql> create user 'test7'@'localhost' identified by '123456' and identified with auth_socket as 'root';
Query OK, 0 rows affected (0.05 sec)

mysql> select user,host,plugin,User_attributes from mysql.user where user='test7';
+-------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user  | host      | plugin                | User_attributes                                                                                                                              |
+-------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| test7 | localhost | caching_sha2_password | {"multi_factor_authentication": [{"plugin": "auth_socket", "passwordless": 0, "authentication_string": "root", "requires_registration": 0}]} |
+-------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

创建成功，之后用户’test6’@'localhost’必须提供正确的密码，且同时本地主机的登录用户为 root 时，才会验证通过。

[root@mysql2 bin]#mysql -utest7 --password1=123456 --password2  -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password: 
## 省略部分代码 ##
mysql>

Ok，测试成功。也可以修改同时登陆用户，如下我们先输入一个错误的密码，在输入一个正确的密码

mysql> alter user 'test7'@'localhost' modify 2 factor identified with auth_socket as 'test7';

再次以主机 root 用户身份，提供正确的密码 123 ，登录失败，因此可以认定双重身份认证机制是生效的。

四、用户权限管理

MySQL数据库对于对象的操作级别分为：全局、数据库、表、字段等。粒度从粗到细。如果粗的粒度的权限满足了，将不再检验细粒度的级别，也就是说全局权限满足了，就不会校验数据库、表和字段，依次类推。

4.1 权限粒度

4.2 显示用户非权限属性

mysql> show create user 'grant'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for grant@localhost: CREATE USER 'grant'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)

ERROR: 
No query specified

4.3 全局级权限

我们创建‘grant’@'localhost'用户进行权限的学习

mysql> create user 'grant'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

使用show grants for查询用户权限，可以看到刚才创建的用户拥有USAGE权限

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
1 row in set (0.00 sec)

ERROR: 
No query specified

授予用户replication client 和 replication slave用户级权限后进行用户权限的查询

mysql> grant replication client , replication slave on *.* to 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
1 row in set (0.00 sec)

ERROR: 
No query specified

查询用户权限，全局级权限对应的mysql.user表

mysql> select * from mysql.user where user='grant' \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: grant
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      password_expired: N
 password_last_changed: 2023-04-20 22:37:57
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

ERROR: 
No query specified

4.4 库级别权限

这里创建一个数据库，模拟测试库级别权限，在这里我们回顾一下字符集的设置创建表的时候没有指定字符集，默认使用数据库，如果数据库也没有设置，就默认使用server的字符集。

mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | utf8                                                           |
| character_set_connection | utf8                                                           |
| character_set_database   | utf8                                                           |
| character_set_filesystem | binary                                                         |
| character_set_results    | utf8                                                           |
| character_set_server     | utf8                                                           |
| character_set_system     | utf8                                                           |
| character_sets_dir       | /opt/mysql/mysql-5.7.39-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)

创建数据库，默认字符集utf8

mysql> create database testdb default character set utf8;
Query OK, 1 row affected (0.00 sec)

授予‘grant’@'localhost'用户testdb 库级的权限

mysql> grant select,insert,update,delete,create,alter,lock tables,show view,trigger on tesdb.* to 'grant'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询用户权限

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
2 rows in set (0.00 sec)

ERROR: 
No query specified

查询库级别权限，库级别权限对应的表是mysql.db表

mysql> select * from mysql.db where user='grant' \G;
*************************** 1. row ***************************
                 Host: localhost
                   Db: tesdb
                 User: grant
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: Y
Create_tmp_table_priv: N
     Lock_tables_priv: Y
     Create_view_priv: N
       Show_view_priv: Y
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: Y
1 row in set (0.00 sec)

ERROR: 
No query specified

4.5 表级权限

我们创建一张表，进行权限的学习

mysql> use testdb;
Database changed
mysql> create table test1 (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

授予‘grant’@'localhost'用户testdb.test1 表级别的权限

mysql> grant  select,insert,update,delete,create,alter  on testdb.test1 to 'grant'@'localhost';

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询用户权限

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
*************************** 3. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `testdb`.`test1` TO 'grant'@'localhost'
3 rows in set (0.00 sec)

ERROR: 
No query specified

查询表级别权限，表级别对应的mysql.tables_priv表

mysql> select *  from mysql.tables_priv where table_name='test1'\G;
*************************** 1. row ***************************
       Host: localhost
         Db: testdb
       User: grant
 Table_name: test1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert,Update,Delete,Create,Alter
Column_priv: 
1 row in set (0.00 sec)

ERROR: 
No query specified

4.6 列级权限

我们授予授予‘grant’@'localhost'用户testdb.test1 表id字段查询权限，name字段更新权限

mysql> grant select(id),update(name) on testdb.test1 to 'grant'@'localhost';
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询用户权限

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
*************************** 3. row ***************************
Grants for grant@localhost: GRANT SELECT, SELECT (id), INSERT, UPDATE, UPDATE (name), DELETE, CREATE, ALTER ON `testdb`.`test1` TO 'grant'@'localhost'
3 rows in set (0.00 sec)

ERROR: 
No query specified

查询列级别权限

列级别对应的mysql.tables_priv表或者columns_priv表,但是我们查询mysql.tables_priv表发现列级别权限有更查询和更新权限，但不知道是哪一列

mysql> select * from mysql.tables_priv where table_name = 'test1' \G;
*************************** 1. row ***************************
       Host: localhost
         Db: testdb
       User: grant
 Table_name: test1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert,Update,Delete,Create,Alter
Column_priv: Select,Update
1 row in set (0.00 sec)

ERROR: 
No query specified

查询mysql.columns_priv表可以明确看出哪一列有什么权限

mysql> select * from mysql.columns_priv where table_name = 'test1' \G;
*************************** 1. row ***************************
       Host: localhost
         Db: testdb
       User: grant
 Table_name: test1
Column_name: id
  Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 2. row ***************************
       Host: localhost
         Db: testdb
       User: grant
 Table_name: test1
Column_name: name
  Timestamp: 0000-00-00 00:00:00
Column_priv: Update
2 rows in set (0.00 sec)

ERROR: 
No query specified

4.7 权限回收

查询用户权限

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
*************************** 3. row ***************************
Grants for grant@localhost: GRANT SELECT, SELECT (id), INSERT, UPDATE, UPDATE (name), DELETE, CREATE, ALTER ON `testdb`.`test1` TO 'grant'@'localhost'
3 rows in set (0.00 sec)

ERROR: 
No query specified

4.7.1 回收部分权限

使用revoke 进行权限的回收

mysql> revoke select(id), update(name) ON  testdb.test1 from 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

在这里，我们需要知道MySQL权限机制中，在一个数据库上多次赋予权限，权限会自动合并；

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
2 rows in set (0.00 sec)

ERROR: 
No query specified

4.7.2 回收部分权限（MySQL 8.0 新特性）

MySQL 数据库对于对象的操作级别分为：全局、数据库、表、字段等。粒度从粗到细。如果粗的粒度的权限满足了，将不再检验细粒度的级别，这种验证方式有的时候不方便，例如需要把 100 个数据库中除了某一个数据库外的访问权限赋予某个用户，需要进行 99 次赋权。从 MySQL 8.0.16 开始，MySQL 推出了一种部分权限回收（Partial Revokes）的功能，可以将粗粒度赋予的权限在细粒度上回收。

可以使用下面的命令将这个参数打开

mysql> SET PERSIST partial_revokes = ON;
Query OK, 0 rows affected (0.00 sec)

我们创建一个'test'@'localhost'用户

mysql> create user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

下面的命令赋予用户 'test'@'localhost'对除了 mysql 之外的所有数据库和下面的表的 select 权限：

mysql> grant select on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> revoke select on mysql.* from 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)

赋权完成后可以使用 show grants 命令进行检查：

mysql> show grants for 'test'@'localhost';
+----------------------------------------------------+
| Grants for test@localhost                          |
+----------------------------------------------------+
| GRANT SELECT ON *.* TO `test`@`localhost`          |
| REVOKE SELECT ON `mysql`.* FROM `test`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.00 sec)

权完成后在 mysql.user 表里面的 User_attributes 会有 Restrictions 的属性：

mysql> select  User_attributes from mysql.user where user='test';
+---------------------------------------------------------------------+
| User_attributes                                                     |
+---------------------------------------------------------------------+
| {"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]} |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

回收部分权限回收功能可以再次赋予部分权限，例如：

mysql> grant select on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'test'@'localhost';
+----------------------------------------------------+
| Grants for test@localhost                          |
+----------------------------------------------------+
| GRANT SELECT ON *.* TO `test`@`localhost`          |
+----------------------------------------------------+
1 rows in set (0.00 sec)

也可以从粗粒度上回收权限，这样细粒度的回收当然没有必要存在了

mysql> revoke all privileges on *.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)

4.7.3 回收全部权限

mysql> revoke all privileges on *.* from 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询用户权限

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
2 rows in set (0.00 sec)

ERROR: 
No query specified

是不是很意外，按照我们的思路，'grant'@'localhost'用户应该没有权限，但是在这里revoke说它干不了这活，加钱也干不了。。。查阅了很多资料：MySQL权限机制中，在一个数据库上多次赋予权限，权限会自动合并；但在多个库上多次赋予权限，每个库上都会认为是单独的一组权限。若要收回，必须单独对相应的库使用revoke命令。看来 revoke也有划水摸鱼的时候。但是在8.0 版本中，好像被优化了。我们只能再次回收库级别权限

mysql> revoke select, insert, update, delete, create, alter, lock tables, show view, trigger on tesdb.* from 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
1 row in set (0.00 sec)

ERROR: 
No query specified

4.8 MySQL权限（MySQL 8.0 新特性）

权限列表大体分为服务级别和表级别，列级别以及大而广的角色（也是MySQL 8.0 新增）存储程序等权限。我们看到有一个特殊的 SUPER 权限，可以做好多个操作。比如 SET 变量，在从机重新指定相关主机信息以及清理二进制日志等。那这里可以看到，SUPER 有点太过强大，导致了仅仅想实现子权限变得十分困难，比如用户只能 SET 变量，其他的都不想要。那么 MySQL 8.0 之前没法实现，权限的细分不够明确，容易让非法用户钻空子。那么 MySQL 8.0 把权限细分为静态权限和动态权限

4.8.1 mysql 8.0 静态权限汇总

MySQL服务器内置了静态特权，下表描述了MySQL中可用的每个静态特权在这里我把权限划分为Server、Table、Column、Rose&User、Stored routine等

管理权限也就是全局权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟 *.*
其实truncate权限就是create+drop，这点需要注意

4.8.2 动态权限汇总

与在服务器中内置的静态特权相反，动态特权是在运行时定义的。下表描述了MySQL中可用的每个动态特权。大多数动态特权是在服务器启动时定义的。其他特权由特定的服务器组件或插件定义，如特权描述中所述。在这种情况下，除非启用了定义特权的组件或插件，否则特权不可用。

这些权限只能后面慢慢测试了

4.8.2 权限授权和回收技巧

授予权限
grant 权限1…权限n on dbname.tablename to user;
回收权限
revoke 权限1…权限n on dbname.tablename from user;

也就是grant对应的revoke,to对应的from

五、角色管理（MySQL 8.0 新特性）

角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以拥有授予和撤消的权限。引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

5.1 创建角色并授予权限

我们使用create role创建角色

mysql> create role 'dba_role','write_role','read_role';
Query OK, 0 rows affected (0.02 sec)

授予权限

mysql> grant all on *.* to 'dba_role';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select  on *.* to 'read_role';
Query OK, 0 rows affected (0.01 sec)

mysql> grant update,delete,insert  on *.* to 'write_role';
Query OK, 0 rows affected (0.01 sec)

将角色授予对应的用户

mysql> create user dba1 identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant 'dba_role' to 'dba1'@'%';
Query OK, 0 rows affected (0.01 sec)

在GRANT授权角色的语法和授权用户的语法不同：有一个ON来区分角色和用户的授权，有ON的为用户授权，而没有ON用来分配角色。由于语法不同，因此不能在同一语句中混合分配用户权限和角色。（允许为用户分配权限和角色，但必须使用单独的GRANT语句，每种语句的语法都要与授权的内容相匹配）

5.2 检查角色权限

要验证分配给用户的权限，使用 SHOW GRANTS，例如：

mysql> SHOW GRANTS FOR 'dba1'@'%';
+------------------------------------+
| Grants for dba1@%                  |
+------------------------------------+
| GRANT USAGE ON *.* TO `dba1`@`%`   |
| GRANT `dba_role`@`%` TO `dba1`@`%` |
+------------------------------------+
2 rows in set (0.00 sec)

但是，它会显示每个授予的角色，而不会将其显示为角色所代表的权限。如果要显示角色权限，添加一个 USING来显示：

mysql> SHOW GRANTS FOR 'dba1'@'%'  USING 'dba_role' \G;
*************************** 1. row ***************************
Grants for dba1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dba1`@`%`
*************************** 2. row ***************************
Grants for dba1@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dba1`@`%`
*************************** 3. row ***************************
Grants for dba1@%: GRANT `dba_role`@`%` TO `dba1`@`%`
3 rows in set (0.00 sec)

5.3 撤消角色或角色权限

revoke 可以用于角色修改角色权限。这不仅影响角色本身权限，还影响任何授予该角色的用户权限。
假设让所有用户只读，使用revoke 从该dba_ro le角色中删除修改权限 ：

mysql> revoke inster, update, delete on *.* from 'dba_role';
Query OK, 0 rows affected, 1 warning (0.02 sec)

正如可以授权某个用户的角色一样，可以从帐户中撤销这些角色：

mysql> revoke dba_role from dba1@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'dba1'@'%';
+----------------------------------+
| Grants for dba1@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `dba1`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

5.4 删除角色

要删除角色，请使用drop role：

mysql> drop role dba_role;
Query OK, 0 rows affected (0.01 sec)

删除角色会从授权它的每个帐户中撤消该角色

六、资源限制

资源限制可以从以下4个方面限制

• 帐户每小时可发出的查询数量
• 帐户每小时可以发布的更新次数
• 帐户每小时可以连接到服务器的次数
• 帐户同时连接到服务器的数量

6.1 用户创建指定配额

mysql> create user 'test4'@'localhost' identified by '123456' WITH MAX_QUERIES_PER_HOUR 2 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.03 sec)

6.2 修改配额

mysql> alter user 'test4'@'localhost' with MAX_QUERIES_PER_HOUR 100;
Query OK, 0 rows affected (0.01 sec)

至此今天的学习就到此结束了，愿您成为坚不可摧的自己~~~
​

You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life

如果我的内容对你有帮助，请 点赞、评论、收藏，创作不易，大家的支持就是我坚持下去的动力

文章来源地址https://www.toymoban.com/news/detail-440339.html

到了这里，关于是不是史上最全的MySQL用户和权限原理实战你说了算的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！