MySQL指定账号加密方式的方法

MySQL指定账号加密方式的方法,MySQL账号常用加密方式:
caching_sha2_password(8.0之后默认加密方式) > sha256_password > mysql_native_password(8.0之前默认)

0.加密安全度

caching_sha2_password > sha256_password > mysql_native_password

The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password.

1.创建用户

13.7.1.3 CREATE USER Syntax
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] ...
user:
    (see Section 6.2.4, “Specifying Account Names”)
auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}
tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}
resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}
password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]Account Management Statements
2241
}
lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}
The CREATE USER statement creates new MySQL accounts. It enables authentication, role, SSL/TLS,
resource-limit, and password-management properties to be established for new accounts, and controls
whether accounts are initially locked or unlocked.
To use CREATE USER, you must have the global CREATE USER privilege, or the INSERT privilege
for the mysql system database. When the read_only system variable is enabled, CREATE USER
additionally requires the CONNECTION_ADMIN or SUPER privilege.
CREATE USER either succeeds for all named users or rolls back and has no effect if any error occurs.
By default, an error occurs if you try to create a user that already exists. If the IF NOT EXISTS clause
is given, the statement produces a warning for each named user that already exists, rather than an
error.
Important
Under some circumstances, CREATE USER may be recorded in server logs or
on the client side in a history file such as ~/.mysql_history, which means
that cleartext passwords may be read by anyone having read access to that
information. For information about the conditions under which this occurs for the
server logs and how to control it, see Section 6.1.2.3, “Passwords and Logging”.
For similar information about client-side logging, see Section 4.5.1.3, “mysql
Logging”.
复制

1.1 例子

CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH sha256_password
                                  BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  PASSWORD HISTORY 5
  ACCOUNT LOCK;
复制

2.修改用户

13.7.1.1 ALTER USER Syntax
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] ...Account Management Statements
2230
ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string']
ALTER USER [IF EXISTS]
    user DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
user:
    (see Section 6.2.4, “Specifying Account Names”)
auth_option: {
    IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string']
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [REPLACE 'current_auth_string']
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}
tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}
resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}
password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}
lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}
The ALTER USER statement modifies MySQL accounts. It enables authentication, role, SSL/TLS,
resource-limit, and password-management properties to be modified for existing accounts, and enables
account locking and unlocking.
In most cases, ALTER USER requires the global CREATE USER privilege, or the UPDATE privilege for
the mysql system database. The exceptions are:
• Any client who connects to the server using a nonanonymous account can change the password for
that account. (In particular, you can change your own password.) To see which account the server
authenticated you as, invoke the CURRENT_USER() function:
SELECT CURRENT_USER();
• For DEFAULT ROLE syntax, ALTER USER requires these privileges:
• Setting the default roles for another user requires the global CREATE USER privilege, or the
UPDATE privilege for the mysql.default_roles system table.
• Setting the default roles for yourself requires no special privileges, as long as the roles you want
as the default have been granted to you.
When the read_only system variable is enabled, ALTER USER additionally requires the
CONNECTION_ADMIN or SUPER privilege.Account Management Statements
2231
By default, an error occurs if you try to modify a user that does not exist. If the IF EXISTS clause is
given, the statement produces a warning for each named user that does not exist, rather than an error.
Important
Under some circumstances, ALTER USER may be recorded in server logs or
on the client side in a history file such as ~/.mysql_history, which means
that cleartext passwords may be read by anyone having read access to that
information. For information about the conditions under which this occurs for the
server logs and how to control it, see Section 6.1.2.3, “Passwords and Logging”.
For similar information about client-side logging, see Section 4.5.1.3, “mysql
Logging”.

复制

2.1 例子

Example: Modify an account to use the sha256_password authentication plugin and the given password. Require that a new password be chosen every 180 days:

ALTER USER 'jeffrey'@'localhost'  IDENTIFIED WITH sha256_password BY 'new_password'  PASSWORD EXPIRE INTERVAL 180 DAY;
复制

[toc]