CREATE USER

Function

CREATE USER 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

CreateUser ::= CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };

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

option ::= {SYSADMIN | NOSYSADMIN}
 | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

Parameter Description

user_name

Specifies the name of the user to be created.

Value range: a string. It must comply with the naming convention. A value can contain a maximum of 63 characters.
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 not advised to use it directly. If a ciphertext password is directly used, the user must know the plaintext corresponding to the ciphertext password and ensure the complexity of the plaintext password. The database does not verify the complexity of the ciphertext password. The security of the ciphertext password is ensured by the user.
Be enclosed by single or double quotation marks.

Value range: a string

For other parameters, see CREATE ROLE.

Examples

-- Create user jim whose login password is xxxxxxxxx:
MogDB=# CREATE USER jim PASSWORD 'xxxxxxxxx';

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

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

-- Change user jim's login password from xxxxxxxxx to Abcd@123:
MogDB=# ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'xxxxxxxxx';

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

-- Set enable_seqscan to on. (The setting will take effect in the next session.)
MogDB=# ALTER USER jim SET enable_seqscan TO on;

-- Reset the enable_seqscan parameter for jim.
MogDB=# ALTER USER jim RESET enable_seqscan;

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

-- Delete users.
MogDB=# DROP USER kim CASCADE;
MogDB=# DROP USER jim CASCADE;
MogDB=# DROP USER dim CASCADE;

Helpful Links

ALTER USER，CREATE ROLE，DROP USER

Issue