Purpose
The CREATE USER statement is used to create a new user in OceanBase Database. After creating a new user, 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 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 this option is specified, the user will not be created if the username already exists. If the username already exists and this option 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. You can create multiple users at a time by separating the usernames with commas (,). |
| auth_plugin | The authentication plugin to use for the user. Only the mysql_native_password plugin is supported. |
| IDENTIFIED BY password | The password for the user. The password is stored in plain text in the mysql.user table and will be encrypted by the server. If the password contains special characters ~!@#%^&*_-+=`|(){}[]:;',.?/, it must be enclosed in English quotation marks (' or "). |
| IDENTIFIED BY PASSWORD password | The password for the user. The password is stored in encrypted form 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 in the required format. |
| REQUIRE | Specifies the password verification requirements for the user. Valid values are as follows:
|
| tls_option | Specifies the specific TLS options. Valid values are as follows:
|
| resource_option | Specifies the resource options for the user. Multiple resource options can be specified, separated by spaces. Valid values are as follows:
|
Examples
Create users test1 and test2, specifying plain text 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.