• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CREATE USER

CREATE USER

Function

CREATE USER creates a user. A user created using the CREATE USER statement has the LOGIN permission by default. A schema named after the user is automatically created in the database where the statement is run, but not in other databases. You can run the CREATE SCHEMA statement to create such a schema for the user in other databases.

Precautions

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 [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' | DISABLE };

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

{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'
    | 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

Parameter Description

  • user_name

    Specifies the user name.

    Value range: A string. It must comply with the naming convention rule.

    NOTE:

    The database user name 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 backward.
    • Contain at least three of the following: uppercase characters (A to Z), lowercase characters (a to z), digits (0 to 9), and other characters (limited to ~!@#$%^&*()-_=+\|[{}];:,<.>/?).
    • Be enclosed by single or double quotation marks.

    Value range: A string.

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

Examples

-- Create user jim whose login password is Bigdata123@:
CREATE USER jim PASSWORD 'Bigdata123@';

-- The following statements are equivalent to the above.
CREATE USER kim IDENTIFIED BY 'Bigdata123@';

--For a user having Create Database permission, add the CREATEDB key word:
CREATE USER dim CREATEDB PASSWORD 'Bigdata123@';

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

-- Add the CREATEROLE permission to user jim:
ALTER USER jim CREATEROLE;

-- Set enable_seqscan to on (the setting will take effect in the next session):
ALTER USER jim SET enable_seqscan TO on;

-- Reset the enable_seqscan parameter for user jim:
ALTER USER jim RESET enable_seqscan;

-- Lock the jim account:
ALTER USER jim ACCOUNT LOCK;

-- Delete the user:
DROP USER kim CASCADE;
DROP USER jim CASCADE;
DROP USER dim CASCADE;

Helpful Links

ALTER USER, CREATE ROLE, DROP USER