All Products
Search
Document Center

CREATE USER

Last Updated: Jun 18, 2021

Description

You can execute the CREATE USER statement to create a user in ApsaraDB for OceanBase. After a user is created, you can use the user to connect to ApsaraDB for OceanBase.

Note

To execute the CREATE USER statement, ensure you have the global CREATE USER permission.

Syntax

create_user_stmt:
    CREATE USER [IF NOT EXISTS] user_name [IDENTIFIED BY 'password'];

alter_user_stmt:
      ALTER USER user_name ACCOUNT {LOCK | UNLOCK};
    | ALTER USER user_name IDENTIFIED BY 'password';
    | SET PASSWORD [FOR user_name] = PASSWORD('password');
    | RENAME USER rename_user_action_list;

drop_user_stmt:
    DROP USER user_name_list;

rename_user_action_list:
    rename_user_action [, rename_user_action ...]

rename_user_action:
    user_name TO user_name

user_name_list:
    user_name [, user_name ...]

password:
    STR_VALUE
    
CREATE USER [IF NOT EXISTS] user_specification_list;

user_specification_list:
user_specification [, user_specification ...]

user_specification:
user IDENTIFIED BY 'authstring'
| user IDENTIFIED BY PASSWORD 'hashstring'

Parameters

Parameter

Description

user_name

The username. After a user is created, a new row is added for the user to the mysql.user table. If the username is used, the system returns an error.

IDENTIFIED BY

Set a password for the user.

user_name [, user_name ...]

To create multiple users at a time, separate them with commas (,).

user IDENTIFIED BY 'authstring'

The plaintext password. After the password is saved to the mysql.user table, the password is stored in ciphertext on the server.

user IDENTIFIED BY PASSWORD 'hashstring'

The ciphertext password.

Examples

  1. Create users sqluser01 and sqluser02, and set their password to 123456.
CREATE USER 'sqluser01' IDENTIFIED BY '123456', 'sqluser02' IDENTIFIED BY '123456';
  1. View the users you create.
SELECT user FROM mysql.user;

Output:

mysql> CREATE USER 'sqluser01' IDENTIFIED BY '123456', 'sqluser02' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.12 sec)
mysql> select user from mysql.user;
+-----------+
| user      |
+-----------+
| root      |
| admin     |
| sqluser01 |
| sqluser02 |
+-----------+
4 rows in set (0.00 sec)