也可以用 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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [Warning] Using a password on the command line interface can be insecure.+-------------+| hello world |+-------------+| hello world |+-------------+

创建新用户不指定 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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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 ~]#  mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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.sockmysql: [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 UNLOCK1 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: NCreate_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: N1 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: YCreate_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: Y1 row in set (0.00 sec)ERROR: No query specified

4.5 表级权限

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

mysql> use testdb;Database changedmysql> 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,AlterColumn_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,AlterColumn_priv: Select,Update1 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: test1Column_name: id  Timestamp: 0000-00-00 00:00:00Column_priv: Select*************************** 2. row ***************************       Host: localhost         Db: testdb       User: grant Table_name: test1Column_name: name  Timestamp: 0000-00-00 00:00:00Column_priv: Update2 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://blog.csdn.net/liang921119/article/details/130171119