MySQL8 创建用户,设置修改密码,授权

MySQL8 创建用户,设置修改密码,授权

MySQL

5.7

可以 (创建用户,设置密码,授权) 一步到位 👇

GRANTALLPRIVILEGESON*.*TO'用户名'@'%' IDENTIFIED BY'密码'WITHGRANTOPTION

👆这样的语句在MySQL

8.0

中行不通, 必须 创设和授权 分步执行👇

CREATEUSER u@'%' IDENTIFIED BY'密';-- 创建用户并指定密码GRANTALLPRIVILEGESON*.*TO u@'%'WITHGRANTOPTION;--授权

也可以分三步

CREATEUSER u@'%';-- 创建用户ALTERUSER u@'%' IDENTIFIED BY'密';-- 指定密码GRANTALLPRIVILEGESON*.*TO u@'%'WITHGRANTOPTION;-- 授权

刷新权限设置

FLUSH PRIVILEGES;

创建远程root的语句模板

创建 root@‘%’ , 并将 root@‘localhost’ 的权限授予 root@‘%’

CREATEUSERIFNOTEXISTS'root'@'%' IDENTIFIED BY'密码';GRANTALLON*.*TO'root'@'%'WITHGRANTOPTION; FLUSH PRIVILEGES;

创建 root@‘%’ , 并让 root@'%'扮演root@‘localhost’ 的角色 , 并设置为默认角色

CREATEUSERIFNOTEXISTS'root'@'%' IDENTIFIED BY'密码';GRANT root@'localhost'TO'root'@'%';SETDEFAULT ROLE root@'localhost'TO'root'@'%'; FLUSH PRIVILEGES;

权限和角色两者都加持

CREATEUSERIFNOTEXISTS'root'@'%' IDENTIFIED BY'密码';GRANTALLON*.*TO'root'@'%'WITHGRANTOPTION;GRANT root@'localhost'TO'root'@'%';SETDEFAULT ROLE root@'localhost'TO'root'@'%'; FLUSH PRIVILEGES;

官方文档-用户管理语句–13.7.1 Account Management Statements

文章目录

官方文档-用户管理语句–13.7.1 Account Management Statements

MySQL账户由用户名@主机组成

MySQL账户

=

用户名@主机

localhost

表示本地主机 , 百分号

%

表示任意主机
百分号

%

是默认值, 可以省略, 例如:

u

=

u@'%'

, CREATE USER

u

得到的用户是

u@'%'

百分号

%

要加单引号或者双引号, 没有空格和其他特殊字符的用户名可以省略单引号或者双引号
张三不用加引号,张 三和张-三要加引号
``666

@888

这个账户中,用户名666要加引号,888可以不用加引号

创建用户,设置密码,修改密码

创建用户

CREATE USER

,

create user

MySQL8.0官方文档,–创建用户的语句—13.7.1.3 CREATE USER Statement

# 用户名@主机(域名,IP)CREATEUSER'username'@'host';# localhost表示本机CREATEUSER'username'@'localhost';# 百分号 % 表示任意主机CREATEUSER'username'@'%';# 百分号 % 可以省略 , 如果用户名没有空格,还可以省略引号CREATEUSER username;

也可以使用

if not exits

CREATEUSERIFNOTEXISTS`root`@`%`;

mysql>createuserifnotexists 张三;
Query OK,0rows affected,1 warning (0.01 sec)

mysql>createuserifnotexists'张 三';
Query OK,0rows affected (0.01 sec)

mysql>createuserifnotexists'张-三';
Query OK,0rows affected (0.01 sec)

mysql>selectuser,host,plugin from mysql.user;+------------------+-----------+-----------------------+|user| host      | plugin                |+------------------+-----------+-----------------------+| root             |%| caching_sha2_password || 张 三            |%| caching_sha2_password || 张-三            |%| caching_sha2_password || 张三             |%| caching_sha2_password || debian-sys-maint | localhost | caching_sha2_password || mysql.infoschema | localhost | caching_sha2_password || mysql.session| localhost | caching_sha2_password || mysql.sys        | localhost | caching_sha2_password || root             | localhost | auth_socket           |+------------------+-----------+-----------------------+

查看已有账户

SELECTuser,host FROM mysql.user

SELECTuser,host,plugin FROM mysql.user

创建用户并指定密码

设置密码的方法有

• 创建用户时指定密码 CREATE USER 账户 IDENTIFIED BY '密码'
• 创建用户后设置修改密码 ALTER USER 账户 IDENTIFIED BY '密码' SET PASSWORD FOR 账户='密码' SET PASSWORD ='自己的密码'

官方推荐在修改密码时用

ALTER USER

,而不是

SET PASSWORD FOR

使用默认加密方式,创建用户并指定密码 和 修改密码

### 创建用户并指定密码CREATEUSER'username'@'%' IDENTIFIED BY'password';### 修改密码ALTERUSER'username'@'%' IDENTIFIED BY'password';### 修改密码方法2SET PASSWORD FOR'username'@'%'='password';

对比助记:

对比助记:
创建用户并指定密码 :  CREATE  USER  'username'@'host'  IDENTIFIED BY 'password';
        修改密码  :  ALTER  USER  'username'@'host'  IDENTIFIED BY 'password';
        删除用户 :    DROP  USER  'username'@'host' 
        

使用指定密码插件, 创建用户并指定密码 和 修改密码

查看 用户,主机,密码插件

SELECTuser, host , plugin   FROM   mysql.user;

show plugins

可以列出mysql的插件

SHOW plugins;

用于密码加密的插件有3个, (MySQL8.0.30)

MySQL8.0内置的3个密码插件👇

👆参见 MySQL8.0官方参考手册–密码管理—6.2.15 Password Management

• mysql_native_password
• caching_sha2_password
• sha256_password

官方不推荐用 sha256_password , 已过时,有替代,未来版本可能删除
caching_sha2_password 和 sha256_password 都用SHA256加密, caching_sha2_password 提供了 sha256_password 功能的超集, caching_sha2_password 带缓存,性能更好,
应该用caching_sha2_password 替代 sha256_password

使用

WITH mysql_native_password

密码插件,创建用户并指定密码 和 修改密码

mysql_native_password

是MySQL5.7(MySQL8.0之前)的默认

### 创建用户并指定密码CREATEUSER'username'@'%' IDENTIFIED WITH mysql_native_password BY'password';### 修改密码ALTERUSER'username'@'%' IDENTIFIED WITH mysql_native_password BY'password';

当一些sql客户端不支持支持新的caching_sha2_password认证连接时, 比如Navicat11 , 可以改用旧的mysql_native_password重新给账户设置密码

使用

WITH caching_sha2_password

密码插件,创建用户并指定密码 和 修改密码

caching_sha2_password

是MySQL8.0的默认

### 创建用户并指定密码CREATEUSER'username'@'%' IDENTIFIED WITH caching_sha2_password BY'password';### 修改密码ALTERUSER'username'@'%' IDENTIFIED WITH caching_sha2_password BY'password';

查看当前的默认密码加密认证插件, 查看系统全局变量

default_authentication_plugin

show variables like'%default_authentication_plugin%';

SHOW variables LIKE'default_authentication_plugin';

SELECT@@default_authentication_plugin;

设置默认的密码加密认证插件, 设置系统全局变量

default_authentication_plugin

• 通过配置文件设置, 重启后仍然有效 在配置文件的 [mysqld] 下方添加 default_authentication_plugin=[mysql_native_password 或者 caching_sha2_password ]
• 通过命令行SET GLOBAL default_authentication_plugin= [mysql_native_password 或者 caching_sha2_password ], 零时设置, 重启MySQL后将会失效
• 通过命令行SET PERSIST default_authentication_plugin= [mysql_native_password 或者 caching_sha2_password ], 持久化设置, 重启MySQL后仍然有效, 并且优先级大于配置文件的设置SET PERSIST 设置的全局变量保存在数据文件夹 datadir下的 mysqld-auto.cnf 文件中, 是json格式, 可以用SHOW VARIABLES LIKE 'datadir';查看MySQL8.0的数据文件夹位置, Ubuntu20.04的MySQL8.0默认是:/var/lib/mysql/mysqld-auto.cnf``````mysqld-auto.cnf 对应表格 performance_schema.persisted_variables , 数据是联动同时变化的 可用 SELECT * FROM performance_schema.persisted_variables; 查看

修改密码

用

ALTER USER

修改自己或别人的密码

ALTERUSER'u'@'%' IDENTIFIED BY'passwd'

# 使用MySQL5.7之前的默认密码插件ALTERUSER'u'@'%' IDENTIFIED WITH mysql_native_password BY'passwd'

# 使用MySQL5.7之前的SHA256密码加密插件, 不推荐, 已过时, 未来的MySQL版本可能删除ALTERUSER'u'@'%' IDENTIFIED WITH sha256_password BY'passwd'

#  使用MySQL8.0默认的带缓存的SHA256加密的密码插件ALTERUSER'u'@'%' IDENTIFIED WITH caching_sha2_password BY'passwd'

用

SET PASSWORD=

设置修改自己的密码

SET PASSWORD='自己的密码';

用

SET PASSWORD FOR

修改自己或其他账户的密码,

=

等号分割账户和密码

SET PASSWORD FOR 账户='密码';

SET PASSWORD FOR'用户名'@'主机IP或域名'='密码';

MySQL8.0官方参考手册–设置密码的语句—13.7.1.10 SET PASSWORD Statement

MySQL8.0官方参考手册–密码管理—6.2.15 Password Management

MySQL8.0官方参考手册–密码和日志–6.1.2.3 Passwords and Logging

MySQL8.0官参–身份认证插件—6.4.1 Authentication Plugins

mysql_native_password—6.4.1.1 Native Pluggable Authentication

caching_sha2_password—6.4.1.2 Caching SHA-2 Pluggable Authentication

sha256_password—6.4.1.3 SHA-256 Pluggable Authentication

密码管理, 密码策略

MySQL8.0官参–密码管理—6.2.15 Password Management

内部与外部凭证存储

一些身份验证插件在mysql.user系统表中将帐户凭据存储在 MySQL 内部：

• mysql_native_password
• caching_sha2_password
• sha256_password

本节中的大多数讨论都适用于此类身份验证插件，因为此处描述的大多数密码管理功能都基于 MySQL 本身处理的内部凭据存储。其他身份验证插件将帐户凭据存储在 MySQL 外部。对于使用针对外部凭据系统执行身份验证的插件的帐户，密码管理也必须在外部针对该系统进行处理。

例外是登录失败跟踪和临时帐户锁定的选项适用于所有帐户，而不仅仅是使用内部凭据存储的帐户，因为 MySQL 能够评估任何帐户的登录尝试状态，无论它使用内部还是外部凭据存储。

有关单个身份验证插件的信息，请参阅 第 6.4.1 节，“身份验证插件”。

密码过期策略

设置某用户的密码立刻过期

ALTERUSER'jeffrey'@'localhost' PASSWORD EXPIRE;

要全局建立自动密码过期策略，请使用

default_password_lifetime 

系统变量。其默认值为

0

，即禁用自动密码过期。如果

default_password_lifetime

的值为 正整数N，则表示允许的密码有效期为N天, (默认单位是天) 因此必须每N天更改密码。

例子：

• 要建立密码有效期约为六个月的全局策略，请在服务器my.cnf文件中使用以下行启动服务器： [mysqld] default_password_lifetime=180
• 要建立密码永不过期的全局策略，请设置 default_password_lifetime 为 0： [mysqld] default_password_lifetime=0 default_password_lifetime
• 也可以在运行时设置和持久化： SET PERSIST default_password_lifetime = 180; #密码180天过期 SET PERSIST default_password_lifetime = 0; #密码不过期

SET PERSIST为正在运行的 MySQL 实例设置一个值。它还保存该值以延续到后续服务器重新启动；请参阅 第 13.7.6.1 节，“变量赋值的 SET 语法” --13.7.6.1 SET Syntax for Variable Assignment。要更改正在运行的 MySQL 实例的值而不使其延续到后续重新启动，请使用GLOBAL 关键字而不是PERSIST.(使用

SET GLOBAL

而不是

SET PERSIST

)

全局密码过期策略适用于所有未设置为覆盖它的帐户。要为个人帐户建立策略，请使用

CREATE USER

和

 ALTER USER

语句的

PASSWORD EXPIRE

密码过期 选项。请参阅 第 13.7.1.3 节，“CREATE USER 语句” 和 第 13.7.1.1 节，“ALTER USER 语句”。

• 要求每 90 天更改一次密码：

CREATEUSER'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL90DAY;ALTERUSER'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL90DAY;

此到期选项会覆盖该语句命名的所有帐户的全局策略。

• 禁用密码过期：

CREATEUSER'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;ALTERUSER'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

此到期选项会覆盖该语句命名的所有帐户的全局策略。

• 遵循语句命名的所有帐户的全局过期策略：

CREATEUSER'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;ALTERUSER'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

当客户端连接成功后，服务器判断账号密码是否过期：

• 服务器检查密码是否已手动过期。
• 否则，服务器根据自动密码过期策略检查密码年龄是否大于其允许的生命周期。如果是这样，则服务器认为密码已过期。

如果密码过期（无论是手动还是自动），服务器要么断开客户端连接，要么限制允许的操作（参见 第 6.2.16 节，“过期密码的服务器处理”）。受限客户端执行的操作会导致错误，直到用户建立新的帐户密码：

mysql>SELECT1;
ERROR 1820(HY000): You must reset your password usingALTERUSER
statement before executing this statement.

mysql>ALTERUSERUSER() IDENTIFIED BY'password';
Query OK,0rows affected (0.01 sec)

mysql>SELECT1;+---+|1|+---+|1|+---+1rowinset(0.00 sec)

客户端重置密码后，服务器恢复会话的正常访问，以及使用该帐户的后续连接。管理用户也可以重置帐户密码，但该帐户的任何现有受限会话仍然受到限制。使用该帐户的客户端必须断开并重新连接才能成功执行语句。

笔记
虽然可以通过将过期密码设置为当前值来“重置”过期密码，但作为一个好的策略，最好选择不同的密码。DBA(数据库管理员) 可以通过建立适当的密码重用策略来强制不重用。请参阅 密码重用策略。

密码重用策略
MySQL 允许限制重用以前的密码。可以根据密码更改的次数、经过的时间或两者兼而有之来建立重用限制。可以全局建立重用策略，并且可以将单个帐户设置为遵循全局策略或使用特定的每个帐户行为覆盖全局策略。

帐户的密码历史记录由过去分配的密码组成。MySQL 可以限制从该历史记录中选择新密码：

• 如果根据密码更改次数限制帐户，则无法从指定数量的最近密码中选择新密码。例如，如果密码更改的最小次数设置为 3，则新密码不能与最近的 3 个密码中的任何一个相同。
• 如果帐户根据经过的时间进行限制，则无法从历史记录中超过指定天数的密码中选择新密码。例如，如果密码重用间隔设置为 60，则新密码不得属于过去 60 天内先前选择的密码。

笔记
空密码不计入密码历史记录，可随时重复使用。

要全局建立密码重用策略，请使用 password_history和 password_reuse_interval系统变量。

例子：

• 要禁止重复使用最近 6 个密码或 365 天后的密码中的任何一个，请将这些行放入服务器 my.cnf文件中：> [mysqld]> password_history=6> password_reuse_interval=365
• 要在运行时设置和持久化变量，请使用如下语句：> SET PERSIST password_history = 6;> SET PERSIST password_reuse_interval = 365;

SET PERSIST为正在运行的 MySQL 实例设置一个值。它还保存该值以延续到后续服务器重新启动；请参阅 第 13.7.6.1 节，“变量赋值的 SET 语法”。要更改正在运行的 MySQL 实例的值而不使其延续到后续重新启动，请使用GLOBAL 关键字而不是PERSIST.

全局密码重用策略适用于所有未设置为覆盖它的帐户。要为个人帐户建立策略，请将

PASSWORD HISTORY 

和

PASSWORD REUSE INTERVAL

选项用到 CREATE USER 和 ALTER USER语句中。请参阅 第 13.7.1.3 节，“CREATE USER 语句” 和 第 13.7.1.1 节，“ALTER USER 语句”。

• 在允许重复使用之前至少需要更改 5 次密码：> CREATE USER ‘jeffrey’@‘localhost’ PASSWORD HISTORY 5;> ALTER USER ‘jeffrey’@‘localhost’ PASSWORD HISTORY 5;

这个历史长度选项会覆盖该语句命名的所有帐户的全局策略。

• 在允许重复使用之前至少需要 365 天：> CREATE USER ‘jeffrey’@‘localhost’ PASSWORD REUSE INTERVAL 365 DAY;> ALTER USER ‘jeffrey’@‘localhost’ PASSWORD REUSE INTERVAL 365 DAY;

这个经过时间的选项会覆盖该语句命名的所有帐户的全局策略。

• 要结合这两种类型的重用限制，请使用 PASSWORD HISTORY和PASSWORD REUSE INTERVAL一起：> CREATE USER ‘jeffrey’@‘localhost’> PASSWORD HISTORY 5> PASSWORD REUSE INTERVAL 365 DAY;> ALTER USER ‘jeffrey’@‘localhost’> PASSWORD HISTORY 5> PASSWORD REUSE INTERVAL 365 DAY;

这些选项会覆盖语句命名的所有帐户的全局策略重用限制。

• 遵循两种类型的重用限制的全局策略：> CREATE USER ‘jeffrey’@‘localhost’> PASSWORD HISTORY DEFAULT> PASSWORD REUSE INTERVAL DEFAULT;> ALTER USER ‘jeffrey’@‘localhost’> PASSWORD HISTORY DEFAULT> PASSWORD REUSE INTERVAL DEFAULT;

需要密码验证的策略

从 MySQL 8.0.13 开始，可以要求通过指定要替换的当前密码来验证更改帐户密码的尝试。这使 DBA 能够防止用户在不证明他们知道当前密码的情况下更改密码。否则，可能会发生此类更改，例如，如果一个用户在没有注销的情况下暂时离开终端会话，并且恶意用户使用该会话更改原始用户的 MySQL 密码。这可能会产生不幸的后果：

• 在管理员重置帐户密码之前，原始用户将无法访问 MySQL。
• 在密码重置发生之前，恶意用户可以使用良性用户更改的凭据访问 MySQL。

可以全局建立密码验证策略，并且可以将单个帐户设置为遵循全局策略或使用特定的每个帐户行为覆盖全局策略。

对于每个帐户，其mysql.user行指示是否存在特定于帐户的设置，要求验证当前密码以尝试更改密码。该设置由

CREATE USER

和

ALTER USER

语句 的

PASSWORD REQUIRE

选项建立：

• 如果帐户设置为PASSWORD REQUIRE CURRENT，密码更改必须指定当前密码。
• 如果帐户设置为PASSWORD REQUIRE CURRENT OPTIONAL，则可以更改密码，但无需指定当前密码。
• 如果帐户设置为PASSWORD REQUIRE CURRENT DEFAULT，则 password_require_current 系统变量确定帐户的验证要求策略：- 如果 password_require_current 启用，密码更改必须指定当前密码。- 如果 password_require_current 禁用，密码更改可能但不需要指定当前密码。

换句话说，如果帐户设置不是

PASSWORD REQUIRE CURRENT DEFAULT

，则帐户设置优先于

password_require_current

系统变量建立的全局策略。否则，帐户将遵循

password_require_current 

设置。

默认情况下，密码验证是可选的：

password_require_current

被禁用并且没有

PASSWORD REQUIRE

选项创建的帐户默认为

PASSWORD REQUIRE CURRENT DEFAULT

.

下表显示了每个帐户的设置如何与 password_require_current 系统变量值交互以确定帐户密码验证所需的策略。

表 6.10 密码验证策略
每个帐户设置password_require_current 系统变量密码更改需要当前密码？PASSWORD REQUIRE CURRENTOFF是的PASSWORD REQUIRE CURRENTON是的PASSWORD REQUIRE CURRENT OPTIONALOFF不PASSWORD REQUIRE CURRENT OPTIONALON不PASSWORD REQUIRE CURRENT DEFAULTOFF不PASSWORD REQUIRE CURRENT DEFAULTON是的

笔记
无论需要验证的策略如何，特权用户都可以更改任何帐户密码而无需指定当前密码。特权用户是具有全局CREATE USER 特权或mysql系统数据库 UPDATE 特权的用户。

要全局建立密码验证策略，请使用 password_require_current 系统变量。其默认值为OFF，因此帐户密码更改不需要指定当前密码。

• 要建立密码更改必须指定当前密码的全局策略，请在服务器my.cnf文件中使用以下行启动服务器：> [mysqld]> password_require_current=ON
• 要在运行时设置和持久 password_require_current化，请使用如下语句之一：> SET PERSIST password_require_current = ON;> SET PERSIST password_require_current = OFF;SET PERSIST为正在运行的 MySQL 实例设置一个值。它还保存该值以延续到后续服务器重新启动；请参阅 第 13.7.6.1 节，“变量赋值的 SET 语法”。要更改正在运行的 MySQL 实例的值而不使其延续到后续重新启动，请使用GLOBAL 关键字而不是PERSIST.

全局密码验证要求策略适用于所有未设置为覆盖它的帐户。要为个人帐户建立策略，请使用 CREATE USER 和 ALTER USER语句的PASSWORD REQUIRE选项。请参阅第 13.7.1.3 节，“CREATE USER 语句”和 第 13.7.1.1 节，“ALTER USER 语句”。

• 要求密码更改指定当前密码：CREATEUSER'jeffrey'@'localhost' PASSWORD REQUIRECURRENT;ALTERUSER'jeffrey'@'localhost' PASSWORD REQUIRECURRENT;此验证选项会覆盖该语句命名的所有帐户的全局策略。
• 不要求密码更改指定当前密码（当前密码可能但不需要给出）：CREATEUSER'jeffrey'@'localhost' PASSWORD REQUIRECURRENT OPTIONAL;ALTERUSER'jeffrey'@'localhost' PASSWORD REQUIRECURRENT OPTIONAL;此验证选项会覆盖该语句命名的所有帐户的全局策略。
• 遵循语句命名的所有帐户的全局密码验证要求策略：CREATEUSER'jeffrey'@'localhost' PASSWORD REQUIRECURRENTDEFAULT;ALTERUSER'jeffrey'@'localhost' PASSWORD REQUIRECURRENTDEFAULT;

当用户使用

ALTER USER

or

SET PASSWORD

语句更改密码时，对当前密码的验证就会发挥作用。示例使用

ALTER USER

，它优于

SET PASSWORD

，但此处描述的原则对于两种语句都是相同的。
在密码更改语句中，一个REPLACE 子句指定要替换的当前密码。例子：

• 更改当前用户的密码：ALTERUSERUSER() IDENTIFIED BY'auth_string'REPLACE'current_auth_string';
• 更改指定用户的密码：ALTERUSER'jeffrey'@'localhost' IDENTIFIED BY'auth_string'REPLACE'current_auth_string';
• 更改指定用户的身份验证插件和密码：ALTERUSER'jeffrey'@'localhost' IDENTIFIED WITH caching_sha2_password BY'auth_string'REPLACE'current_auth_string';

该REPLACE子句的工作方式如下：

• REPLACE如果需要更改帐户的密码以指定当前密码，则必须给出，以验证尝试进行更改的用户实际上知道当前密码。
• REPLACE如果帐户的密码更改可能但不需要指定当前密码，则为可选。
• 如果REPLACE指定，则必须指定正确的当前密码，否则会出错。即使REPLACE是可选的也是如此。
• REPLACE只有在更改当前用户的帐户密码时才能指定。（这意味着在刚刚显示的示例中，jeffrey 除非当前用户是 ，否则显式命名帐户的语句将失败jeffrey。）即使特权用户尝试为另一个用户进行更改也是如此；但是，这样的用户可以更改任何密码而无需指定REPLACE.
• REPLACE从二进制日志中省略，以避免向其写入明文密码。

双密码支持

从 MySQL 8.0.14 开始，允许用户帐户具有双重密码，指定为主密码和辅助密码。双密码功能可以在以下场景中无缝执行凭据更改：

• 一个系统有大量 MySQL 服务器，可能涉及复制。
• 多个应用程序连接到不同的 MySQL 服务器。
• 必须对应用程序用于连接到服务器的一个或多个帐户进行定期凭据更改。

考虑在前一种情况下，当一个帐户只允许一个密码时，必须如何执行凭据更改。在这种情况下，必须密切配合帐户密码更改的时间并在所有服务器中传播，以及使用该帐户的所有应用程序更新以使用新密码的时间。此过程可能涉及服务器或应用程序不可用的停机时间。

使用双密码，可以更轻松地分阶段更改凭证，无需密切合作，也无需停机：

1. 对于每个受影响的帐户，在服务器上建立一个新的主密码，保留当前密码作为辅助密码。这使服务器能够识别每个帐户的主密码或辅助密码，而应用程序可以继续使用与以前相同的密码（现在是辅助密码）连接到服务器。
2. 在密码更改传播到所有服务器后，修改使用任何受影响帐户的应用程序以使用帐户主密码进行连接。
3. 在所有应用程序都从二级密码迁移到一级密码之后，二级密码不再需要并且可以丢弃。在此更改传播到所有服务器后，只能使用每个帐户的主密码进行连接。凭证更改现已完成。

MySQL 使用保存和丢弃辅助密码的语法实现双密码功能：

• 当您分配新的主密码时， and 语句 的RETAIN CURRENT PASSWORD子句将帐户当前密码保存为其辅助密码。 ALTER USERSET PASSWORD
• DISCARD OLD PASSWORDfor 子句 ALTER USER丢弃帐户二级密码，只留下主密码 。

假设,对于前面描述的 凭证更改 场景， 一个名为 ‘appuser1’@‘host1.example.com’ 的帐户使用应用程序连接到服务器，并且帐户密码将从 ‘password_a’ 更改为 ‘password_b’。

要执行此凭据更改，请使用ALTER USER执行以下命令：

1. 在每个不是副本的服务器上，建立 新的主密码，保留当前密码作为辅助密码： 'password_b’appuser1ALTERUSER'appuser1'@'host1.example.com' IDENTIFIED BY'password_b' RETAIN CURRENT PASSWORD;
2. 等待密码更改在整个系统中复制到所有副本。
3. 修改使用该appuser1帐户的每个应用程序， 使其使用密码 ‘password_b’ 而不是 ‘password_a’ 连接到服务器 。
4. 此时，不再需要二级密码。在每个不是副本的服务器上，丢弃辅助密码：ALTERUSER'appuser1'@'host1.example.com'DISCARD OLD PASSWORD;
5. 丢弃密码更改复制到所有副本后，凭据更改完成。

RETAIN CURRENT PASSWORDand DISCARD OLD PASSWORD子句具有以下作用 ：

• RETAIN CURRENT PASSWORD保留帐户当前密码作为其二级密码，替换任何现有的二级密码。新密码成为主密码，但客户端可以使用该帐户使用主密码或辅助密码连接到服务器。ALTER USER （例外：如果or语句指定的新密码SET PASSWORD为空，则二级密码也为空，即使 RETAIN CURRENT PASSWORD已给出。）
• 如果您RETAIN CURRENT PASSWORD 为具有空主密码的帐户指定，则该语句将失败。
• 如果帐户有二级密码，而您更改了其主密码而未指定RETAIN CURRENT PASSWORD，则二级密码保持不变。
• 对于ALTER USER，如果您更改分配给该帐户的身份验证插件，则二级密码将被丢弃。如果您更改身份验证插件并指定RETAIN CURRENT PASSWORD，则该语句将失败。
• 对于ALTER USER， DISCARD OLD PASSWORD丢弃二级密码（如果存在）。该帐户仅保留其主密码，客户端可以使用该帐户仅通过主密码连接到服务器。

修改二级密码的语句需要以下权限：

• 需要该 APPLICATION_PASSWORD_ADMIN 权限才能将RETAIN CURRENT PASSWORDorDISCARD OLD PASSWORD子句用于适用于您自己帐户的ALTER USER和语句。SET PASSWORD操作您自己的二级密码需要该权限，因为大多数用户只需要一个密码。
• 如果要允许一个帐户操纵所有帐户的二级密码，则应授予该 CREATE USER权限而不是 APPLICATION_PASSWORD_ADMIN.

随机密码生成

从 MySQL 8.0.18 开始，CREATE USER、ALTER USER和 SET PASSWORD语句能够为用户帐户生成随机密码，作为要求显式管理员指定的文字密码的替代方法。有关语法的详细信息，请参阅每个语句的说明。本节介绍生成的随机密码的共同特征。

默认情况下，生成的随机密码长度为 20 个字符。该长度由

generated_random_password_length

系统变量控制，范围为 5 到 255。

对于语句为其生成随机密码的每个帐户，该语句将密码存储在 mysql.user系统表中，并针对帐户身份验证插件进行适当的哈希处理。该语句还在结果集中的一行中返回明文密码，以使其可供执行该语句的用户或应用程序使用。mysql.user系统表中受影响行的结果集列被命名为

user

、

host

、

generated password

，和

auth_factor

分别表示: 用户名, 主机名、明文生成的密码 , 显示的密码值适用的身份验证因素。

mysql>CREATEUSER'u1'@'localhost' IDENTIFIED BY RANDOM PASSWORD,'u2'@'%.example.com' IDENTIFIED BY RANDOM PASSWORD,'u3'@'%.org' IDENTIFIED BY RANDOM PASSWORD;+------+---------------+----------------------+-------------+|user| host          | generated password   | auth_factor |+------+---------------+----------------------+-------------+| u1   | localhost     | iOeqf>Mh9:;XD&qn(Hl} |1|| u2   |%.example.com | sXTSAEvw3St-R+_-C3Vb |1|| u3   |%.org         | nEVe%Ctw/U/*Md)Exc7&|1|+------+---------------+----------------------+-------------+
mysql>ALTERUSER'u1'@'localhost' IDENTIFIED BY RANDOM PASSWORD,'u2'@'%.example.com' IDENTIFIED BY RANDOM PASSWORD;+------+---------------+----------------------+-------------+|user| host          | generated password   | auth_factor |+------+---------------+----------------------+-------------+| u1   | localhost     | Seiei:&cw}8]@3OA64vh|1|| u2   |%.example.com | j@&diTX80l8}(NiHXSae |1|+------+---------------+----------------------+-------------+
mysql>SET PASSWORD FOR'u3'@'%.org'TO RANDOM;+------+-------+----------------------+-------------+|user| host  | generated password   | auth_factor |+------+-------+----------------------+-------------+| u3   |%.org | n&cz2xF;P3!U)+]Vw52H |1|+------+-------+----------------------+-------------+

一个为帐户生成随机密码的

CREATE USER

,

ALTER USER,

或

SET PASSWORD

语句将作为带有

IDENTIFIED WITH auth_plugin AS 'auth_string'

子句的 CREATE USER 或 ALTER USER 语句写入二进制日志，其中auth_plugin 是 帐户身份验证插件， 'auth_string’是帐户哈希密码值。

如果安装了

validate_password

组件，则它实施的策略对生成的密码没有影响。（密码验证的目的是帮助人们创建更好的密码。）

登录失败跟踪和临时帐户锁定

从 MySQL 8.0.19 开始，管理员可以配置用户帐户，以便太多连续登录失败导致临时帐户锁定。

在这种情况下， “登录失败”是指客户端在连接尝试期间未能提供正确的密码。它不包括由于未知用户或网络问题等原因导致的连接失败。对于具有双重密码的帐户（请参阅双重密码支持），任何一个帐户密码都被视为正确。

使用

CREATE USER 

和

ALTER USER

语句的

FAILED_LOGIN_ATTEMPTS

和

PASSWORD_LOCK_TIME

选项 可以为每个帐户配置所需的登录失败次数和锁定时间 。例子：

CREATEUSER'u1'@'localhost' IDENTIFIED BY'password'
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3;ALTERUSER'u2'@'localhost'
  FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME UNBOUNDED;

当发生太多连续登录失败时，客户端会收到如下所示的错误：

ERROR 3957 (HY000): Access denied for user user.
Account is blocked for D day(s) (R day(s) remaining)
due to N consecutive failed logins.

使用如下选项：

• FAILED_LOGIN_ATTEMPTS N

此选项指示是否跟踪指定错误密码的帐户登录尝试。该数字 N指定有多少连续错误密码导致临时帐户锁定。

• PASSWORD_LOCK_TIME {N | UNBOUNDED}

此选项指示在连续多次登录尝试提供错误密码后锁定帐户多长时间。该值是一个数字N ，用于指定帐户保持锁定的天数，或者 UNBOUNDED指定当帐户进入临时锁定状态时，该状态的持续时间是无限的，并且在帐户解锁之前不会结束。稍后将描述解锁发生的条件。

每个选项的允许值N在 0 到 32767 的范围内。值为 0 将禁用该选项。

登录失败跟踪和临时帐户锁定具有以下特征：

• 对于帐户发生登录失败跟踪和临时锁定，其FAILED_LOGIN_ATTEMPTS和 PASSWORD_LOCK_TIME选项都必须非零。
• 对于CREATE USER，如果 FAILED_LOGIN_ATTEMPTS或未 PASSWORD_LOCK_TIME指定，其隐含的默认值为 0 对于语句命名的所有帐户。这意味着禁用登录失败跟踪和临时帐户锁定。（这些隐式默认值也适用于在引入失败登录跟踪之前创建的帐户。）
• 对于ALTER USER，如果 FAILED_LOGIN_ATTEMPTS或未 PASSWORD_LOCK_TIME指定，其值对于该语句命名的所有帐户保持不变。
• 要发生临时帐户锁定，密码失败必须是连续的。在达到FAILED_LOGIN_ATTEMPTS 失败登录值之前发生的任何成功登录都会导致失败计数重置。例如，如果FAILED_LOGIN_ATTEMPTS是 4 并且发生了三个连续的密码失败，则需要再失败一次才能开始锁定。但是，如果下次登录成功，则该帐户的登录失败计数将被重置，因此再次需要连续四次失败才能锁定。
• 一旦临时锁定开始，即使使用正确的密码也无法成功登录，直到锁定持续时间已过或通过以下讨论中列出的帐户重置方法之一解锁帐户。

当服务器读取授权表时，它会初始化每个帐户的状态信息，例如是否启用了登录失败跟踪，该帐户当前是否被临时锁定以及何时开始锁定，如果该帐户发生临时锁定之前的失败次数未锁定。

可以重置帐户的状态信息，这意味着重置失败登录计数，并且如果当前暂时锁定该帐户，则该帐户将被解锁。帐户重置可以对所有帐户或每个帐户都是全局的：

• 对于以下任何一种情况，都会对所有帐户进行全局重置：- 服务器重新启动。- 的执行FLUSH PRIVILEGES。（启动服务器 --skip-grant-tables 会导致无法读取授权表，这会禁用登录失败跟踪。在这种情况下，第一次执行FLUSH PRIVILEGES 会导致服务器读取授权表并启用登录失败跟踪，此外还会重置所有帐户.)
• 对于以下任何一种情况，都会发生按帐户重置：- 成功登录帐户。- 锁定持续时间过去。在这种情况下，登录失败计数会在下次登录尝试时重置。- ALTER USER为将其中一个FAILED_LOGIN_ATTEMPTS或 PASSWORD_LOCK_TIME（或两者）设置为任何值（包括当前选项值）的帐户执行语句，或为帐户执行 语句ALTER USER … UNLOCK。该帐户的其他ALTER USER 语句对其当前的登录失败计数或其锁定状态没有影响。

登录失败跟踪与用于检查凭据的登录帐户相关联。如果正在使用用户代理，则为代理用户而不是代理用户进行跟踪。也就是说，跟踪与 指示的帐户相关联 USER()，而不是与指示的帐户相关联CURRENT_USER()。有关代理和代理用户之间的区别的信息，请参阅第 6.2.19 节，“代理用户”。

重命名用户或角色

RENAME USER

MySQL8.0官参 —重命名用户 — 13.7.1.7 RENAME USER Statement

RENAMEUSER old_user TO new_user
    [, old_user TO new_user]...

RENAMEUSER'jeffrey'@'localhost'TO'jeff'@'127.0.0.1';

用户和角色都能用

RENAME USER

改名 , 没有 RENAME ROLE 命令

DROPUSERIFEXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;CREATEUSERIFNOTEXISTS u1,u2;CREATE ROLE IFNOTEXISTS r1,r2;SELECTuser,host,plugin FROM mysql.user;RENAMEUSER u1 TO u1@localhost, r1 TO r1@localhost,  u2 TO u3 , r2 TO r3;SELECTuser,host,plugin FROM mysql.user;DROP ROLE IFEXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;SELECTuser,host,plugin FROM mysql.user;

授权 , 允许权力继续传递 , 撤权

授权语法:

GRANT 权限以逗号分隔 ON 库.表 TO 账户以逗号分隔 [WITH GRANT OPTION]

官方 GRANT 语句说明 — 13.7.1.6 GRANT Statement

将所有库的所有表的所有权限,授权给

GRANT ALL PRIVILEGES ON *.* TO

或

GRANT ALL ON *.* TO

ALL

等同

ALL PRIVILEGES

, 表示所有权限

*.*

表示所有库下的所有表
执行该操作的用户必须具有这些权限

GRANTALLON*.*TO
等同
GRANTALLPRIVILEGESON*.*TO

GRANTALLON*.*TO'user'@'%';FLUSH PRIVILEGES;

将所有库所有表的 select 和 insert 权限授予名为si的用户, 并允许权力继续传递

createuser si@'%' identifity by'si';grantselect,inserton*.*to si@'%';
flush privileges;

将用户1的权力传递给用户2

GRANT 用1TO 用2; FLUSH PRIVILEGES;

将角色的权力传递给用户

GRANT 角色 TO 用户; FLUSH PRIVILEGES;

将用户的权力传递给角色

GRANT 用户 TO 角色; FLUSH PRIVILEGES;

将角色1的权利传递给角色2

GRANT 角色1TO 角色2; FLUSH PRIVILEGES;

允许权力传递

WITH GRANT OPTION

赋权语句后接

WITH GRANT OPTION

, 允许被授权的用户继续传递权力, 如果赋权者的权力被回收, 被赋权者的权力也会被回收

超级用户授权给另一个超级用户

#  超级用户授权给另一个超级用户GRANTALLPRIVILEGESON*.*TO'super'@'%'WITHGRANTOPTION; FLUSH PRIVILEGES;# 或者省略 PRIVILEGES ,效果一样 GRANTALLON*.*TO'super'@'%'WITHGRANTOPTION; FLUSH PRIVILEGES;

将所有库所有表的 select 和 insert 权限授予名为si的用户, 并允许权力继续传递

createuser si@'%' identifity by'si';grantselect,inserton*.*to si@'%'withgrantoption;
flush privileges;

用户和用户,角色和角色,用户和角色,角色和用户,可以相互授权, 但要避免权力循环

CREATEUSERifnotexists u1,u2,u3,user007;CREATE ROLE ifnotexists r1,r2,r3,role007;GRANT u1,u2,u3,r1,r2,r3 TO user007,role007;

但是不能将自己授权给自己, 或者将a授权给b,又将b授权给a, 形成了死循环. 官方例子👇

CREATEUSER'u1','u2';CREATE ROLE 'r1','r2';GRANT'u1'TO'u1';-- simple loop: u1 => u1GRANT'r1'TO'r1';-- simple loop: r1 => r1GRANT'r2'TO'u2';GRANT'u2'TO'r2';-- mixed user/role loop: u2 => r2 => u2

权力不能循环例子2

dropuserifexists u1,u2,u3,r1,r2,r3;createuserifnotexists u1,u2,u3;create role ifnotexists r1,r2,r3;grant r1 to u1;grant u1 to r2;grant r2 to u2;grant u2 to r3;grant r3 to u3;grant u3 to r1;# 前面都能执行成功,这句形成了循环,执行失败

结果

mysql>dropuserifexists u1,u2,u3,r1,r2,r3;
Query OK,0rows affected (0.01 sec)

mysql>createuserifnotexists u1,u2,u3;
Query OK,0rows affected (0.01 sec)

mysql>create role ifnotexists r1,r2,r3;
Query OK,0rows affected (0.00 sec)

mysql>grant r1 to u1;
Query OK,0rows affected (0.01 sec)

mysql>grant u1 to r2;
Query OK,0rows affected (0.00 sec)

mysql>grant r2 to u2;
Query OK,0rows affected (0.01 sec)

mysql>grant u2 to r3;
Query OK,0rows affected (0.00 sec)

mysql>grant r3 to u3;
Query OK,0rows affected (0.01 sec)

mysql>grant u3 to r1;# 前面都能执行成功,这句形成了循环,执行失败
ERROR 4027(HY000): User account `r1`@`%`is directly or indirectly granted to the role `u3`@`%`. The GRANT would create a loop

撤销授权

撤销所有授权

#   撤销全部权限REVOKEALLPRIVILEGES,GRANTOPTIONFROM 用户或角色1, 用户2, 角色3, 用户4;

可加上

 IF EXISTS

, 有对应权限才执行撤销动作

#   撤销全部权限REVOKEIFEXISTSALLPRIVILEGES,GRANTOPTIONFROM 用户或角色 , 用户2, 角色7;

ALL PRIVILEGES

=

ALL

#   撤销全部权限REVOKEIFEXISTSALL,GRANTOPTIONFROM   用户或角色 , 用户2, 角色7;

可以后加

IGNORE UNKNOWN USER

忽略未知用户

#   撤销全部权限REVOKEIFEXISTSALL,GRANTOPTIONFROM   用户或角色 , 用户2, 角色7IGNORE UNKNOWN USER;

撤销远程root的全部权限

#   撤销远程root的全部权限REVOKEIFEXISTSALL,GRANTOPTIONFROM   root@'%'IGNORE UNKNOWN USER;

#   撤销远程root的全部权限, root=root@'%' , 不会影响 root@localhostREVOKEIFEXISTSALL,GRANTOPTIONFROM   root IGNORE UNKNOWN USER;

撤销指定权限

撤销user001对于所有库所有表的 insert,update,delete 权限

REVOKEinsert,update,deleteON*.*FROM'user001'@'%';

撤销本机用户user002@localhost对于db2库下的所有表的 insert,update,delete 权限

REVOKEinsert,update,deleteON db2.*FROM'user002'@'localhost';

撤销本机用户user003@localhost对于 db3库下的t1表 和 db4下的t3表 的 insert,update,delete 权限

REVOKEinsert,update,deleteON db3.t1,db4.t3 FROM user003@localhost;

对于用户 u1@‘%’ , u2@‘%’ , u3@‘localhost’ , u4@‘%’ , 如果用户存在,且update权限存在, 就撤销

REVOKEIFEXISTSupdateON db3.t1,db4.t3 FROM u1,u2,u3@loalhost,u4 IGNORE UNKNOWN USER;

GRANT 和 REVOKE 语法对比

GRANTinsert,updateON*.*TO 账户
REVOKEinsert,updateON*.*FROM 账户

GRANTALLON*.*TO 账户
REVOKEALLON*.*FROM 账户

REVOKE语句可以加

IF EXISTS

和

IGNORE UNKNOWN USER 

REVOKEIFEXISTS 权限1,权限2ON*.*FROM 账户1,账户2,账户3IGNORE UNKNOWN USER;

超级用户创建并授权超级用户

#  超级用户授权给另一个超级用户CREATEUSERIFNOTEXISTS'super'@'%' IDENTIFIED BY'super';GRANTALLON*.*TO'super'@'%'WITHGRANTOPTION; FLUSH PRIVILEGES;

删除用户

DROP USER 'u'@'h'

DROP USER 语法

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

DROPUSER supper;

DROPUSERIFEXISTS supper;

DROPUSER 张三 , 李四@'%', 王五@'192.168.1.3';

DROPUSERIFEXISTS 张三,李四@'%',王五@'192.168.1.3';

创建用户,授权,删除用户的官方例子

官方文档 - 6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts

CREATEUSER'finley'@'localhost'
  IDENTIFIED BY'password';GRANTALLON*.*TO'finley'@'localhost'WITHGRANTOPTION;CREATEUSER'finley'@'%.example.com'
  IDENTIFIED BY'password';GRANTALLON*.*TO'finley'@'%.example.com'WITHGRANTOPTION;CREATEUSER'admin'@'localhost'
  IDENTIFIED BY'password';GRANT RELOAD,PROCESS
  ON*.*TO'admin'@'localhost';CREATEUSER'dummy'@'localhost';

CREATEUSER'custom'@'localhost'
  IDENTIFIED BY'password';GRANTALLON bankaccount.*TO'custom'@'localhost';CREATEUSER'custom'@'host47.example.com'
  IDENTIFIED BY'password';GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROPON expenses.*TO'custom'@'host47.example.com';CREATEUSER'custom'@'%.example.com'
  IDENTIFIED BY'password';GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROPON customer.addresses
  TO'custom'@'%.example.com';

角色管理

官方文档, 使用角色 — 6.2.10 Using Roles

用

CREATE ROLE

创建的角色也会出现在

mysql.user

表中, 只是被锁定

可以用

select user,host from mysql.user

查看到

CREATE ROLE IFNOTEXISTS role001@'localhost';CREATE ROLE IFNOTEXISTS role002@'%';CREATE ROLE IFNOTEXISTS role003;CREATE ROLE IFNOTEXISTS role168@'192.168.168.168';SELECTuser,host,plugin FROM mysql.user;

个人感觉,
用户和角色都能各自携带被赋予的权限,并且传递给用户或角色,

• 用户 to 用户,
• 用户 to 角色,
• 角色 to 用户,
• 角色 to 角色

用户和角色的授权语法基本一样
删除角色不单可用

DROP ROLE

, 还可用

DROP USER

角色改名可以用

RENAME USER

用户和角色都能用

RENAME USER

改名 , 没有 RENAME ROLE 命令

DROPUSERIFEXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;CREATEUSERIFNOTEXISTS u1,u2;CREATE ROLE IFNOTEXISTS r1,r2;SELECTuser,host,plugin FROM mysql.user;RENAMEUSER u1 TO u1@localhost, r1 TO r1@localhost,  u2 TO u3 , r2 TO r3;SELECTuser,host,plugin FROM mysql.user;DROP ROLE IFEXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;SELECTuser,host,plugin FROM mysql.user;

角色和用户的区别只在于用户可以登录,角色不能登录
用户用 create user 创建, 可以指定密码
角色用 create role 创建, 不能指定密码
角色不能登录,会报被锁定👇

mysql -urole001
ERROR 3118 (HY000): Access denied for user ‘role001’@‘localhost’. Account is locked.

创建角色

CREATE ROLE

,

create role

create role 创建角色的语法

CREATE ROLE [IFNOTEXISTS] role [, role ]...

create role 创建角色的栗子

CREATE ROLE 'admin','developer';CREATE ROLE 'webapp'@'localhost';CREATE ROLE '远程1组'@'192.168.1,13','开发1组'@'192.168.1,17','remote3'@'%'CREATE ROLE IFNOTEXISTS 角色1,角色2,角色3,role4,role5,role6;

授予角色权限的语法和授予用户权限的语法一样

MySQL8.0官参—13.7.1.6 GRANT Statement

GRANTSELECT,INSERTON*.*TO role001;GRANTSELECT,INSERTON schema3.table2 TO role001 WITHGRANTOPTION;GRANTALLON*.*TO role001;GRANTALLON database5.table1 TO role001 WITHGRANTOPTION;GRANT 角色1TO 角色3GRANT 用户3,角色2,角色1TO 角色2,用户5,角色7# 不能 GRANT 自己 TO 自己 会产生循环

试验1

# 试验CREATE ROLE ifnotexists u1,u2,u3,user007,r1,r2,r3,role007;CREATEUSERifnotexists u1,u2,u3,user007;CREATE ROLE ifnotexists r1,r2,r3,role007;GRANT u1,u2,u3,r1,r2,r3 TO user007,role007;GRANT u1,u2,u3,r1,r2,r3 TO u1,u2,u3,r1,r2,r3;# 这句不能成功, 因为会形成权力循环

权力不能循环例子2

dropuserifexists u1,u2,u3,r1,r2,r3;createuserifnotexists u1,u2,u3;create role ifnotexists r1,r2,r3;grant r1 to u1;grant u1 to r2;grant r2 to u2;grant u2 to r3;grant r3 to u3;grant u3 to r1;# 前面都能执行成功,这句形成了循环,执行失败

激活角色

被授予(grant)角色的用户, 需要要激活其角色, 才能使用角色所具有的权限;

不单角色是角色,账户也可以当作角色.

默认情况下，自动角色激活被禁用。
可能: 用

show grants

看到角色已被授予, 但

select current_role()

并没看到被授予的角色
还需要激活才能发挥作用

可以用

activate_all_roles_on_login

设置是否(ON/OFF)在用户登陆时,激活其被GRANT语句授予的角色

可以用

SET DEFAULT ROLE

语句,在用户被授予的角色中,选取一些角色作为默认角色,在登陆时自动激活

查看用户的角色 , 或角色的角色

查看当前用户已被授予的角色和权限

SHOW grants

;

SHOW grants;

查看当前用户的已激活的角色(自己的角色) :

SELECT current_role();

SELECT current_role();

复制粘贴,一步到位

SHOW grants;--       ---------- 当前用户的权限和角色;SELECT current_role();--     -- 当前用户已激活的角色;

SET ROLE

, 临时激活角色

set role 激活的角色, 会在重新登陆后取消激活
例如:激活角色1,角色2,角色3, 在本次登录有效

SET ROLE 角色1,角色2,角色3

SET ROLE ALL

临时激活所有被grant授予的角色

SET ROLE ALL;

SET ROLE DEFAULT

临时激活默认的角色

SET ROLE DEFAULT;

SET ROLE NONE

取消激活的角色

SET ROLE NONE;

设置默认角色

SET DEFAULT ROLE

,

SET ROLE DEFAULT

和

SET DEFAULT ROLE

是两个 功能不同,但功能相关, 的命令语句

• set default role 不能用在自己身上, 不能自己给自己设置默认角色 ,
• set role default 是用在自己身上

当 你 用

set default role

给 他 设置默认角色后, 他登录时就是默认角色, 当他用

set role

改变激活的默认角色后, 可以用

set role default

切换到默认角色

例子如下👇

1. 先用授权用户,将角色授给被授用户, 再用set default role指定被授用户的默认角色

mysql>createuser admin007;
Query OK,0rows affected (0.01 sec)# 已创建 admin001,admin002,admin003,admin004,admin005,admin006
mysql>GRANT  root,admin001,admin002,admin003,admin004,admin005,admin006 TO admin007;
Query OK,0rows affected (0.01 sec)

mysql>SETDEFAULT ROLE admin005,admin006 TO admin007;
Query OK,0rows affected (0.01 sec)

1. 登录被授权的用户
2. 被授权用户通过select current_role可看到, 当前激活角色是默认角色, set role all后是所有角色, 再set role default后,又回到默认角色

mysql>exit
Bye
admin@VM-12-12-ubuntu:~$ mysql -u admin007
Welcome to the MySQL monitor.  Commands endwith;or \g.
Your MySQL connection id is97
Server version: 8.0.30-0ubuntu0.20.04.2(Ubuntu)

Copyright (c)2000,2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type'help;'or'\h'for help.Type'\c'to clear the current input statement.

mysql>select current_role();+-------------------------------+| current_role()|+-------------------------------+|`admin005`@`%`,`admin006`@`%`|+-------------------------------+1rowinset(0.00 sec)

mysql>SET ROLE ALL;
Query OK,0rows affected (0.00 sec)

mysql>SELECT CURRENT_ROLE();+------------------------------------------------------------------------------------------------------+| CURRENT_ROLE()|+------------------------------------------------------------------------------------------------------+|`admin001`@`%`,`admin002`@`%`,`admin003`@`%`,`admin004`@`%`,`admin005`@`%`,`admin006`@`%`,`root`@`%`|+------------------------------------------------------------------------------------------------------+1rowinset(0.00 sec)

mysql>SET ROLE DEFAULT;
Query OK,0rows affected (0.00 sec)

mysql>SELECT current_role();+-------------------------------+| current_role()|+-------------------------------+|`admin005`@`%`,`admin006`@`%`|+-------------------------------+1rowinset(0.00 sec)

mysql>

将用户所有grant语句授予的角色设为默认角色 ,

SET DEFAULT ROLE ALL TO

SETDEFAULT ROLE ALLTO u1,u2,r1,r2,u3@localhost,r3@'127.0.0.1';

默认角色必须属于被GRANT语句授予的角色,测试例

DROPUSERIFEXISTS u1,u2,u3,r1,r2,r3,user100,role100;CREATEUSER u1,u2,u3,user100;CREATE ROLE r1,r2,r3,role100;GRANT u1,u2,r1,r2 TO user100,role100;SETDEFAULT ROLE u1,u2,r1,r2 TO user100,role100;#这句能执行成功SETDEFAULT ROLE u3,r3 TO user100,role100;#这句会执行失败, 因为u3和r3未被GRANTDROP ROLE IFEXISTS u1,u2,u3,r1,r2,r3,user100,role100;

设置全局变量

activate_all_roles_on_login

,

ON

启用, 默认

OFF

停用 , 用户登陆时激活所有GRANT语句授予的角色

查看变量 activate_all_roles_on_login , 登录时激活用户被grant授予的角色 , ON为启用,OFF为停用

# 查看变量 activate_all_roles_on_login , 登录时激活用户被grant授予的角色 , ON为启用,OFF为停用SHOW VARIABLES LIKE'%activate_all_roles_on_login%';SHOW VARIABLES LIKE'activate_all_roles_on_login';SELECT @@activate_all_roles_on_login;

在配置文件设置设置

activate_all_roles_on_login

每次重启都有效

MySQL8.0官方文档—使用配置文件 — 4.2.2.2 Using Option Files
配置文件的位置

• Windows 在MySQL安装目录,手工创建 my.inf或my.cnf``````%WINDIR%\my.ini , %WINDIR%\my.cnf``````C:\my.ini , C:\my.cnf``````BASEDIR\my.ini , BASEDIR\my.cnf
• Linux 在 /etc/my.cnf , /etc/mysql/my.cnf , SYSCONFDIR/my.cnf``````/etc/mysql/mysql.conf.d/mysql.conf``````/etc/mysql/mysql.conf.d/mysqld.conf

在

[mysqld]

下方加上

activate_all_roles_on_login='ON'

或

activate_all_roles_on_login="ON"

或

activate_all_roles_on_login=ON

[mysqld]
# 左右边和等号左右可以有空格和制表符 , 值可以单引双引不引
# 启用 activate_all_roles_on_login , 用户登录时,激活所有GRANT语句授予的角色
activate_all_roles_on_login=ON
   activate_all_roles_on_login = 'ON'
           activate_all_roles_on_login            =        "ON"
# 停用 activate_all_roles_on_login , 用户登录时,激活所有GRANT语句授予的角色
activate_all_roles_on_login=OFF
   activate_all_roles_on_login='OFF'
        activate_all_roles_on_login="OFF"

用

SET GLOBAL

命令设置

activate_all_roles_on_login

重启后失效

用

SET GLOBAL

命令设置

activate_all_roles_on_login

用户登录时,激活所有GRANT语句授予的角色, 重启后会失效

#  用户登录时,激活所有GRANT语句授予的角色 重启后会失效SETGLOBAL  activate_all_roles_on_login='ON';

SHOW VARIABLES LIKE'%activate_all_roles_on_login%';SETGLOBAL  activate_all_roles_on_login='OFF';SHOW VARIABLES LIKE'activate_all_roles_on_login';SETGLOBAL  activate_all_roles_on_login='ON';SHOW VARIABLES LIKE'%activate_all_roles_on_login';SETGLOBAL  activate_all_roles_on_login="OFF";SHOW VARIABLES LIKE'activate_all_roles_on_login%';SETGLOBAL  activate_all_roles_on_login=ON;SHOW VARIABLES LIKE'activate_all_roles_on_login';

用

SET PERSIST

命令设置

activate_all_roles_on_login

重启后有效,且优先级大于传统配置文件

#  用户登录时,激活所有GRANT语句授予的角色 , 用 SET PERSIST 重启后有效,且优先级大于传统配置文件SET PERSIST  activate_all_roles_on_login='ON';

MySQL8.0官参—13.7.1.11 SET ROLE       Statement
MySQL8.0官参—13.7.1.9  SET DEFAULT ROLE Statement

撤销角色权限的语法和撤销用户权限的语法一样, 用

REVOKE...FROM...

MySQL8.0官参----13.7.1.8 REVOKE Statement

REVOKE[IFEXISTS]
    priv_type [(column_list)][, priv_type [(column_list)]]...ON[object_type] priv_level
    FROM user_or_role [, user_or_role]...[IGNORE UNKNOWN USER]REVOKE[IFEXISTS]ALL[PRIVILEGES],GRANTOPTIONFROM user_or_role [, user_or_role]...[IGNORE UNKNOWN USER]REVOKE[IFEXISTS] PROXY ON user_or_role
    FROM user_or_role [, user_or_role]...[IGNORE UNKNOWN USER]REVOKE[IFEXISTS] role [, role ]...FROM user_or_role [, user_or_role ]...[IGNORE UNKNOWN USER]

user_or_role: {
    user(see Section 6.2.4, “Specifying Account Names”)| role (see Section 6.2.5, “Specifying Role Names”
}

用户或角色详情
用户见章节MySQL8.0官参 – 账户名详情 — 6.2.4 Specifying Account Names
角色见章节MySQL8.0官参 – 角色名详情 — 6.2.5 Specifying Role Names

撤销用户的角色,撤销角色的角色,撤销用户的用户,撤销角色的用户, 用

REVOKE...FROM...

REVOKE 用户1,角色1FROM 用户2,角色2;

REVOKEIFEXISTS 用户1,角色1FROM 用户2,角色2IGNORE UNKNOWN USER;

DROPUSERIFEXISTS u1,u2,u3,u4,u5,u6,r1,r2,r3,r4,r5,r6;CREATEUSERifnotexists u1,u2,u3,u4,u5,u6;CREATE ROLE IFNOTEXISTS r1,r2,r3,r4,r5,r6;GRANT u1,u2,u3,r1,r2,r3 TO u4,u5,u6,r4,r5,r6;SHOW GRANTS FOR u6;SHOW GRANTS FOR r6;REVOKE u1,u2,u3,r1,r2,r3 FROM u4,u5,u6,r4,r5,r6;SHOW GRANTS FOR u6;SHOW GRANTS FOR r6;DROP ROLE IFEXISTS u1,u2,u3,u4,u5,u6,r1,r2,r3,r4,r5,r6;

删除角色, 既可以用

DROP ROLE

,也可以用

DROP USER

MySQL8.0官参 --删除角色— 13.7.1.4 DROP ROLE Statement

删除角色: DROP ROLE 的语法

DROP ROLE [IFEXISTS] role [, role ]...

试验 drop user 和 drop role 通用

#试验 drop user 和 drop role 通用CREATEUSERIFNOTEXISTS u1,u2,u3,u4,u5,u6,u7,u8,u9;CREATE ROLE IFNOTEXISTS r1,r2,r3,r4,r5,r6,r7,r8,r9;SELECTuser 用户,host 主机,plugin 密码插件 FROM mysql.user;DROPUSERIFEXISTS u1,r2,u3,r4,u5,r6,u7,r8,u9;SELECTuser 用户,host 主机,plugin 密码插件 FROM mysql.user;DROP ROLE IFEXISTS r1,u2,r3,u4,r5,u6,r7,u8,r9;SELECTuser 用户,host 主机,plugin 密码插件 FROM mysql.user;

文章目录

知识扩展

查看msql版本

SELECT@@VERSION

,

show variables like 'version';

SELECT @@VERSION;

没有空格也可以, SELECT@@VERSION; select@@version; sEleCt@@vErSIoN; 都行

sEleCt@@vErSIoN;

SHOW VARIABLES LIKE'version';SHOWGLOBAL VARIABLES LIKE'VERSION';

查看当前用户是谁

select user();

或者

select current_user();

, 查看当前角色

select current_role();

selectuser();

mysql>SELECTuser();+----------------+|user()|+----------------+| root@localhost|+----------------+1rowinset(0.00 sec)

selectcurrent_user();

mysql>selectcurrent_user();+----------------+|current_user()|+----------------+| root@localhost|+----------------+1rowinset(0.00 sec)

SELECT current_role();

查看设置默认的密码加密插件

default_authentication_plugin

通过配置文件设置默认的密码插件

MySQL8.0官方文档—使用配置文件 — 4.2.2.2 Using Option Files
配置文件的位置

• Windows 在MySQL安装目录,手工创建 my.inf或my.cnf``````%WINDIR%\my.ini , %WINDIR%\my.cnf``````C:\my.ini , C:\my.cnf``````BASEDIR\my.ini , BASEDIR\my.cnf
• Linux 在 /etc/my.cnf , /etc/mysql/my.cnf , SYSCONFDIR/my.cnf``````/etc/mysql/mysql.conf.d/mysql.conf``````/etc/mysql/mysql.conf.d/mysqld.conf

要放在

[mysqld]

下面

[mysqld]
default_authentication_plugin=mysql_native_password
default_authentication_plugin = 'mysql_native_password'
default_authentication_plugin = "mysql_native_password"

[mysqld]
default_authentication_plugin=caching_sha2_password
default_authentication_plugin = 'caching_sha2_password'
default_authentication_plugin = "caching_sha2_password"

解释配置文件中的

[mysqld]

,

[mysql]

,

[client]

• [mysqld] : 数据库的设置；
• [mysql] : 控制台的客户端, 那个使用mysql -u -p 命令登录的客户端 的设置；
• [client] : 客户端的设置内容；

default_authentication_plugin

是只读的, 没法通过

SET GLOBAL

或

SET PERSIST

来设置

查看默认的默认的密码认证插件, 查看 default_authentication_plugin 系统变量

show variables like'%default_authentication_plugin%';

SHOW variables LIKE'default_authentication_plugin';

SELECT @@default_authentication_plugin;

查看权限列表

SHOW privileges;

SHOWprivileges;

mysql>SHOWprivileges;+------------------------------+---------------------------------------+-------------------------------------------------------+| Privilege                    | Context                               |Comment|+------------------------------+---------------------------------------+-------------------------------------------------------+|Alter|Tables|Toalter the table||Alterroutine| Functions,Procedures                  |Toalterordrop stored functions/procedures          ||Create|Databases,Tables,Indexes              |Tocreate new databasesandtables||Createroutine|Databases|TouseCREATEFUNCTION/PROCEDURE||Create role                  | Server Admin                          |Tocreate new roles                                   ||Createtemporarytables|Databases|TouseCREATETEMPORARYTABLE||Createview|Tables|Tocreate new views                                   ||Createuser| Server Admin                          |Tocreate new users                                   ||Delete|Tables|Todelete existing rows||Drop|Databases,Tables|Todropdatabases,tables,and views                  ||Drop role                    | Server Admin                          |Todrop roles                                         || Event                        | Server Admin                          |Tocreate,alter,dropandexecute events             ||Execute| Functions,Procedures                  |Toexecute stored routines                            ||File|File access on server                 |Toreadandwrite files on the server                 ||Grantoption|Databases,Tables,Functions,Procedures |To give to other users those privileges you possess   ||Index|Tables|Tocreateordrop indexes                             ||Insert|Tables|Toinsertdataintotables||Locktables|Databases|TouseLOCKTABLES(together withSELECT privilege)|| Process                      | Server Admin                          |Toview the plain textof currently executing queries || Proxy                        | Server Admin                          |To make proxy user possible                           ||References|Databases,Tables|To have referencesontables|| Reload                       | Server Admin                          |To reload or refresh tables, logs andprivileges||Replication client           | Server Admin                          |To ask where the slave or master servers are          ||Replication slave            | Server Admin                          |Toreadbinary log events from the master             ||Select|Tables|To retrieve rowsfromtable||Showdatabases| Server Admin                          |To see alldatabaseswithSHOWDATABASES||Showview|Tables|To see views withSHOWCREATEVIEW||Shutdown| Server Admin                          |To shut down the server                               || Super                        | Server Admin                          |TouseKILL thread,SETGLOBAL, CHANGE MASTER, etc.||Trigger|Tables|Touse triggers                                       ||Createtablespace| Server Admin                          |Tocreate/alter/drop tablespaces                      ||Update|Tables|Toupdate existing rows||Usage| Server Admin                          |Noprivileges- allow connect only                    || FIREWALL_EXEMPT              | Server Admin                          ||| AUDIT_ABORT_EXEMPT           | Server Admin                          ||| XA_RECOVER_ADMIN             | Server Admin                          ||| TABLE_ENCRYPTION_ADMIN       | Server Admin                          ||| SYSTEM_VARIABLES_ADMIN       | Server Admin                          ||| FLUSH_STATUS                 | Server Admin                          ||| CONNECTION_ADMIN             | Server Admin                          ||| ENCRYPTION_KEY_ADMIN         | Server Admin                          ||| INNODB_REDO_LOG_ARCHIVE      | Server Admin                          ||| CLONE_ADMIN                  | Server Admin                          ||| BINLOG_ENCRYPTION_ADMIN      | Server Admin                          ||| FLUSH_TABLES                 | Server Admin                          ||| BACKUP_ADMIN                 | Server Admin                          ||| AUTHENTICATION_POLICY_ADMIN  | Server Admin                          ||| REPLICATION_APPLIER          | Server Admin                          ||| GROUP_REPLICATION_STREAM     | Server Admin                          ||| REPLICATION_SLAVE_ADMIN      | Server Admin                          ||| FLUSH_OPTIMIZER_COSTS        | Server Admin                          ||| SESSION_VARIABLES_ADMIN      | Server Admin                          ||| APPLICATION_PASSWORD_ADMIN   | Server Admin                          |||SYSTEM_USER| Server Admin                          ||| RESOURCE_GROUP_ADMIN         | Server Admin                          ||| AUDIT_ADMIN                  | Server Admin                          ||| FLUSH_USER_RESOURCES         | Server Admin                          ||| GROUP_REPLICATION_ADMIN      | Server Admin                          ||| INNODB_REDO_LOG_ENABLE       | Server Admin                          ||| PASSWORDLESS_USER_ADMIN      | Server Admin                          ||| ROLE_ADMIN                   | Server Admin                          ||| BINLOG_ADMIN                 | Server Admin                          ||| PERSIST_RO_VARIABLES_ADMIN   | Server Admin                          ||| RESOURCE_GROUP_USER          | Server Admin                          ||| SENSITIVE_VARIABLES_OBSERVER | Server Admin                          ||| SERVICE_CONNECTION_ADMIN     | Server Admin                          ||| SHOW_ROUTINE                 | Server Admin                          ||| SET_USER_ID                  | Server Admin                          ||+------------------------------+---------------------------------------+-------------------------------------------------------+68rowsinset(0.00 sec)

查看用户权限

SHOW GRANTS;

和

SHOW GRANTS FOR 'u'@'h';

USAGE

表示没有权限

查看当前用户的权限, 查看自己的权限

SHOW grants;

查看 root 有哪些权限

SHOW GRANTS FOR root@'localhost';

mysql>show grants for root@'localhost';+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost|+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP, RELOAD,SHUTDOWN, PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOWDATABASES, SUPER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,CREATEUSER, EVENT,TRIGGER,CREATETABLESPACE,CREATE ROLE,DROP ROLE ON*.*TO`root`@`localhost`WITHGRANTOPTION||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`root`@`localhost`WITHGRANTOPTION||GRANT PROXY ON``@`` TO `root`@`localhost`WITHGRANTOPTION|+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+3rowsinset(0.00 sec)

MySQL的一些

SHOW

命令语句

MySQL8.0官参–SHOW语句—13.7.7 SHOW Statements

SHOW STATUS

︰列出 MySQL 的状态。

SHOW VARIABLES

︰列出 MySQL 系统变量值。

SHOW VARIABLES LIKE '%XXX%'

︰筛选 MySQL 系统变量值,

SHOW DATABASES;

或者

SHOW SCHEMAS;

︰列出数据库。 schema=database

SHOW TABLES

︰列出当前数据库中的数据表。

SHOW TABLES FROM 数据库名

︰列出指定数据库中的数据表。

SHOW TABLE STATUS FROM 数据库名

︰列出数据库中的数据表并且提供详细信息。
SHOW COLUMNS FROM tbl_name [FROM db_name]︰列出数据表的列清单]。
SHOW FULL COLUMNS FROM tbl_name [FROM db_name]︰列出数据表的列清单，并且提供详细数据，同 SHOW FULL FIELDS FROM tbl_name [FROM db_name]。
SHOW INDEX FROM tbl_name [FROM db_name]︰列出数据表中的索引。
SHOW PROCESSLIST︰列出那个任务正在执行。

GRANT 的官方文档说明 版本8.0

MySQL8.0官参—授权语句—13.7.1.6 GRANT Statement

GRANT Statement 语句语法

GRANT
    priv_type [(column_list)][, priv_type [(column_list)]]...ON[object_type] priv_level
    TO user_or_role [, user_or_role]...[WITHGRANTOPTION][ASuser[WITH ROLE
            DEFAULT| NONE
          |ALL|ALLEXCEPT role [, role ]...| role [, role ]...]]
}

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role]...[WITHGRANTOPTION]GRANT role [, role]...TO user_or_role [, user_or_role]...[WITH ADMIN OPTION]

object_type: {
    TABLE|FUNCTION|PROCEDURE
}

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

user_or_role: {
    user(see Section 6.2.4, “Specifying Account Names”)| role (see Section 6.2.5, “Specifying Role Names”)
}

用户或角色详情
用户见章节MySQL8.0官参 – 账户名详情 — 6.2.4 Specifying Account Names
角色见章节MySQL8.0官参 – 角色名详情 — 6.2.5 Specifying Role Names

GRANT 概述
该GRANT语句使系统管理员能够授予权限和角色，这些权限和角色可以授予用户帐户和角色。这些语法限制适用于：

• GRANT不能在同一语句中混合授予权限和角色。给定的 GRANT语句必须授予权限或角色。
• 该ON子句区分语句是否授予权限或角色： - 使用ON，该语句授予权限。- 如果没有ON，则该语句授予角色。- 允许将权限和角色分配给一个帐户，但您必须分别使用单独的 GRANT语句，每个语句的语法都适合要授予的内容。

要用 GRANT 授权，您必须拥有这些

GRANT OPTION

的权限，并且您必须拥有您正在授予的权限。（或者，如果您对mysql system schema 系统架构(系统数据库) 的 grant table(权限表) 具有 UPDATE 权限，则可以授予任何帐户任何权限。）当系统变量

read_only

启用时，GRANT 另外还需要

CONNECTION_ADMIN

权限（或已弃用的 SUPER 权限）。

GRANT要么对所有命名用户和角色成功，要么回滚，如果发生任何错误则无效。仅当对所有命名用户和角色都成功时，该语句才会写入二进制日志。

REVOKE语句

对应

GRANT语句

, 并允许管理员删除帐户权限。请参阅 第 13.7.1.8 节，“REVOKE 语句”。

每个帐户名称都使用6.2.4 节“指定帐户名称” 中描述的格式。每个角色名称使用第 6.2.5 节，“指定角色名称”中描述的格式。例如：

GRANTALLON db1.*TO'jeffrey'@'localhost';GRANT'role1','role2'TO'user1'@'localhost','user2'@'localhost';GRANTSELECTON world.*TO'role3';

帐户或角色名称的主机名部分，如果省略，则默认为

'%'

.

通常，数据库管理员首先使用

CREATE USER

创建一个帐户并定义其非特权特征，例如密码、是否使用安全连接以及对服务器资源的访问限制，然后再用 GRANT定义其权限。

ALTER USER

可用于更改现有帐户的非特权特性。例如：

CREATEUSER'jeffrey'@'localhost' IDENTIFIED BY'password';GRANTALLON db1.*TO'jeffrey'@'localhost';GRANTSELECTON db2.invoice TO'jeffrey'@'localhost';ALTERUSER'jeffrey'@'localhost'WITH MAX_QUERIES_PER_HOUR 90;

从mysql程序中， 成功执行时GRANT会响应 Query OK, 0 rows affected。要确定该操作会产生哪些特权，请使用

SHOW GRANTS

. 请参阅 第 13.7.7.21 节，“SHOW GRANTS 语句”。

重要的

在某些情况下， GRANT可能会记录在服务器日志中或客户端的历史文件中，
例如> ~/.mysql_history，
这意味着任何有权读取该信息的人都可以读取明文密码。有关服务器日志发生这种情况的条件以及如何控制它的信息，请参阅第 6.1.2.3 节，“密码和日志记录”。有关客户端日志记录的类似信息，请参阅 第 4.5.1.3 节，“mysql 客户端日志记录”。

GRANT支持最长 255 个字符的主机名（在 MySQL 8.0.17 之前为 60 个字符）。用户名最多可包含 32个字符。数据库、表、列和routine名称最多可包含 64 个字符。

警告

不要试图通过更改

mysql.user

系统表来更改用户名的允许长度。这样做会导致不可预知的行为，甚至可能使用户无法登录 MySQL
服务器。切勿以任何方式更改mysql系统模式中的表结构，除非通过 第 2.11 节 “Upgrading MySQL升级 MySQL”中描述的过程。

对象引用准则
语句中的几个对象需要被

GRANT语句 

引用，尽管在许多情况下引用是可选的：帐户、角色、数据库、表、列和例程名称。例如，如果帐户名称中的 user_name 或者 host_name 值不带引号是合法的，则无需引用它。但是，如果包含特殊字符, 例如 user_name 包含减号

%

, host_name包含百分号

%

，则必须使用引号写成这样:

'test-user'@'%.com'

。分别引用用户名和主机名。

需要明确指明该值为引用的情况(要加给值加引号的情况)：

• 引用 database, table, column, and routine 的名称作为标识符的时候。
• 引用用户名和主机名作为标识符或字符串。
• 将密码引用为字符串。

有关字符串引用和标识符引用指南，请参阅 第 9.1.1 节，“字符串文字” 和 第 9.2 节，“模式对象名称”。

在GRANT语句中,按数据库级别授权,指定数据库名称时，允许使用下划线

_

和百分号

%

通配符 (GRANT … ON db_name.* )。这意味着，例如，想要将下划线

_

字符用作数据库名称的一部分，请使用转义字符反斜杠

\

进行转义, 将下划线

_

写为反斜杠下划线

\_

,再写入GRANT语句中 （例如， GRANT … ON

foo\_bar

.* TO …)）。

在权限分配中，MySQL 在以下情况下将数据库名称中出现的未转义字符

_

和

%

SQL 通配符解释为文字字符：

当数据库名称不用于在数据库级别授予权限时，而是用作向某些其他对象（例如表或例程）授予权限的限定符（例如，GRANT … ON db_name.tbl_name）。

启用partial_revokes 会导致 MySQL 将数据库名称中的未转义字符_ 和%通配符解释为文字字符，就像它们被转义为

\_

和

\%

一样。因为这改变了 MySQL 解释权限的方式，所以建议在 partial_revokes可能启用的安装中避免在权限分配中使用未转义的通配符。有关详细信息，请参阅 第 6.2.12 节，“使用部分撤销的权限限制” Privilege Restriction Using Partial Revokes。

帐户名称
GRANT语句中的 MySQL 帐户表明一个user值。为了适应从任意主机向用户授予权限，MySQL 支持在表单 ‘user_name’@'host_name’中指定user的值 。

您可以在主机名中使用通配符。例如， ‘user_name’@‘%.example.com’ 适用用于 user_name 到 example.com 域名中的任意主机, 还有, ‘user_name’@‘198.51.100.%’ 适用于user_name对于198.51.100这个C类子网 中的任何主机。’

简单形式 ‘user_name’ 是 ‘user_name’@‘%’ 的同义词 。(@'%'可以省略)

MySQL 不支持用户名中的通配符。要引用匿名用户，请使用以下GRANT语句 指定具有空用户名的帐户

GRANTALLON test.*TO''@'localhost'...;

在这种情况下，使用匿名用户的正确密码从本地主机连接的任何用户都被允许访问，并具有与匿名用户帐户关联的权限。

有关帐户名中的用户名和主机名值的其他信息，请参阅第 6.2.4 节，“指定帐户名”。

警告
如果您允许本地匿名用户连接到 MySQL 服务器，您还应该将权限授予所有本地用户’user_name’@‘localhost’，如 . 否则，当命名用户尝试从本地机器t登录 MySQL 服务器时，将使用系统表mysql.user中 的匿名@localhost用户帐户 。有关详细信息，请参阅 第 6.2.6 节，“访问控制，第 1 阶段：连接验证”。
命名的本机用户例如: hello@localhost
匿名的本机用户例如: ‘’@localhost
要确定此问题是否适用于您，请执行以下查询，其中列出了所有匿名用户：

SELECT Host,UserFROM mysql.userWHEREUser='';

为避免刚刚描述的问题，请使用以下语句删除本地匿名用户帐户：

DROPUSER''@'localhost';

MySQL 支持的权限 privileges

表 13.11 GRANT 和 REVOKE 的允许静态权限
权限含义和可授予级别ALL [PRIVILEGES]授予 GRANT OPTION 和 PROXY 之外的 指定访问级别的 所有权限 。ALTER启用使用ALTER TABLE. 级别：全局、数据库、表。ALTER ROUTINE允许更改或删除存储的例程。级别：全局、数据库、常规。CREATE启用数据库和表创建。级别：全局、数据库、表。CREATE ROLE启用角色创建。级别：全局。CREATE ROUTINE启用存储的例程创建。级别：全局，数据库。CREATE TABLESPACE允许创建、更改或删除表空间和日志文件组。级别：全局。CREATE TEMPORARY TABLES启用使用CREATE TEMPORARY TABLE. 级别：全局，数据库。CREATE USER允许使用CREATE USER、 DROP USER、 RENAME USER和 REVOKE ALL PRIVILEGES。级别：全局。CREATE VIEW允许创建或更改视图。级别：全局、数据库、表。DELETE启用使用DELETE. 级别：全局、数据库、表。DROP启用要删除的数据库、表和视图。级别：全局、数据库、表。DROP ROLE启用要删除的角色。级别：全局。EVENT为 Event Scheduler 启用事件。级别：全局，数据库。EXECUTE使用户能够执行存储的例程。级别：全局、数据库、常规。FILE使用户能够使服务器读取或写入文件。级别：全局。GRANT OPTION允许向其他帐户授予或删除权限。级别：全局、数据库、表、例程、代理。INDEX允许创建或删除索引。级别：全局、数据库、表。INSERT启用使用INSERT. 级别：全局、数据库、表、列。LOCK TABLESLOCK TABLES在您有SELECT 权限的表上启用使用。级别：全局，数据库。PROCESS使用户能够使用 . 查看所有进程SHOW PROCESSLIST。级别：全局。PROXY启用用户代理。级别：从用户到用户。REFERENCES启用外键创建。级别：全局、数据库、表、列。RELOAD启用FLUSH操作的使用。级别：全局。REPLICATION CLIENT使用户能够询问源服务器或副本服务器在哪里。级别：全局。REPLICATION SLAVE使副本能够从源读取二进制日志事件。级别：全局。SELECT启用使用SELECT. 级别：全局、数据库、表、列。SHOW DATABASES启用SHOW DATABASES以显示所有数据库。级别：全局。SHOW VIEW启用使用SHOW CREATE VIEW. 级别：全局、数据库、表。SHUTDOWN启用使用mysqladmin shutdown。级别：全局。SUPER启用其他管理操作，例如 CHANGE REPLICATION SOURCE TO、CHANGE MASTER TO、KILL、 PURGE BINARY LOGS、 SET GLOBAL和mysqladmin 调试命令。级别：全局。TRIGGER启用触发操作。级别：全局、数据库、表。UPDATE启用使用UPDATE. 级别：全局、数据库、表、列。USAGE“没有权限”的同义词
表 13.12 GRANT 和 REVOKE 允许的动态权限
权限意义和可授予级别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启用InnoDB密钥轮换。级别：全局。FIREWALL_ADMIN启用防火墙规则管理，任何用户。级别：全局。FIREWALL_EXEMPT免除用户的防火墙限制。级别：全局。FIREWALL_USER启用防火墙规则管理，self。级别：全局。FLUSH_OPTIMIZER_COSTS启用优化器成本重新加载。级别：全局。FLUSH_STATUS启用状态指示器刷新。级别：全局。FLUSH_TABLES启用表刷新。级别：全局。FLUSH_USER_RESOURCES启用用户资源刷新。级别：全局。GROUP_REPLICATION_ADMIN启用组复制控制。级别：全局。INNODB_REDO_LOG_ENABLE启用或禁用重做日志记录。级别：全局。INNODB_REDO_LOG_ARCHIVE启用重做日志归档管理。级别：全局。NDB_STORED_USER启用 SQL 节点（NDB 集群）之间的用户或角色共享。级别：全局。PASSWORDLESS_USER_ADMIN启用无密码用户帐户管理。级别：全局。PERSIST_RO_VARIABLES_ADMIN启用持久化只读系统变量。级别：全局。REPLICATION_APPLIER充当PRIVILEGE_CHECKS_USER复制通道。级别：全局。REPLICATION_SLAVE_ADMIN启用定期复制控制。级别：全局。RESOURCE_GROUP_ADMIN启用资源组管理。级别：全局。RESOURCE_GROUP_USER启用资源组管理。级别：全局。ROLE_ADMIN允许角色被授予或撤销，使用WITH ADMIN OPTION. 级别：全局。SESSION_VARIABLES_ADMIN启用设置受限会话系统变量。级别：全局。SET_USER_ID启用设置非自身DEFINER值。级别：全局。SHOW_ROUTINE启用对存储的例程定义的访问。级别：全局。SKIP_QUERY_REWRITE不要重写此用户执行的查询。级别：全局。SYSTEM_USER指定账户为系统账户。级别：全局。SYSTEM_VARIABLES_ADMIN启用修改或持久化全局系统变量。级别：全局。TABLE_ENCRYPTION_ADMIN启用覆盖默认加密设置。级别：全局。VERSION_TOKEN_ADMIN启用版本令牌功能。级别：全局。XA_RECOVER_ADMIN启用XA RECOVER执行。级别：全局。

MySQL配置文件的位置

MySQL8.0官方文档–使用配置文件—4.2.2.2 Using Option Files

Windows下MySQL配置文件的位置

Option Files Read on Windows Systems
File NamePurpose%WINDIR%\my.ini, %WINDIR%\my.cnfGlobal optionsC:\my.ini, C:\my.cnfGlobal optionsBASEDIR\my.ini, BASEDIR\my.cnfGlobal optionsdefaults-extra-fileThe file specified with --defaults-extra-file, if any%APPDATA%\MySQL.mylogin.cnfLogin path options (clients only)DATADIR\mysqld-auto.cnfSystem variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)
这个配置文件的优先级大于传统配置文件, 内容是JSON格式,
与表格

performance_schema.persisted_variables

联动
可用

SELECT * FROM performance_schema.persisted_variables; 

查看 ;

Linux Unix下MySQL配置文件的位置

Option Files Read on Unix and Unix-Like Systems
File NamePurpose/etc/my.cnfGlobal options/etc/mysql/my.cnfGlobal optionsSYSCONFDIR/my.cnfGlobal options$MYSQL_HOME/my.cnfServer-specific options (server only)defaults-extra-fileThe file specified with --defaults-extra-file, if any/.my.cnfUser-specific options/.mylogin.cnfUser-specific login path options (clients only)DATADIR/mysqld-auto.cnfSystem variables persisted with

SET PERSIST

or

SET PERSIST_ONLY 

(server only)
这个配置文件的优先级大于传统配置文件, 内容是JSON格式,
与表格

performance_schema.persisted_variables

联动
可用

SELECT * FROM performance_schema.persisted_variables; 

查看 ;
Ubuntu20.04的MySQL8.0.30 , 在/etc/mysql目录下有,my.cnf和mysql.cnf, 其实是一个文件, my.cnf是一个符号链接(软链接),指向另一个符号链接/etc/alternatives/my.cnf, 而/etc/alternatives/my.cnf指向/etc/mysql/mysql.cnf