Purpose
The CREATE USER statement is used to create a new OceanBase Database user. After a new user is created, you can use the new user to connect to OceanBase Database.
Privilege requirements
To execute the CREATE USER statement, the current user must have the global CREATE USER privilege. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible 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
| user_name IDENTIFIED [WITH auth_plugin] AS PASSWORD 'auth_string'
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 | If specified, the user will not be created if the username already exists. If the username already exists and IF NOT EXISTS is not specified, an error will be returned. |
| user_name | The username. After the user is created, a new row will be added to the mysql.user table. Multiple users can be created at the same time, separated by commas (,). |
| auth_plugin | The authentication plugin to use. Only mysql_native_password is supported. |
| IDENTIFIED BY password | Specifies the plaintext password for the new user. After the password is stored in the mysql.user table, it will be encrypted. If the password contains special characters ~!@#%^&*_-+=`|(){}[]:;',.?/, it must be enclosed in English quotes (' or "). |
| IDENTIFIED BY PASSWORD password | Specifies the encrypted password for the new user. The password will be directly stored in the mysql.user table. |
| IDENTIFIED AS PASSWORD | Sets the authentication plugin to auth_plugin and stores the auth_string value in the mysql.user table. If the plugin requires a hashed string, it is assumed to be already in the required hashed format. |
| REQUIRE | Specifies the password verification requirements for the user. Valid values:
|
| tls_option | Specifies the specific TLS requirements. Valid values:
|
| resource_option | Specifies the resource options for the user. Multiple resource options can be specified, separated by spaces. Valid values:
|
Examples
Create users test1 and test2, specifying plaintext passwords and the maximum number of connections allowed for each user.
obclient> CREATE USER IF NOT EXISTS test1 IDENTIFIED BY '********', test2 IDENTIFIED BY '********' WITH MAX_USER_CONNECTIONS 10;Create user test3, specifying an encrypted password and requiring SSL connections for authentication.
obclient> CREATE USER IF NOT EXISTS test3 IDENTIFIED BY PASSWORD '********' REQUIRE SSL;Create user test4, specifying the mysql_native_password authentication plugin.
obclient> CREATE USER IF NOT EXISTS test4 IDENTIFIED WITH mysql_native_password BY PASSWORD '********';
References
For information about granting privileges to a user, see Grant privileges.
You can view the created user information in the
mysql.usertable. For more information about themysql.usertable, see mysql.user.For information about connecting to OceanBase Database using the created user, see Connect to OceanBase Database.