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 classification 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 | Indicates whether to create a user if the username already exists. If the username already exists and you do not specify IF NOT EXISTS, an error will be returned. |
| user_name | The username. After a new user is created, a new row will be added to the mysql.user table. You can create multiple users at a time. Separate the usernames with commas (,). |
| auth_plugin | The authentication method. Only the mysql_native_password authentication plugin is supported. |
| IDENTIFIED BY password | Specifies the password for the new user. The password is stored in plain text in the mysql.user table and is then encrypted by the server. If the password contains special characters ~!@#%^&*_-+=`|(){}[]:;',.?/, enclose it in double quotation marks (") or single quotation marks ('). |
| IDENTIFIED BY PASSWORD password | Specifies the password for the new 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 hash string, it is assumed to be in the required hash 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. When specifying multiple resource options, separate them with spaces. Valid values:
|
Examples
Create users test1 and test2, and specify the 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, specify the encrypted password, and require SSL connections for authentication.
obclient> CREATE USER IF NOT EXISTS test3 IDENTIFIED BY PASSWORD '********' REQUIRE SSL;Create user test4, and specify the mysql_native_password authentication plugin.
obclient> CREATE USER IF NOT EXISTS test4 IDENTIFIED WITH mysql_native_password BY PASSWORD '********';
References
For information about how to grant 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 how to connect to OceanBase Database using the created user, see Connect to OceanBase Database.