Updated on 2025-03-13 GMT+08:00

ALTER USER

Description

Alters the attributes of a database user.

Precautions

Session parameters modified by ALTER USER apply to a specified user and take effect in the next session.

Syntax

  • Modify user permissions or other information.
    1
    ALTER USER user_name [ [ WITH ] option [ ... ] ];
    

    The option clause is as follows:

     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
    { CREATEDB | NOCREATEDB }
        | { CREATEROLE | NOCREATEROLE }
        | { INHERIT | NOINHERIT }
        | { AUDITADMIN | NOAUDITADMIN }
        | { SYSADMIN | NOSYSADMIN }
        | {MONADMIN | NOMONADMIN}
        | {OPRADMIN | NOOPRADMIN}
        | {POLADMIN | NOPOLADMIN}
        | { USEFT | NOUSEFT }
        | { LOGIN | NOLOGIN }
        | { REPLICATION | NOREPLICATION }
        | {VCADMIN | NOVCADMIN}
        | {PERSISTENCE | NOPERSISTENCE}
        | CONNECTION LIMIT connlimit
        | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [EXPIRED] | DISABLE | EXPIRED }
        | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
        | VALID BEGIN 'timestamp'
        | VALID UNTIL 'timestamp'
        
        | USER GROUP 'groupuser'
        | PERM SPACE 'spacelimit'
        | TEMP SPACE 'tmpspacelimit'
        | SPILL SPACE 'spillspacelimit'
        | NODE GROUP logic_cluster_name
        | PGUSER
    
  • Change the username.
    1
    2
    ALTER USER user_name 
        RENAME TO new_name;
    
  • Lock or unlock.
    ALTER USER user_name
        ACCOUNT { LOCK | UNLOCK };

Parameters

  • user_name

    Specifies the current username.

    Value range: an existing username. If a username contains uppercase letters, enclose the name with double quotation marks ("").

  • new_password

    Specifies a new password.

    The new password must:

    • Differ from the old password.
    • Contain at least eight characters. This is the default length.
    • Differ from the username or the username spelled backward.
    • Contain at least three types of the following four types of characters: uppercase characters (A to Z), lowercase characters (a to z), digits (0 to 9), and special characters, including: ~!@#$%^&*()-_=+\|[{}];:,<.>/? If the password contains characters other than the preceding characters, an error will be reported during statement execution.

    Value range: a string.

  • old_password

    Specifies the old password.

  • ACCOUNT { LOCK | UNLOCK }
    • ACCOUNT LOCK: locks an account to forbid login to databases.
    • ACCOUNT UNLOCK: unlocks an account to allow login to databases.
  • PGUSER

    In the current version, the PGUSER attribute of a user cannot be modified.

For details about other parameters, see "Parameters" in CREATE ROLE and ALTER ROLE.

The current version does not support the setting of user-level parameters.

Examples

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

-- Change the login password of user jim. (Use the ALTER USER... IDENTIFIED BY... REPLACE... syntax to change the user password. The password before REPLACE is the new password, and the password after REPLACE is the original password.)
gaussdb=# ALTER USER jim IDENTIFIED BY '**********' REPLACE '********';

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

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

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

-- Unlock jim.
gaussdb=# ALTER USER jim ACCOUNT UNLOCK;

-- Change the username.
gaussdb=# ALTER USER jim RENAME TO lisa;

-- Delete the user.
gaussdb=# DROP USER lisa CASCADE;

Helpful Links

CREATE ROLE, CREATE USER, and DROP USER