Updated on 2023-02-08 GMT+08:00

CREATE USER

Function

CREATE USER creates a user.

Important Notes

Syntax

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

The option clause is used for setting information including permissions and attributes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
{SYSADMIN | NOSYSADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | 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
    | AUTHINFO 'authinfo'
    | PASSWORD EXPIRATION period

Parameters

  • user_name

    Specifies the user name.

    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.

    A password must:

    • Contain at least eight characters. This is the default length.
    • Differ from the user name or the user name spelled backwards.
    • Contains at least three of the following four character types: uppercase letters, lowercase letters, digits, and special characters, including: ~!@#$%^&*()-_=+\|[{}];:,<.>/?. If you use characters other than the four types, a warning is displayed, but you can still create the password.
    • Be enclosed by single or double quotation marks.

    Value range: a string

For details on other parameters, see CREATE ROLE Parameter Description.

Example

Create user jim:

1
CREATE USER jim PASSWORD '{password}';

The following statements are equivalent to the above:

1
CREATE USER kim IDENTIFIED BY '{password}';

For a user having the Create Database permission, add the CREATEDB keyword:

1
CREATE USER dim CREATEDB PASSWORD '{password}';