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

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

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION

这样的语句在MySQL8.0中行不通, 必须 创设和授权 分步执行

CREATE USER u@'%' IDENTIFIED BY '密';-- 创建用户并指定密码GRANT ALL PRIVILEGES ON *.* TO u@'%' WITH GRANT OPTION;--授权

也可以分三步

CREATE USER u@'%' ;-- 创建用户ALTERUSER u@'%' IDENTIFIED BY '密';-- 指定密码GRANT ALL PRIVILEGES ON *.* TO u@'%' WITH GRANT OPTION;-- 授权

刷新权限设置

FLUSH PRIVILEGES;

创建远程root的语句模板

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

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '密码'; GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

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

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '密码'; GRANT root@'localhost' TO 'root'@'%'; SET DEFAULT ROLE root@'localhost' TO 'root'@'%'; FLUSH PRIVILEGES;

权限和角色两者都加持

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '密码'; GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION; GRANT root@'localhost' TO 'root'@'%'; SET DEFAULT ROLE root@'localhost' TO 'root'@'%'; FLUSH PRIVILEGES;

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

文章目录

  • MySQL8 创建用户,设置修改密码,授权
      • 创建远程root的语句模板
      • MySQL账户由用户名@主机组成
      • 创建用户,设置密码,修改密码
        • 创建用户 `CREATE USER` , `create user`
        • 创建用户并指定密码
          • 使用默认加密方式,创建用户并指定密码 和 修改密码
          • 使用指定密码插件, 创建用户并指定密码 和 修改密码
            • 查看 用户,主机,密码插件
            • `show plugins`可以列出mysql的插件
            • 使用 `WITH mysql_native_password` 密码插件,创建用户并指定密码 和 修改密码
            • 使用 `WITH caching_sha2_password` 密码插件,创建用户并指定密码 和 修改密码
            • 查看当前的默认密码加密认证插件, 查看系统全局变量`default_authentication_plugin`
            • 设置默认的密码加密认证插件, 设置系统全局变量`default_authentication_plugin`
        • 修改密码
          • 用 `ALTER USER` 修改自己或别人的密码
          • 用 `SET PASSWORD=` 设置修改自己的密码
          • 用 `SET PASSWORD FOR` 修改自己或其他账户的密码, `=`等号分割账户和密码
        • 密码管理, 密码策略
          • 内部与外部凭证存储
          • 密码过期策略
            • 设置某用户的密码立刻过期
          • 需要密码验证的策略
            • 双密码支持
            • 随机密码生成
            • 登录失败跟踪和临时帐户锁定
        • 重命名用户或角色 `RENAME USER`
      • 授权 , 允许权力继续传递 , 撤权
        • 授权语法: `GRANT 权限以逗号分隔 ON 库.表 TO 账户以逗号分隔 [WITH GRANT OPTION]`
          • 将所有库的所有表的所有权限,授权给
          • 允许权力传递 `WITH GRANT OPTION`
          • 用户和用户,角色和角色,用户和角色,角色和用户,可以相互授权, 但要避免权力循环
        • 撤销授权
          • 撤销所有授权
            • 撤销远程root的全部权限
          • 撤销指定权限
          • GRANT 和 REVOKE 语法对比
          • REVOKE语句可以加 `IF EXISTS` 和 `IGNORE UNKNOWN USER `
        • 超级用户创建并授权超级用户
      • 删除用户 `DROP USER ‘u’@’h’`
      • 创建用户,授权,删除用户的官方例子
      • 角色管理
        • 用 `CREATE ROLE` 创建的角色也会出现在 `mysql.user` 表中, 只是被锁定
        • 创建角色 `CREATE ROLE` , `create role`
        • 授予角色权限的语法和授予用户权限的语法一样
        • 激活角色
          • 查看用户的角色 , 或角色的角色
          • `SET ROLE` , 临时激活角色
            • `SET ROLE ALL` 临时激活所有被grant授予的角色
            • `SET ROLE DEFAULT` 临时激活默认的角色
            • `SET ROLE NONE` 取消激活的角色
            • 设置默认角色`SET DEFAULT ROLE` ,
          • 设置全局变量 `activate_all_roles_on_login` , `ON`启用, 默认`OFF`停用 , 用户登陆时激活所有GRANT语句授予的角色
            • 在配置文件设置设置 `activate_all_roles_on_login` 每次重启都有效
            • 用 `SET GLOBAL` 命令设置 `activate_all_roles_on_login` 重启后失效
            • 用 `SET PERSIST` 命令设置 `activate_all_roles_on_login` 重启后有效,且优先级大于传统配置文件
        • 撤销角色权限的语法和撤销用户权限的语法一样, 用`REVOKE…FROM…`
        • 撤销用户的角色,撤销角色的角色,撤销用户的用户,撤销角色的用户, 用`REVOKE…FROM…`
        • 删除角色, 既可以用`DROP ROLE`,也可以用`DROP USER`
      • 知识扩展
          • 查看msql版本 `SELECT@@VERSION` , `show variables like ‘version’;`
          • 查看当前用户是谁 `select user();` 或者 `select current_user();` , 查看当前角色 `select current_role();`
        • 查看设置默认的密码加密插件 `default_authentication_plugin`
          • 通过配置文件设置默认的密码插件
          • 查看默认的默认的密码认证插件, 查看 default_authentication_plugin 系统变量
        • 查看权限列表 `SHOW privileges;`
        • 查看用户权限 `SHOW GRANTS;` 和 `SHOW GRANTS FOR ‘u’@’h’;`
          • 查看当前用户的权限, 查看自己的权限
          • 查看 root 有哪些权限
        • MySQL的一些 `SHOW` 命令语句
        • GRANT 的官方文档说明 版本8.0
          • GRANT Statement 语句语法
          • MySQL 支持的权限 privileges
        • MySQL配置文件的位置
          • Windows下MySQL配置文件的位置
          • Linux Unix下MySQL配置文件的位置

官方文档-用户管理语句–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)CREATE USER 'username'@'host';# localhost表示本机CREATE USER 'username'@'localhost';# 百分号 % 表示任意主机CREATE USER 'username'@'%';# 百分号 % 可以省略 , 如果用户名没有空格,还可以省略引号CREATE USER username;

也可以使用 if not exits

CREATE USER IF NOT EXISTS `root`@`%`;
mysql> create user if not exists 张三;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> create user if not exists '张 三';Query OK, 0 rows affected (0.01 sec)mysql> create user if not exists '张-三';Query OK, 0 rows affected (0.01 sec)mysql> select user,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 |+------------------+-----------+-----------------------+

查看已有账户

SELECT user,host FROM mysql.user
SELECT user,host,plugin FROM mysql.user

创建用户并指定密码

设置密码的方法有

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

官方推荐在修改密码时用ALTER USER,而不是SET PASSWORD FOR

使用默认加密方式,创建用户并指定密码 和 修改密码
### 创建用户并指定密码CREATE USER 'username'@'%' IDENTIFIED BY 'password';### 修改密码 ALTER USER 'username'@'%' IDENTIFIED BY 'password';### 修改密码方法2SET PASSWORD FOR 'username'@'%'='password';

对比助记:

对比助记:创建用户并指定密码 :CREATEUSER'username'@'host'IDENTIFIED BY 'password';修改密码:ALTERUSER'username'@'host'IDENTIFIED BY 'password';删除用户 :DROPUSER'username'@'host'
使用指定密码插件, 创建用户并指定密码 和 修改密码
查看 用户,主机,密码插件
SELECT user , 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之前)的默认

### 创建用户并指定密码CREATE USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';### 修改密码 ALTER USER '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的默认

### 创建用户并指定密码CREATE USER 'username'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';### 修改密码 ALTER USER '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 修改自己或别人的密码
ALTER USER 'u'@'%' IDENTIFIED BY 'passwd'
# 使用MySQL5.7之前的默认密码插件ALTER USER 'u'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd'
# 使用MySQL5.7之前的SHA256密码加密插件, 不推荐, 已过时, 未来的MySQL版本可能删除ALTER USER 'u'@'%' IDENTIFIED WITH sha256_password BY 'passwd'
#使用MySQL8.0默认的带缓存的SHA256加密的密码插件ALTER USER '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 节，“身份验证插件”。

密码过期策略
设置某用户的密码立刻过期
ALTER USER '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=0default_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 天更改一次密码：
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

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

  • 禁用密码过期：
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

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

  • 遵循语句命名的所有帐户的全局过期策略：
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

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

  • 服务器检查密码是否已手动过期。

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

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

mysql> SELECT 1;ERROR 1820 (HY000): You must reset your password using ALTER USERstatement before executing this statement.mysql> ALTER USER USER() IDENTIFIED BY 'password';Query OK, 0 rows affected (0.01 sec)mysql> SELECT 1;+---+| 1 |+---+| 1 |+---+1 row in set (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 语句”。

  • 要求密码更改指定当前密码：

    CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

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

  • 不要求密码更改指定当前密码（当前密码可能但不需要给出）：

    CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;

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

  • 遵循语句命名的所有帐户的全局密码验证要求策略：

    CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

当用户使用 ALTER USER or SET PASSWORD 语句更改密码时，对当前密码的验证就会发挥作用。示例使用ALTER USER，它优于SET PASSWORD，但此处描述的原则对于两种语句都是相同的。
在密码更改语句中，一个REPLACE 子句指定要替换的当前密码。例子：

  • 更改当前用户的密码：

    ALTER USER USER() IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';

  • 更改指定用户的密码：

    ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';

  • 更改指定用户的身份验证插件和密码：

    ALTER USER '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’appuser1

    ALTER USER 'appuser1'@'host1.example.com'IDENTIFIED BY 'password_b'RETAIN CURRENT PASSWORD;

  2. 等待密码更改在整个系统中复制到所有副本。

  3. 修改使用该appuser1帐户的每个应用程序， 使其使用密码 ‘password_b’ 而不是 ‘password_a’ 连接到服务器 。

  4. 此时，不再需要二级密码。在每个不是副本的服务器上，丢弃辅助密码：

    ALTER USER '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> CREATE USER '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> ALTER USER '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 选项 可以为每个帐户配置所需的登录失败次数和锁定时间 。例子：

CREATE USER 'u1'@'localhost' IDENTIFIED BY 'password'FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3;ALTER USER '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

RENAME USER old_user TO new_user[, old_user TO new_user] ...
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';

用户和角色都能用RENAME USER改名 , 没有 RENAME ROLE 命令

DROP USER IF EXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;CREATE USER IF NOT EXISTS u1,u2;CREATE ROLE IF NOT EXISTS r1,r2;SELECT user,host,plugin FROM mysql.user;RENAME USER u1 TO u1@localhost , r1 TO r1@localhost ,u2 TO u3 , r2 TO r3;SELECT user,host,plugin FROM mysql.user;DROP ROLE IF EXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;SELECT user,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 , 表示所有权限
*.* 表示所有库下的所有表
执行该操作的用户必须具有这些权限

GRANT ALL ON *.* TO等同GRANT ALL PRIVILEGES ON *.* TO
GRANT ALL ON *.* TO 'user'@'%' ;FLUSH PRIVILEGES;

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

create user si@'%' identifity by 'si';grant select,insert on *.* to si@'%' ;flush privileges;

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

GRANT 用1 TO 用2; FLUSH PRIVILEGES;

将角色的权力传递给用户

GRANT 角色 TO 用户; FLUSH PRIVILEGES;

将用户的权力传递给角色

GRANT 用户 TO 角色; FLUSH PRIVILEGES;

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

GRANT 角色1 TO 角色2; FLUSH PRIVILEGES;
允许权力传递 WITH GRANT OPTION

赋权语句后接 WITH GRANT OPTION , 允许被授权的用户继续传递权力, 如果赋权者的权力被回收, 被赋权者的权力也会被回收

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

#超级用户授权给另一个超级用户GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;# 或者省略 PRIVILEGES ,效果一样 GRANT ALL ON *.* TO 'super'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

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

create user si@'%' identifity by 'si';grant select,insert on *.* to si@'%' with grant option;flush privileges;
用户和用户,角色和角色,用户和角色,角色和用户,可以相互授权, 但要避免权力循环
CREATE USER if not exists u1,u2,u3,user007;CREATE ROLE if not exists r1,r2,r3,role007;GRANT u1,u2,u3,r1,r2,r3 TO user007,role007;

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

CREATE USER '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

drop user if exists u1,u2,u3,r1,r2,r3;create user if not exists u1,u2,u3;create role if not exists 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> drop user if exists u1,u2,u3,r1,r2,r3;Query OK, 0 rows affected (0.01 sec)mysql> create user if not exists u1,u2,u3;Query OK, 0 rows affected (0.01 sec)mysql> create role if not exists r1,r2,r3;Query OK, 0 rows affected (0.00 sec)mysql> grant r1 to u1;Query OK, 0 rows affected (0.01 sec)mysql> grant u1 to r2;Query OK, 0 rows affected (0.00 sec)mysql> grant r2 to u2;Query OK, 0 rows affected (0.01 sec)mysql> grant u2 to r3;Query OK, 0 rows affected (0.00 sec)mysql> grant r3 to u3;Query OK, 0 rows 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

撤销授权

撤销所有授权
# 撤销全部权限REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户或角色1 , 用户2 , 角色3 , 用户4;

可加上 IF EXISTS , 有对应权限才执行撤销动作

# 撤销全部权限REVOKE IF EXISTS ALL PRIVILEGES, GRANT OPTION FROM 用户或角色 , 用户2 , 角色7 ;

ALL PRIVILEGES = ALL

# 撤销全部权限REVOKE IF EXISTS ALL , GRANT OPTION FROM 用户或角色 , 用户2 , 角色7 ;

可以后加 IGNORE UNKNOWN USER 忽略未知用户

# 撤销全部权限REVOKE IF EXISTS ALL , GRANT OPTION FROM 用户或角色 , 用户2 , 角色7 IGNORE UNKNOWN USER ;
撤销远程root的全部权限
# 撤销远程root的全部权限REVOKE IF EXISTS ALL , GRANT OPTION FROM root@'%' IGNORE UNKNOWN USER ;
# 撤销远程root的全部权限, root=root@'%' , 不会影响 root@localhostREVOKE IF EXISTS ALL , GRANT OPTION FROM root IGNORE UNKNOWN USER ;
撤销指定权限

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

REVOKE insert,update,delete ON *.* FROM 'user001'@'%';

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

REVOKE insert,update,delete ON db2.* FROM 'user002'@'localhost';

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

REVOKE insert,update,delete ON db3.t1,db4.t3 FROM user003@localhost;

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

REVOKE IF EXISTS update ON db3.t1,db4.t3 FROM u1,u2,u3@loalhost,u4 IGNORE UNKNOWN USER ;
GRANT 和 REVOKE 语法对比
GRANTinsert,update ON *.* TO 账户REVOKE insert,update ON *.* FROM 账户
GRANTALL ON *.* TO 账户REVOKE ALL ON *.* FROM 账户
REVOKE语句可以加 IF EXISTS 和 IGNORE UNKNOWN USER 
REVOKE IF EXISTS 权限1,权限2 ON *.* FROM 账户1,账户2,账户3 IGNORE UNKNOWN USER;

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

#超级用户授权给另一个超级用户CREATE USER IF NOT EXISTS 'super'@'%' IDENTIFIED BY 'super'; GRANT ALL ON *.* TO 'super'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

删除用户 DROP USER 'u'@'h'

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

DROP USER supper;
DROP USER IF EXISTS supper;
DROP USER 张三 , 李四@'%' , 王五@'192.168.1.3' ;
DROP USER IF EXISTS 张三,李四@'%',王五@'192.168.1.3' ;

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

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

CREATE USER 'finley'@'localhost'IDENTIFIED BY 'password';GRANT ALLON *.*TO 'finley'@'localhost'WITH GRANT OPTION;CREATE USER 'finley'@'%.example.com'IDENTIFIED BY 'password';GRANT ALLON *.*TO 'finley'@'%.example.com'WITH GRANT OPTION;CREATE USER 'admin'@'localhost'IDENTIFIED BY 'password';GRANT RELOAD,PROCESSON *.*TO 'admin'@'localhost';CREATE USER 'dummy'@'localhost';
CREATE USER 'custom'@'localhost'IDENTIFIED BY 'password';GRANT ALLON bankaccount.*TO 'custom'@'localhost';CREATE USER 'custom'@'host47.example.com'IDENTIFIED BY 'password';GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROPON expenses.*TO 'custom'@'host47.example.com';CREATE USER 'custom'@'%.example.com'IDENTIFIED BY 'password';GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROPON customer.addressesTO 'custom'@'%.example.com';

角色管理

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

用 CREATE ROLE 创建的角色也会出现在 mysql.user 表中, 只是被锁定

可以用select user,host from mysql.user查看到

CREATE ROLE IF NOT EXISTS role001@'localhost';CREATE ROLE IF NOT EXISTS role002@'%';CREATE ROLE IF NOT EXISTS role003;CREATE ROLE IF NOT EXISTS role168@'192.168.168.168';SELECT user,host,plugin FROM mysql.user;

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

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

用户和角色的授权语法基本一样
删除角色不单可用DROP ROLE , 还可用DROP USER
角色改名可以用 RENAME USER
用户和角色都能用RENAME USER改名 , 没有 RENAME ROLE 命令

DROP USER IF EXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;CREATE USER IF NOT EXISTS u1,u2;CREATE ROLE IF NOT EXISTS r1,r2;SELECT user,host,plugin FROM mysql.user;RENAME USER u1 TO u1@localhost , r1 TO r1@localhost ,u2 TO u3 , r2 TO r3;SELECT user,host,plugin FROM mysql.user;DROP ROLE IF EXISTS u1,u1@localhost,u2,u3,r1,r1@localhost,r2,r3;SELECT user,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 [IF NOT EXISTS] 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 IF NOT EXISTS 角色1,角色2,角色3,role4,role5,role6;

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

MySQL8.0官参—13.7.1.6 GRANT Statement

GRANT SELECT,INSERT ON *.* TO role001;GRANT SELECT,INSERT ON schema3.table2 TO role001 WITH GRANT OPTION;GRANT ALL ON *.* TO role001;GRANT ALL ON database5.table1 TO role001 WITH GRANT OPTION;GRANT 角色1 TO 角色3GRANT 用户3,角色2,角色1 TO 角色2,用户5,角色7# 不能 GRANT 自己 TO 自己 会产生循环

试验1

# 试验CREATE ROLE if not exists u1,u2,u3,user007,r1,r2,r3,role007;CREATE USER if not exists u1,u2,u3,user007;CREATE ROLE if not exists 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

drop user if exists u1,u2,u3,r1,r2,r3;create user if not exists u1,u2,u3;create role if not exists 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> create user admin007;Query OK, 0 rows affected (0.01 sec)# 已创建 admin001,admin002,admin003,admin004,admin005,admin006mysql> GRANTroot,admin001,admin002,admin003,admin004,admin005,admin006 TO admin007;Query OK, 0 rows affected (0.01 sec)mysql> SET DEFAULT ROLE admin005,admin006 TO admin007;Query OK, 0 rows affected (0.01 sec)
  1. 登录被授权的用户
  2. 被授权用户通过select current_role可看到, 当前激活角色是默认角色, set role all后是所有角色, 再set role default后,又回到默认角色
mysql> exitByeadmin@VM-12-12-ubuntu:~$ mysql -u admin007Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 97Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select current_role();+-------------------------------+| current_role()|+-------------------------------+| `admin005`@`%`,`admin006`@`%` |+-------------------------------+1 row in set (0.00 sec)mysql> SET ROLE ALL;Query OK, 0 rows affected (0.00 sec)mysql> SELECT CURRENT_ROLE();+------------------------------------------------------------------------------------------------------+| CURRENT_ROLE() |+------------------------------------------------------------------------------------------------------+| `admin001`@`%`,`admin002`@`%`,`admin003`@`%`,`admin004`@`%`,`admin005`@`%`,`admin006`@`%`,`root`@`%` |+------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> SET ROLE DEFAULT;Query OK, 0 rows affected (0.00 sec)mysql> SELECT current_role();+-------------------------------+| current_role()|+-------------------------------+| `admin005`@`%`,`admin006`@`%` |+-------------------------------+1 row in set (0.00 sec)mysql>

将用户所有grant语句授予的角色设为默认角色 , SET DEFAULT ROLE ALL TO

SET DEFAULT ROLE ALL TO u1,u2,r1,r2,u3@localhost,r3@'127.0.0.1';

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

DROP USER IF EXISTS u1,u2,u3,r1,r2,r3,user100,role100;CREATE USER u1,u2,u3,user100;CREATE ROLE r1,r2,r3,role100;GRANT u1,u2,r1,r2 TO user100,role100;SET DEFAULT ROLE u1,u2,r1,r2 TO user100,role100;#这句能执行成功SET DEFAULT ROLE u3,r3 TO user100,role100;#这句会执行失败, 因为u3和r3未被GRANTDROP ROLE IF EXISTS 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语句授予的角色 重启后会失效SET GLOBALactivate_all_roles_on_login='ON';
SHOW VARIABLES LIKE '%activate_all_roles_on_login%';SET GLOBALactivate_all_roles_on_login='OFF';SHOW VARIABLES LIKE 'activate_all_roles_on_login';SET GLOBALactivate_all_roles_on_login='ON';SHOW VARIABLES LIKE '%activate_all_roles_on_login';SET GLOBALactivate_all_roles_on_login="OFF";SHOW VARIABLES LIKE 'activate_all_roles_on_login%';SET GLOBALactivate_all_roles_on_login=ON;SHOW VARIABLES LIKE 'activate_all_roles_on_login';
用 SET PERSIST 命令设置 activate_all_roles_on_login 重启后有效,且优先级大于传统配置文件
#用户登录时,激活所有GRANT语句授予的角色 , 用 SET PERSIST 重启后有效,且优先级大于传统配置文件SET PERSISTactivate_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 [IF EXISTS]priv_type [(column_list)][, priv_type [(column_list)]] ...ON [object_type] priv_levelFROM user_or_role [, user_or_role] ...[IGNORE UNKNOWN USER]REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTIONFROM user_or_role [, user_or_role] ...[IGNORE UNKNOWN USER]REVOKE [IF EXISTS] PROXY ON user_or_roleFROM user_or_role [, user_or_role] ...[IGNORE UNKNOWN USER]REVOKE [IF EXISTS] 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,角色1 FROM 用户2,角色2;
REVOKE IF EXISTS 用户1,角色1 FROM 用户2,角色2 IGNORE UNKNOWN USER;
DROP USER IF EXISTS u1,u2,u3,u4,u5,u6,r1,r2,r3,r4,r5,r6;CREATE USER if not exists u1,u2,u3,u4,u5,u6;CREATE ROLE IF NOT EXISTS 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 IF EXISTS 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 [IF EXISTS] role [, role ] ...

试验 drop user 和 drop role 通用

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

文章目录

  • MySQL8 创建用户,设置修改密码,授权
      • 创建远程root的语句模板
      • MySQL账户由用户名@主机组成
      • 创建用户,设置密码,修改密码
        • 创建用户 `CREATE USER` , `create user`
        • 创建用户并指定密码
          • 使用默认加密方式,创建用户并指定密码 和 修改密码
          • 使用指定密码插件, 创建用户并指定密码 和 修改密码
            • 查看 用户,主机,密码插件
            • `show plugins`可以列出mysql的插件
            • 使用 `WITH mysql_native_password` 密码插件,创建用户并指定密码 和 修改密码
            • 使用 `WITH caching_sha2_password` 密码插件,创建用户并指定密码 和 修改密码
            • 查看当前的默认密码加密认证插件, 查看系统全局变量`default_authentication_plugin`
            • 设置默认的密码加密认证插件, 设置系统全局变量`default_authentication_plugin`
        • 修改密码
          • 用 `ALTER USER` 修改自己或别人的密码
          • 用 `SET PASSWORD=` 设置修改自己的密码
          • 用 `SET PASSWORD FOR` 修改自己或其他账户的密码, `=`等号分割账户和密码
        • 密码管理, 密码策略
          • 内部与外部凭证存储
          • 密码过期策略
            • 设置某用户的密码立刻过期
          • 需要密码验证的策略
            • 双密码支持
            • 随机密码生成
            • 登录失败跟踪和临时帐户锁定
        • 重命名用户或角色 `RENAME USER`
      • 授权 , 允许权力继续传递 , 撤权
        • 授权语法: `GRANT 权限以逗号分隔 ON 库.表 TO 账户以逗号分隔 [WITH GRANT OPTION]`
          • 将所有库的所有表的所有权限,授权给
          • 允许权力传递 `WITH GRANT OPTION`
          • 用户和用户,角色和角色,用户和角色,角色和用户,可以相互授权, 但要避免权力循环
        • 撤销授权
          • 撤销所有授权
            • 撤销远程root的全部权限
          • 撤销指定权限
          • GRANT 和 REVOKE 语法对比
          • REVOKE语句可以加 `IF EXISTS` 和 `IGNORE UNKNOWN USER `
        • 超级用户创建并授权超级用户
      • 删除用户 `DROP USER ‘u’@’h’`
      • 创建用户,授权,删除用户的官方例子
      • 角色管理
        • 用 `CREATE ROLE` 创建的角色也会出现在 `mysql.user` 表中, 只是被锁定
        • 创建角色 `CREATE ROLE` , `create role`
        • 授予角色权限的语法和授予用户权限的语法一样
        • 激活角色
          • 查看用户的角色 , 或角色的角色
          • `SET ROLE` , 临时激活角色
            • `SET ROLE ALL` 临时激活所有被grant授予的角色
            • `SET ROLE DEFAULT` 临时激活默认的角色
            • `SET ROLE NONE` 取消激活的角色
            • 设置默认角色`SET DEFAULT ROLE` ,
          • 设置全局变量 `activate_all_roles_on_login` , `ON`启用, 默认`OFF`停用 , 用户登陆时激活所有GRANT语句授予的角色
            • 在配置文件设置设置 `activate_all_roles_on_login` 每次重启都有效
            • 用 `SET GLOBAL` 命令设置 `activate_all_roles_on_login` 重启后失效
            • 用 `SET PERSIST` 命令设置 `activate_all_roles_on_login` 重启后有效,且优先级大于传统配置文件
        • 撤销角色权限的语法和撤销用户权限的语法一样, 用`REVOKE…FROM…`
        • 撤销用户的角色,撤销角色的角色,撤销用户的用户,撤销角色的用户, 用`REVOKE…FROM…`
        • 删除角色, 既可以用`DROP ROLE`,也可以用`DROP USER`
      • 知识扩展
          • 查看msql版本 `SELECT@@VERSION` , `show variables like ‘version’;`
          • 查看当前用户是谁 `select user();` 或者 `select current_user();` , 查看当前角色 `select current_role();`
        • 查看设置默认的密码加密插件 `default_authentication_plugin`
          • 通过配置文件设置默认的密码插件
          • 查看默认的默认的密码认证插件, 查看 default_authentication_plugin 系统变量
        • 查看权限列表 `SHOW privileges;`
        • 查看用户权限 `SHOW GRANTS;` 和 `SHOW GRANTS FOR ‘u’@’h’;`
          • 查看当前用户的权限, 查看自己的权限
          • 查看 root 有哪些权限
        • MySQL的一些 `SHOW` 命令语句
        • GRANT 的官方文档说明 版本8.0
          • GRANT Statement 语句语法
          • MySQL 支持的权限 privileges
        • MySQL配置文件的位置
          • Windows下MySQL配置文件的位置
          • Linux Unix下MySQL配置文件的位置

知识扩展

查看msql版本 SELECT@@VERSION , show variables like 'version';
SELECT @@VERSION;

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

sEleCt@@vErSIoN;

SHOW VARIABLES LIKE 'version';SHOW GLOBAL VARIABLES LIKE 'VERSION';
查看当前用户是谁 select user(); 或者 select current_user(); , 查看当前角色 select current_role();
select user();
mysql> SELECT user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)
select current_user();
mysql> select current_user();+----------------+| current_user() |+----------------+| root@localhost |+----------------+1 row in set (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_passworddefault_authentication_plugin = 'mysql_native_password'default_authentication_plugin = "mysql_native_password"
[mysqld]default_authentication_plugin=caching_sha2_passworddefault_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;

SHOW privileges;
mysql> SHOW privileges;+------------------------------+---------------------------------------+-------------------------------------------------------+| Privilege| Context | Comment |+------------------------------+---------------------------------------+-------------------------------------------------------+| Alter| Tables| To alter the table|| Alter routine| Functions,Procedures| To alter or drop stored functions/procedures|| Create | Databases,Tables,Indexes| To create new databases and tables|| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE|| Create role| Server Admin| To create new roles || Create temporary tables| Databases | To use CREATE TEMPORARY TABLE || Create view| Tables| To create new views || Create user| Server Admin| To create new users || Delete | Tables| To delete existing rows || Drop | Databases,Tables| To drop databases, tables, and views|| Drop role| Server Admin| To drop roles || Event| Server Admin| To create, alter, drop and execute events || Execute| Functions,Procedures| To execute stored routines|| File | File access on server | To read and write files on the server || Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess || Index| Tables| To create or drop indexes || Insert | Tables| To insert data into tables|| Lock tables| Databases | To use LOCK TABLES (together with SELECT privilege) || Process| Server Admin| To view the plain text of currently executing queries || Proxy| Server Admin| To make proxy user possible || References | Databases,Tables| To have references on tables|| Reload | Server Admin| To reload or refresh tables, logs and privileges|| Replication client | Server Admin| To ask where the slave or master servers are|| Replication slave| Server Admin| To read binary log events from the master || Select | Tables| To retrieve rows from table || Show databases | Server Admin| To see all databases with SHOW DATABASES|| Show view| Tables| To see views with SHOW CREATE VIEW|| Shutdown | Server Admin| To shut down the server || Super| Server Admin| To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. || Trigger| Tables| To use triggers || Create tablespace| Server Admin| To create/alter/drop tablespaces|| Update | Tables| To update existing rows || Usage| Server Admin| No privileges - 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| |+------------------------------+---------------------------------------+-------------------------------------------------------+68 rows in set (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|+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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 `root`@`localhost` WITH GRANT OPTION || 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` WITH GRANT OPTION || GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION |+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+3 rows in set (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 语句语法
GRANTpriv_type [(column_list)][, priv_type [(column_list)]] ...ON [object_type] priv_levelTO user_or_role [, user_or_role] ...[WITH GRANT OPTION][AS user[WITH ROLEDEFAULT| NONE| ALL| ALL EXCEPT role [, role ] ...| role [, role ] ...]]}GRANT PROXY ON user_or_roleTO user_or_role [, user_or_role] ...[WITH GRANT OPTION]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 节，“指定角色名称”中描述的格式。例如：

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';GRANT SELECT ON world.* TO 'role3';

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

通常，数据库管理员首先使用CREATE USER创建一个帐户并定义其非特权特征，例如密码、是否使用安全连接以及对服务器资源的访问限制，然后再用 GRANT定义其权限。 ALTER USER可用于更改现有帐户的非特权特性。例如：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';ALTER USER '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语句 指定具有空用户名的帐户

GRANT ALL ON test.* TO ''@'localhost' ...;

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

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

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

SELECT Host, User FROM mysql.user WHERE User='';

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

DROP USER ''@'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 options
C:\my.ini, C:\my.cnfGlobal options
BASEDIR\my.ini, BASEDIR\my.cnfGlobal options
defaults-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 options
SYSCONFDIR/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