Purpose
You can use this statement to create an OceanBase Database user. After you create a user, you can use it to connect to OceanBase Database.
Required privileges
You must have the global CREATE USER privilege to use the CREATE USER statement. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.
Syntax
CREATE USER [IF NOT EXISTS] {user [, user...]}
[REQUIRE {NONE | SSL | X509 | tls_option}]
[WITH resource_option [resource_option...]];
user:
user_name
| user_name IDENTIFIED [WITH auth_plugin] BY password
| user_name IDENTIFIED [WITH auth_plugin] BY PASSWORD password
tls_option:
CIPHER cipher_name
| ISSUER issuer_name
| SUBJECT subject_name
resource_option:
MAX_CONNECTIONS_PER_HOUR integer
| MAX_USER_CONNECTIONS integer
Parameters
| Parameter | Description |
|---|---|
| IF NOT EXISTS | Specifies not to create a user when the username already exists. If the username already exists and IF NOT EXISTS is not specified, an error is returned. |
| user_name | The username. For each user created, a new entry is created in the mysql.user table. To create multiple users at a time, use commas (,) to separate them. |
| auth_plugin | The user authentication method. Currently, only the mysql_native_password authentication plug-in is supported. |
| IDENTIFIED BY password | The password in plaintext for the user, which is then saved to the mysql.user table in ciphertext. If the password contains special characters, it must be enclosed in single or double quotation marks. Special characters are ~!@#%^&*_-+=`|(){}[]:;',.?/ |
| IDENTIFIED BY PASSWORD password | The password in ciphertext for the user, which is saved to the mysql.user table directly. |
| REQUIRE | The authentication requirements for the user. Valid values:
|
| tls_option | The options for TLS requirements. Valid values:
|
| resource_option | The resource options for the user. Separate multiple resource options with spaces.
|
Examples
Create users named test1 and test2, and specify passwords in plaintext and the maximum number of concurrent connections allowed per user.
obclient> CREATE USER IF NOT EXISTS test1 IDENTIFIED BY '********', test2 IDENTIFIED BY '********' WITH MAX_USER_CONNECTIONS 10;Create a user named test3, specify a password in ciphertext, and specify to use an SSL connection for authentication.
obclient> CREATE USER IF NOT EXISTS test3 IDENTIFIED BY PASSWORD '********' REQUIRE SSL;Create a user named test4 and specify to use the mysql_native_password authentication plug-in.
obclient> CREATE USER IF NOT EXISTS test4 IDENTIFIED WITH mysql_native_password BY PASSWORD '********';
References
For more information about how to grant user privileges, see Grant privileges.
You can query the information about the created user in the
mysql.usertable. For more information about themysql.usertable, see mysql.user.For more information about how to use the created user to connect to OceanBase Database, see Connect to OceanBase Database.