创建用户

以下为数据库版本 5.7语法。

语法

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [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 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_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
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

示例

Each account name uses the format described in Section 6.2.4, “Specifying Account Names”. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

The host name part of the account name, if omitted, defaults to '%'.

• Mark the password expired so that the user must choose a new one at the first connection to the server:

CREATE USER 'jeffrey'@'localhost'  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

• Create an account that uses the sha256_password authentication plugin and the given password. Require that a new password be chosen every 180 days:

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

• Create multiple accounts, specifying some per-account properties and some global properties:

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
  ACCOUNT LOCK;

在auth_option这部分：
auth_plugin names an authentication plugin. The plugin name can be a quoted string literal or an unquoted name. Plugin names are stored in the plugin column of the mysql.user system table.

For auth_option syntax that does not specify an authentication plugin, the default plugin is indicated by the value of the default_authentication_plugin system variable. For descriptions of each plugin, see Section 6.4.1, “Authentication Plugins”.

在使用MySQL8时，容易碰到类似以下的问题：
The server requested authentication method unknown to the client [caching_sha2_password] in Standard input code on line 22
可以创建使用本地认证方式的用户：
create user 'someone' IDENTIFIED WITH 'mysql_native_password' BY 'plaintxt_password'
也可以通过设置数据库变量 default-authentication-plugin=mysql_native_password 来实现
在数据库中可用以下命令查看当前设置值： show variables like 'default_authentication_plugin'

Each auth_option value (IDENTIFIED WITH ... BY in this case) applies only to the account named immediately preceding it, so each account uses the immediately following authentication plugin and password.

The remaining properties apply globally to all accounts named in the statement, so for both accounts:

• Connections must be made using a valid X.509 certificate.
• Up to 60 queries per hour are permitted.
• The account is locked initially, so effectively it is a placeholder and cannot be used until an administrator unlocks it.

账户名称

MySQL account names consist of a user name and a host name, which enables creation of distinct accounts for users with the same user name who can connect from different hosts.

• MySQL账户名称由用户名和主机名称组成，并且两者均需使用引号(单引号或者双引号)或者反勾号(``)单独包括起来，格式： 'user_name'@'host_name'，
• 账户名称如果仅有 'user_name'，那么等同于 'user_name'@'%'
• 如果用户名称和主机名称不包含特殊字符时可以不用引号扩起来（密码仍然需要）
• current_user或者current_user() 相当于 当前客户端用户的完整账户名称

删除用户

语法：

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

Important

DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design.

• 删除用户不会关闭该用户已经打开的会话，而是当用户结束会话时这个删除语句才会生效。
• 删除时“user”需要使用完整账户即'user_name'@'host_name'，如未指明主机名称，则相当于删除 'user_name'@'%'

查看用户

For each account, CREATE USER creates a new row in the mysql.user system table. The account row reflects the properties specified in the statement.
对于每个用户，create user操作创建新的一行记录到mysql.user系统表中。

use mysql;
select host, user from user;

授权

An account when first created has no privileges. To assign privileges, use the GRANT statement.
新创建的账户没有任何权限，需要使用grant语句进行授权。

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

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

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 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_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
}

示例：

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;

Table 13.8 Permissible Privileges for GRANT and REVOKE

A trigger is associated with a table. To create or drop a trigger, you must have the TRIGGER privilege for the table, not the trigger.

查看用户权限

示例：

show grants for 'root'@'localhost'