创建用户

语法

CREATE [OR REPLACE] USER [IF NOT EXISTS] 
 user_specification [,user_specification ...] 
  [REQUIRE {NONE | tls_option [[AND] tls_option ...] }]
  [WITH resource_option [resource_option ...] ]
  [lock_option] [password_option] 

user_specification:
  username [authentication_option]

authentication_option:
  IDENTIFIED BY 'password' 
  | IDENTIFIED BY PASSWORD 'password_hash'
  | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule  ...]

authentication_rule:
    authentication_plugin
  | authentication_plugin {USING|AS} 'authentication_string'
  | authentication_plugin {USING|AS} PASSWORD('password')

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
  | MAX_STATEMENT_TIME time

password_option:
  PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY

lock_option:
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

描述

CREATE USER 语句创建新的 MariaDB 帐户。要使用它，您必须具有全局 CREATE USER 特权或 INSERT 特权以访问 mysql 数据库。对于每个帐户，CREATE USER 在 mysql.user (在 MariaDB 10.3 之前是表，在 MariaDB 10.4 之后是视图) 或 mysql.global_priv_table (从 MariaDB 10.4 开始) 中创建一个没有权限的新行。

如果指定的任何帐户或指定的帐户的任何权限已经存在，则服务器将返回 ERROR 1396 (HY000)。如果发生错误，CREATE USER 仍将创建未产生错误的帐户。对于未创建的所有用户，只产生一个错误：

ERROR 1396 (HY000): 
  Operation CREATE USER failed for 'u1'@'%','u2'@'%'

当 CREATE USER、DROP USER、CREATE ROLE 和 DROP ROLE 失败时，它们都会产生相同的错误代码。

有关指定帐户名称的详细信息，请参阅下面的 帐户名称。

OR REPLACE

如果使用可选的 OR REPLACE 子句，则基本上是以下快捷方式：

DROP USER IF EXISTS name;
CREATE USER name ...;

例如：

CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'

CREATE OR REPLACE USER foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

IF NOT EXISTS

当使用 IF NOT EXISTS 子句时，如果指定的用户已经存在，则 MariaDB 将返回一个警告而不是错误。

例如：

CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'

CREATE USER IF NOT EXISTS foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

显示警告;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note  | 1973 | Can't create user 'foo2'@'test'; it already exists |
+-------+------+----------------------------------------------------+

认证选项

IDENTIFIED BY 'password'

可选的 IDENTIFIED BY 子句可用于为帐户提供密码。密码应以明文形式指定。在存储在 mysql.user/mysql.global_priv_table 表中之前，它将由 PASSWORD 函数进行哈希处理。

例如，如果我们的密码是 mariadb，则可以使用以下命令创建用户：

CREATE USER foo2@test IDENTIFIED BY 'mariadb';

如果您没有在 IDENTIFIED BY 子句中指定密码，则该用户将能够无密码连接。空白密码不是匹配任何密码的通配符。如果没有设置密码，则用户必须在不提供密码的情况下连接。

此子句支持的唯一 认证插件 是 mysql_native_password 和 mysql_old_password。

IDENTIFIED BY PASSWORD 'password_hash'

可选的 IDENTIFIED BY PASSWORD 子句可用于提供已经被哈希处理的密码的帐户。密码应指定为由 PASSWORD 函数提供的哈希值。它将按原样存储在 mysql.user/mysql.global_priv_table 表中。

例如，如果我们的密码是 mariadb，则可以使用以下命令查找哈希值：

SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb')                       |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)

然后，我们可以使用哈希值创建用户：

CREATE USER foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';

如果您没有在 IDENTIFIED BY 子句中指定密码，则该用户将能够无密码连接。空白密码不是匹配任何密码的通配符。如果没有设置密码，则用户必须在不提供密码的情况下连接。

这个子句支持的唯一认证插件是mysql_native_password和mysql_old_password。

通过认证插件认证

可选的IDENTIFIED VIA认证插件允许您指定帐户应由特定的认证插件进行身份验证。插件名称必须是作为SHOW PLUGINS的活动认证插件。如果它没有显示在该输出中，则需要使用INSTALL PLUGIN或INSTALL SONAME安装它。

例如，这可以与PAM认证插件一起使用：

CREATE USER foo2@test IDENTIFIED VIA pam;

某些认证插件允许在USING或AS关键字之后指定其他参数。例如，PAM认证插件接受服务名称：

CREATE USER foo2@test IDENTIFIED VIA pam USING 'mariadb';

附加参数的确切含义取决于特定的认证插件。

CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');

CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;

默认情况下，当您创建一个没有指定认证插件的用户时，MariaDB使用mysql_native_password插件。

TLS 选项

默认情况下，MariaDB在服务器和客户端之间传输数据时不加密。当服务器和客户端在同一主机上运行或在通过其他方式保证安全性的网络中运行时，这通常是可以接受的。但是，在服务器和客户端存在于不同网络或它们在高风险网络中的情况下，缺乏加密确实会引入安全问题，因为恶意行为者有可能窃听它们之间在网络上传输的流量。

为了缓解这个问题，MariaDB允许您使用传输层安全性（TLS）协议在服务器和客户端之间加密传输的数据。TLS以前称为安全套接字层（SSL），但严格来说SSL协议是TLS的前身，并且该协议的版本现在被认为是不安全的。文档仍经常使用术语SSL，出于兼容性原因，与TLS相关的服务器系统和状态变量仍使用前缀ssl_，但在内部，MariaDB仅支持其安全的后继者。

有关如何确定您的MariaDB服务器是否具有TLS支持的更多信息，请参见Secure Connections Overview。

您可以为特定用户帐户设置某些与TLS相关的限制。例如，您可能会将其用于需要访问敏感数据的用户帐户，同时将其发送到您无法控制的网络。可以使用CREATE USER、ALTER USER或GRANT语句为用户帐户启用这些限制。以下选项可用：

The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required.

For example, you can create a user account that requires these TLS options with the following:

CREATE USER 'alice'@'%'
 REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
 AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/[email protected]'
 AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS.

See Securing Connections for Client and Server for information on how to enable TLS on the client and server.

Resource Limit Options

It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:

If any of these limits are set to 0, then there is no limit for that resource for that user.

Here is an example showing how to create a user with resource limits:

CREATE USER 'someone'@'localhost' WITH
    MAX_USER_CONNECTIONS 10
    MAX_QUERIES_PER_HOUR 200;

The resources are tracked per account, which means 'user'@'server'; not per user name or per connection.

The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mysqladmin reload.

Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions, max_updates, max_connections (for MAX_CONNECTIONS_PER_HOUR), and max_user_connections (for MAX_USER_CONNECTIONS).

Account Names

Account names have both a user name component and a host name component, and are specified as 'user_name'@'host_name'.

The user name and host name may be unquoted, quoted as strings using double quotes (") or single quotes ('), or quoted as identifiers using backticks (`). You must use quotes when using special characters (such as a hyphen) or wildcard characters. If you quote, you must quote the user name and host name separately (for example 'user_name'@'host_name').

Host Name Component

If the host name is not provided, it is assumed to be '%'.

Host names may contain the wildcard characters % and _. They are matched as if by the LIKE clause. If you need to use a wildcard character literally (for example, to match a domain name with an underscore), prefix the character with a backslash. See LIKE for more information on escaping wildcard characters.

Host name matches are case-insensitive. Host names can match either domain names or IP addresses. Use 'localhost' as the host name to allow only local client connections. On Linux, the loopback interface (127.0.0.1) will not match 'localhost' as it is not considered a local connection: this means that only connections via UNIX-domain sockets will match 'localhost'.

You can use a netmask to match a range of IP addresses using 'base_ip/netmask' as the host name. A user with an IP address ip_addr will be allowed to connect if the following condition is true:

ip_addr & netmask = base_ip

For example, given a user:

CREATE USER 'maria'@'247.150.130.0/255.255.255.0';

the IP addresses satisfying this condition range from 247.150.130.0 to 247.150.130.255.

Using 255.255.255.255 is equivalent to not using a netmask at all. Netmasks cannot be used for IPv6 addresses.

Note that the credentials added when creating a user with the '%' wildcard host will not grant access in all cases. For example, some systems come with an anonymous localhost user, and when connecting from localhost this will take precedence.

Before MariaDB 10.6, the host name component could be up to 60 characters in length. Starting from MariaDB 10.6, it can be up to 255 characters.

User Name Component

User names must match exactly, including case. A user name that is empty is known as an anonymous account and is allowed to match a login attempt with any user name component. These are described more in the next section.

For valid identifiers to use as user names, see Identifier Names.

It is possible for more than one account to match when a user connects. MariaDB selects the first matching account after sorting according to the following criteria:

• Accounts with an exact host name are sorted before accounts using a wildcard in the host name. Host names using a netmask are considered to be exact for sorting.
• Accounts with a wildcard in the host name are sorted according to the position of the first wildcard character. Those with a wildcard character later in the host name sort before those with a wildcard character earlier in the host name.
• Accounts with a non-empty user name sort before accounts with an empty user name.
• Accounts with an empty user name are sorted last. As mentioned previously, these are known as anonymous accounts. These are described more in the next section.

The following table shows a list of example account as sorted by these criteria:

+---------+-------------+
| User    | Host        |
+---------+-------------+
| joffrey | 192.168.0.3 |
|         | 192.168.0.% |
| joffrey | 192.168.%   |
|         | 192.168.%   |
+---------+-------------+

Once connected, you only have the privileges granted to the account that matched, not all accounts that could have matched. For example, consider the following commands:

CREATE USER 'joffrey'@'192.168.0.3';
CREATE USER 'joffrey'@'%';
GRANT SELECT ON test.t1 to 'joffrey'@'192.168.0.3';
GRANT SELECT ON test.t2 to 'joffrey'@'%';

If you connect as joffrey from 192.168.0.3, you will have the SELECT privilege on the table test.t1, but not on the table test.t2. If you connect as joffrey from any other IP address, you will have the SELECT privilege on the table test.t2, but not on the table test.t1.

Usernames can be up to 80 characters long before 10.6 and starting from 10.6 it can be 128 characters long.

Anonymous Accounts

Anonymous accounts are accounts where the user name portion of the account name is empty. These accounts act as special catch-all accounts. If a user attempts to log into the system from a host, and an anonymous account exists with a host name portion that matches the user's host, then the user will log in as the anonymous account if there is no more specific account match for the user name that the user entered.

For example, here are some anonymous accounts:

CREATE USER ''@'localhost';
CREATE USER ''@'192.168.0.3';

Fixing a Legacy Default Anonymous Account

On some systems, the mysql.db table has some entries for the ''@'%' anonymous account by default. Unfortunately, there is no matching entry in the mysql.user/mysql.global_priv_table table, which means that this anonymous account doesn't exactly exist, but it does have privileges--usually on the default test database created by mysql_install_db. These account-less privileges are a legacy that is leftover from a time when MySQL's privilege system was less advanced.

This situation means that you will run into errors if you try to create a ''@'%' account. For example:

CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'

The fix is to DELETE the row in the mysql.db table and then execute FLUSH PRIVILEGES:

DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;

And then the account can be created:

CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)

See MDEV-13486 for more information.

Password Expiry

CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;

Account Locking

CREATE USER 'marijn'@'localhost' ACCOUNT LOCK;

From MariaDB 10.4.7 and MariaDB 10.5.8, the lock_option and password_option clauses can occur in either order.

See Also

• Troubleshooting Connection Issues
• Authentication from MariaDB 10.4
• Identifier Names
• GRANT
• ALTER USER
• DROP USER
• SET PASSWORD
• SHOW CREATE USER
• mysql.user table
• mysql.global_priv_table
• Password Validation Plugins - permits the setting of basic criteria for passwords
• Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.