CREATE USER

Description

Creates a user.

Precautions

A user created using the CREATE USER statement has the LOGIN permission by default.
When you run the CREATE USER command to create a user, the system creates a schema with the same name as the user in the database where the command is executed.
The owner of an object created by a system administrator in a schema with the same name as a common user is the common user, not the system administrator.

Syntax

CREATE USER user_name [ [ WITH ] option [ ... ] ]{ PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };

The option clause is used to configure information, including permissions and properties.

{SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | USER GROUP 'groupuser'
    | NODE GROUP logic_cluster_name
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

Parameters

user_name
Specifies the username.

Value range: a string of up to 63 characters in compliance with Identifier Description. If the value contains more than 63 characters, the database truncates it and retains the first 63 characters as the username. When a user is created, the database will display a message. The username can be enclosed in backquote (`). When sql_mode is set to ANSI_QUOTES, the role name can also be enclosed in double quotation marks ("). Usernames that are not enclosed in backquote or double quotation marks will be converted to lowercase letters.

The identifier must be letters, underscores (_), digits (0-9), or dollar signs ($) and must start with a letter (a-z) or underscore (_).
password
Specifies the login password.

The new password must:
- Contain at least eight characters. This is the default length.
- Differ from the username or the username spelled backward.
- Contain at least three of the following character types: uppercase characters, lowercase characters, digits, and special characters (limited to ~!@#$ %^&*()-_=+\|[{}];:,<.>/?).
- The password can also be a ciphertext character string that meets the format requirements. This mode is mainly used to import user data. You are advised not to use it directly. If a ciphertext password is used, the user must know the plaintext corresponding to the ciphertext password and ensure that the password meets the complexity requirements. The database does not verify the complexity of the ciphertext password. Instead, the security of the ciphertext password is ensured by the user.
- Be enclosed by single quotation marks when a user is created.
Value range: a string

For other parameters, see CREATE ROLE.

Examples

-- Create user jim whose login password is ********.
m_db=# CREATE USER jim PASSWORD '********';

-- Alternatively, you can run the following statement:
m_db=# CREATE USER kim IDENTIFIED BY '********';

-- To create a user with the CREATEDB permission, add the CREATEDB keyword.
m_db=# CREATE USER dim CREATEDB PASSWORD '********';

-- Change the login password of user jim from ******** to **********.
m_db=# ALTER USER jim IDENTIFIED BY '**********' REPLACE '********';

-- Add the CREATEROLE permission to jim.
m_db=# ALTER USER jim CREATEROLE;

-- Lock jim.
m_db=# ALTER USER jim ACCOUNT LOCK;

-- Delete the user.
m_db=# DROP USER kim CASCADE;
m_db=# DROP USER jim CASCADE;
m_db=# DROP USER dim CASCADE;