MySQL 8创建用户与用户管理

最新推荐文章于 2024-08-29 04:29:16 发布

山与先生

最新推荐文章于 2024-08-29 04:29:16 发布

阅读量1k

点赞数

分类专栏： MySQL 文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/u010257584/article/details/106800527

版权

MySQL 专栏收录该内容

34 篇文章 1 订阅

订阅专栏

本文深入讲解MySQL中的用户创建、身份验证、密码管理、资源限制、TLS配置及用户管理操作，如ALTER、DROP和RENAME。涵盖了CREATE USER语法、各种选项如auth_option、tls_option、resource_option、password_option和lock_option的详细解释。

摘要由CSDN通过智能技术生成

1. 创建user

对于每个帐户，CREATE USER在mysql.user系统表中创建一条新数据行，包括创建语句中指定的属性，未指定的属性设置为其默认值：

身份验证：由default_authentication_plugin系统变量定义的身份验证插件和空凭据；
默认角色： NONE；
SSL/TLS： NONE；
资源限制：无限制；
密码管理：PASSWORD EXPIRE DEFAULT PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT；禁用登录失败跟踪和临时帐户锁定。
帐户锁定： ACCOUNT UNLOCK

Create user语法：

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

1.1 USER

MySQL帐户名由用户名和主机名组成，这可以为具有相同用户名且从不同主机进行连接的用户创建不同的帐户。

使用反引号（`），单引号（’）或双引号（"）将用户名和主机名作为标识符或字符串引号，USER名称的语法为：‘user_name’@‘host_name’，帐户名的主机名部分（如果省略）默认为’%’，仅包含用户名的帐户名等效于’user_name’@’%’，例如’cy’等效于’cy’@’%’；
主机值可以是主机名或IP地址（IPv4或IPv6），‘localhost’表示本地主机，IP地址’127.0.0.1’ 表示IPv4环回接口，IP地址 '::1’表示IPv6环回接口；
主机名或IP地址值中允许使用%和_通配符，例如，主机值’%‘匹配任何主机名，而值’%.mysql.com’匹配mysql.com域中的任何主机， '198.51.100.%'匹配198.51.100 C类网络中的任何主机。

示例1，创建最简单的本地账户（从MySQL 8.0.21开始，可以创建具有注释或用户属性的用户）：

mysql> CREATE USER 'cy'@'localhost' IDENTIFIED BY 'cy123' COMMENT 'Information about ChengYu';
mysql> CREATE USER 'yx'@'localhost' ATTRIBUTE '{"fname": "Zhong", "lname": "yx", "phone": "136-86**-****"}';

示例2：创建一个使用caching_sha2_password身份验证插件和给定密码的帐户，要求每365天更新一次密码，并启用登录失败跟踪，3次连续的错误密码输入就锁定账户2天：

mysql> CREATE USER 'cy'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'cy123'
  PASSWORD EXPIRE INTERVAL 365 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

示例3：创建多个帐户，可以设置每个帐户有私有属性和一些全局属性：

必须使用有效的X.509证书进行连接;
每小时最多允许60个查询;
密码更改不能复用5个最近使用的密码中的任何一个;
该帐户最初是被锁定的，因此实际上是一个占位符，只有在管理员将其解锁后才能使用。

mysql> CREATE USER
  'cy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'cy123',
  'yx'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yx456'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  PASSWORD HISTORY 5
  ACCOUNT LOCK;

1.2 auth_option

auth_option指定帐户身份验证插件，插件名称可以是带引号的字符串文字或不带引号的名称，插件名称存储在系统表的plugin列中 mysql.user。

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

IDENTIFIED BY ‘auth_string’：将明文密码传递给该插件以进行散列处理，并将结果存储在mysql.user系统表中。
IDENTIFIED BY RANDOM PASSWORD：生成随机密码，将明文密码值传递给该插件以进行散列处理，并将结果存储在mysql.user系统表中。
IDENTIFIED WITH auth_plugin：将帐户身份验证插件设置为auth_plugin，将凭据清除为空字符串，并将结果存储在mysql.user系统表中。
IDENTIFIED WITH auth_plugin BY ‘auth_string’：将帐户身份验证插件设置为auth_plugin，将明文密码传递给该插件以进行散列处理，并将结果存储在系统表的帐户行中。
IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD：将帐户身份验证插件设置为auth_plugin，生成一个随机密码，将明文密码值传递给该插件以进行散列处理，并将结果存储在mysql.user系统表中。
IDENTIFIED WITH auth_plugin AS ‘auth_string’：将帐户身份验证插件设置为auth_plugin并将密码存储到帐户行中，如果插件需要哈希字符串，则假定该字符串已经以插件所需的格式进行哈希处理。

# 使用默认插件并将密码指定为明文
mysql> CREATE USER 'yx'@'localhost' IDENTIFIED BY 'yx456';
Query OK, 0 rows affected (0.07 sec)

mysql> select plugin,authentication_string from mysql.user where user='yx'\G
*************************** 1. row ***************************
                  plugin: caching_sha2_password
   authentication_string: $A$005${__(RC>xwH:,iHdxMEtMoYy4emipZbr2fIVE2WklKpbcnYZSr3gQYPM0y5
1 row in set (0.00 sec)

# 指定身份验证插件以及明文密码
mysql> CREATE USER 'yx'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yx456';
Query OK, 0 rows affected (0.07 sec)

mysql> select plugin,authentication_string from mysql.user where user='yx'\G
                  plugin: mysql_native_password
   authentication_string: *966262893486B77167411EBDE108C027282B03A5

# 随机密码
mysql> CREATE USER 'yx'@'localhost' IDENTIFIED WITH mysql_native_password BY RANDOM PASSWORD;
+------+-----------+----------------------+
| user | host      | generated password   |
+------+-----------+----------------------+
| yx   | localhost | q;x.q4qrZSWb1+QfJI,d |
+------+-----------+----------------------+
1 row in set (0.07 sec)
mysql> select plugin,authentication_string from mysql.user where user='yx'\G
*************************** 1. row ***************************
               plugin: mysql_native_password
authentication_string: *BE895BDCEFEF2284A4F07ECA4A2E1855C24C9FCF
1 row in set (0.00 sec)

1.3 用户密码

MySQL支持以下密码管理功能：

密码到期，要求定期更改密码；
密码重用限制，以防止再次选择旧密码；
密码验证，要求更改密码还指定要替换的当前密码；
双密码，使客户端可以使用主密码或辅助密码进行连接；
密码强度评估，要求使用强密码；
随机密码生成，作为要求管理员指定明确的文字密码的替代方法；
密码失败跟踪，用于在连续多次错误密码登录失败后启用临时帐户锁定。

示例，手动使帐户密码失效，在mysql.user系统表的相应行中标记密码已过期：

mysql> ALTER USER 'cy'@'localhost' PASSWORD EXPIRE;

要建立密码有效期为全局策略，可以在my.cnf文件中使用如下参数来控制，N为天数，为0是表示永不过期：
[mysqld]
default_password_lifetime=N

也可以在运行时设置和持久化：

mysql> SET PERSIST default_password_lifetime = N;

全局密码过期策略适用于尚未设置覆盖该策略的所有用户，要为单个用户建立政策，请使用PASSWORD EXPIRE。

# 每隔180天更新一次密码
mysql> CREATE USER 'cy'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
mysql> ALTER USER 'cy'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;

# 禁用密码有效期：
mysql> CREATE USER 'cy'@'localhost' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'cy'@'localhost' PASSWORD EXPIRE NEVER;

# 对语句命名的所有用户遵循全局到期策略
mysql> CREATE USER 'cy'@'localhost' PASSWORD EXPIRE DEFAULT;
mysql> ALTER USER 'cy'@'localhost' PASSWORD EXPIRE DEFAULT;

# 在允许重复使用之前，至少需要更改5次密码，至少需要经过365天
mysql> CREATE USER 'cy'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;
mysql> ALTER USER 'cy'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;

密码重用策略：使用 password_history和 password_reuse_interval系统变量控制，例如，禁止重复使用最近6个密码或365天以内的密码：
[mysqld]
password_history=6
password_reuse_interval=365

关于用户双密码、随机密码另起专题讨论。

1.4 tls_option

除基于用户名和凭据的常规身份验证外，MySQL还可以检查X.509证书属性，要为MySQL帐户指定与SSL/TLS相关的选项，请使用REQUIRE子句指定一个或多个tls_option值；REQUIRE选项的顺序无关紧要，但是不能重复指定同一选项，可使用AND关键字指定多个REQUIRE。

tls_option: {
   SSL 表示该语句命名的所有帐户都没有SSL或X.509要求
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

tls_option值：

NONE：REQUIRE默认值，表示该语句命名的所有帐户都没有SSL或X.509要求，如果用户名和密码有效，则允许未加密的连接；如果客户端具有正确的证书和密钥文件，则可以选择使用加密连接。
SSL：服务器仅允许该语句命名的所有帐户的加密连接，客户端默认尝试建立安全连接，对于REQUIRE SSL帐户，如果无法建立安全连接，则连接将失败。
X509：对于该语句命名的所有帐户，要求客户出示有效的证书，但是确切的证书，颁发者和主题不重要。唯一的要求是其中有CA证书来进行身份验证，X.509证书是加密使用的，因此SSL在这种情况下并不是必要的。对具有REQUIRE X509的帐户，客户必须指定–ssl-key 和–ssl-cert选项进行连接（建议但不要求指定–ssl-ca，以便可以验证服务器提供的公共证书）。
ISSUER ‘issuer’：对于该语句命名的所有帐户，要求客户端出示由CA颁发的有效的X.509证书，如果客户端出示的证书有效但颁发者不同，则服务器将拒绝连接；X.509证书是加密使用的，因此SSL在这种情况下并不是必要的。因为ISSUER需要X509，所以客户端必须指定–ssl-key和–ssl-cert选项进行连接。（建议但不要求指定–ssl-ca，以便可以验证服务器提供的公共证书。）
SUBJECT ‘subject’：对于该语句命名的所有帐户，要求客户端出示包含subject的有效X.509证书；如果客户端出示的证书有效但subject不同，则服务器拒绝连接；X.509证书的使用暗含加密，因此SSL在这种情况下并不是必要的。
CIPHER ‘cipher’：对于该语句命名的所有帐户，都需要使用特定的加密方法来加密连接，需要使用此选项来确保使用足够强度的密码和密钥长度，如果使用使用短加密密钥的旧算法，则加密可能会很弱。
REQUIRE clause里SUBJECT，ISSUER和CIPHER选项可组合使用。

1.5 resource_option

限制客户端使用MySQL服务器资源的一种方法是将全局max_user_connections系统变量设置为非零值，这限制了任何给定帐户可以建立的同时连接的数量，但是对客户端一旦连接后可以执行的操作没有任何限制，此外，设置max_user_connections无法启用单个帐户的管理，为此，MySQL允许限制使用这些服务器资源的个人帐户：

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

客户端发出的任何语句都将计入查询限制，但计入更新限制的只有修改数据库或表的语句。

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

resource_option可以限制帐户对服务器资源的使用，使用WITH指定一个或多个resource_option值，WITH选项的顺序无关紧要，多次指定给定的某个资源限制，以最后的为准。

CREATE USER的resource_option值：

MAX_QUERIES_PER_HOUR count，MAX_UPDATES_PER_HOUR count，MAX_CONNECTIONS_PER_HOUR count：对于该语句命名的所有帐户，这些选项限制了在任何给定的每小时内允许帐户进行查询、更新和与服务器的连接的次数，如果count为 0（默认值），则表示该帐户没有限制。
MAX_USER_CONNECTIONS count：对于该语句命名的所有帐户，限制每个帐户同时连接到服务器的最大数量（并发数）；如果 count为0 （默认值），则服务器根据max_user_connections系统变量的全局值确定该帐户的并发连接数，如果max_user_connections也为零，则该帐户没有限制。

1.6 password_option

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

CREATE USER支持多个password_option密码管理值：

密码到期选项：可以手动使帐户密码到期并建立其密码到期策略。策略选项不会使密码失效，而是根据密码有效期来确定服务器如何将自动过期应用于帐户，该有效期是根据最近的帐户密码更改的日期和时间来确定。
密码重用选项：可以基于密码更改次数、经过时间或两者兼而有之来限制密码重用。
密码验证所需的选项：可以规定更改帐户密码时是否必须输入当前密码，以验证尝试进行更改的用户实际上是否知道当前密码。
错误密码失败登录跟踪选项：跟踪登录失败并暂时锁定多次密码输入错误的账户，失败次数和锁定时间是可配置的。

注意：除了与失败登录跟踪有关的选项外，密码管理选项仅适用于使用身份验证插件的帐户，该身份验证插件将凭据存储在内部。对于使用插件对MySQL外部的凭据系统执行身份验证的帐户，密码管理也必须在外部针对该系统进行处理。
如果帐户密码是手动过期的，或者根据自动过期策略，密码年龄超过定义的生存期，则客户端的密码将过期；在这种情况下，服务器要么断开与客户端的连接，要么限制其允许的操作，受限制的客户端执行的操作会导致错误，直到用户建立新的帐户密码。

CREATE USER允许使用以下password_option值来控制密码的有效期：

PASSWORD EXPIRE：立即将该语句命名的所有帐户的密码标记为过期。
PASSWORD EXPIRE DEFAULT：该参数定义的账户将应用default_password_lifetime系统全局到期策略。
PASSWORD EXPIRE NEVER：该到期选项将覆盖该语句命名的所有帐户的全局策略，对于每个密码，它都会禁用密码过期，以使密码永不过期。
PASSWORD EXPIRE INTERVAL N DAY：该到期选项将覆盖该语句命名的所有帐户的全局策略，对于每个密码，它将密码有效期设置为N几天。

# 每365天更换一次密码
mysql> CREATE USER 'cy'@'localhost' PASSWORD EXPIRE INTERVAL 365 DAY;

用于根据所需的最小密码更改次数来控制先前密码的重用，CREATE USER允许这些password_option值：

PASSWORD HISTORY DEFAULT：该参数定义的账户会应用有关密码历史记录长度的全局策略，禁止在password_history系统变量指定的更改次数之前重复使用密码。
PASSWORD HISTORY N：此历史记录长度选项将覆盖该语句命名的所有帐户的全局策略，将密码历史记录设置为N，则禁止重复使用之前任何N以内的密码，以下语句禁止重用之前的6个密码：

mysql> CREATE USER 'cy'@'localhost' PASSWORD HISTORY 6;

根据经过的时间来控制先前密码的重用，CREATE USER允许这些password_option值用：

PASSWORD REUSE INTERVAL DEFAULT：该值定义的帐户将应用有关经过时间的全局策略，以禁止重复使用password_reuse_interval指定的天数内的新密码。
PASSWORD REUSE INTERVAL N DAY：这个选项会覆盖该语句命名的所有帐户的全局策略，密码重用间隔设置为N天，即禁止重用N天内的新密码。

CREATE USER允许使用这些 password_option值来控制尝试更改帐户密码是否必须指定当前密码，以验证尝试进行更改的用户实际上是否知道当前密码：

PASSWORD REQUIRE CURRENT：该验证选项将覆盖该语句命名的所有帐户的全局策略，对于每个密码，要求更改密码时指定当前密码。
PASSWORD REQUIRE CURRENT OPTIONAL：该验证选项将覆盖该语句命名的所有帐户的全局策略，对于每个密码，不需要更改密码即可指定当前密码（可以但不必提供当前密码）。
PASSWORD REQUIRE CURRENT DEFAULT：该选项定义的账户将应用password_require_current有关密码验证的全局策略。

从MySQL 8.0.19开始，CREATE USER允许使用以下password_option值来控制登录失败跟踪：

FAILED_LOGIN_ATTEMPTS N：N必须为0到32767之间的数字，值0会禁用登录失败跟踪，大于0的值表示有多少连续密码失败导致临时帐户锁定（如果PASSWORD_LOCK_TIME也是非零的话）。
PASSWORD_LOCK_TIME {N | UNBOUNDED}：定义连续多次密码输入错误后的账户锁定时长， N必须是0到32767之间的数字、或者UNBOUNDED，值为0将禁用临时帐户锁定，大于0的值表示锁定帐户的天数，值 UNBOUNDED导致帐户锁定时间不受限制；一旦锁定，该帐户将保持锁定状态，直到被解锁。

为了进行失败的登录跟踪和临时锁定，帐户FAILED_LOGIN_ATTEMPTS和 PASSWORD_LOCK_TIME选项都必须为非零，创建帐户在连续6次密码失败后保持锁定状态3天：

mysql> CREATE USER 'cy'@'localhost' FAILED_LOGIN_ATTEMPTS 6 PASSWORD_LOCK_TIME 3;

1.7 lock_option

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

MySQL支持使用ACCOUNT LOCK和ACCOUNT UNLOCK选项锁定和解锁帐户，在CREATE USER和ALTER USER子句中使用， ALTER USER … UNLOCK解锁由由于登录失败次数过多而暂时锁定的任何帐户。

帐户锁定状态记录在系统表mysql.user的account_locked列中，SHOW CREATE USER可查看帐户是锁定还是未锁定，如果客户端尝试连接到锁定的帐户，连接将失败，同时服务器端的Locked_connects状态变量值递增，并返回 ER_ACCOUNT_HAS_BEEN_LOCKED错误，并将消息写入错误日志：

Access denied for user 'user_name'@'host_name'.
Account is locked.

2. 用户管理

2.1 ALTER用户

ALTER USER允许为现有帐户修改身份验证，角色，SSL / TLS，资源限制和密码管理属性，也可以用于锁定和解锁帐户；在大多数情况下，ALTER USER需要全局CREATE USER或系统UPDATE权限。

# 语法
ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

来几个例子：

mysql> ALTER USER 'cy'@'localhost' IDENTIFIED BY 'King_123';

mysql> ALTER USER 'cy'@'localhost' ACCOUNT UNLOCK;

mysql> ALTER USER 'cy'@'localhost' IDENTIFIED BY '123456',
  'yx'@'localhost' IDENTIFIED BY '789012' 
  REPLACE 'josh_current_password' RETAIN CURRENT PASSWORD
  REQUIRE SSL WITH MAX_USER_CONNECTIONS 2
  PASSWORD HISTORY 5;
  
mysql> ALTER USER 'cy'@'localhost'
  IDENTIFIED WITH mysql_native_password BY '123456'
  REPLACE 'current_password';
  
mysql> ALTER USER 'jeffrey'@'localhost'
FAILED_LOGIN_ATTEMPTS 6 PASSWORD_LOCK_TIME 3;

2.2 DROP用户

执行drop操作必须具有全局CREATE USER权限或系统级别的DELETE权限，对于只读环境，DROP USER还需要CONNECTION_ADMIN权限。

# 语法
DROP USER [IF EXISTS] user [, user] ...
# 例子
mysql> DROP USER 'cy'@'localhost';
mysql> DROP USER 'cy'@'%';
mysql> DROP USER 'cy';

注意：帐户名的主机名部分（如果省略）默认为’%’，所以’cy’@’%'与’cy’为同一用户；DROP USER不会自动关闭任何打开的用户会话，相反，如果删除了具有打开的会话的用户，则该语句在该用户的会话关闭之前不会生效，直到该会话关闭drop才生效，并且该用户的下一次登录将失败。

用SELECT和CONCAT来批量删除的SQL语句：

mysql> SELECT DISTINCT CONCAT(' drop user ''',user,'''@''',host,''';') AS query FROM mysql.user;
+--------------------------------------------+
| query                                      |
+--------------------------------------------+
|  drop user 'cy'@'192.183.3.147';           |
|  drop user 'cy'@'localhost';               |
|  drop user 'mysql.infoschema'@'localhost'; |
|  drop user 'mysql.session'@'localhost';    |
|  drop user 'mysql.sys'@'localhost';        |
|  drop user 'root'@'localhost';             |
|  drop user 'yx'@'localhost';               |
+--------------------------------------------+
7 rows in set (0.03 sec)

2.3 RENAME USER

要使用RENAME USER必须具有全局CREATE USER权限或系统级别的UPDATE权限，在read_only环境下，RENAME USER还需要CONNECTION_ADMIN 权限，对于不存在的旧帐户或已经存在的新帐户，将发生错误。

# 语法
RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

# 例子   
mysql> RENAME USER 'cy'@'localhost' TO 'cy'@'127.0.0.1';

RENAME USER使旧用户拥有的权限成为新用户拥有的权限，但是， RENAME USER不会自动删除旧用户创建的数据库或其中的数据库或对象或使它们无效，这包括DEFINER属性名称为旧用户的存储程序或视图。

2020年6月19日
“有时候会想，生命里某些当时充满怨怼的曲折，在后来好像都成了一种能量和养分，因为若非这些曲折，好像就不会在人生的岔路上遇见别人可能求之亦不得见的人与事；而这些人、那些事在经过时间的筛滤之后，几乎都只剩下笑与泪与感动和温暖，曾经的怨与恨与屈辱和不满仿佛都已云消雾散。” ——吴念真《这些人，那些事》
翻篇了……