ALTER USER

Function

ALTER USER modifies 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.

  ALTER USER user_name [ [ WITH ] option [ ... ] ];

  The option clause is as follows:

  { CREATEDB | NOCREATEDB }
      | { CREATEROLE | NOCREATEROLE }
      | { INHERIT | NOINHERIT }
      | { AUDITADMIN | NOAUDITADMIN }
      | { SYSADMIN | NOSYSADMIN }
      | { USEFT | NOUSEFT }
      | { LOGIN | NOLOGIN }
      | { REPLICATION | NOREPLICATION }
      | {INDEPENDENT | NOINDEPENDENT}
      | {VCADMIN | NOVCADMIN}
      | 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'
      | RESOURCE POOL 'respool'
      | PERM SPACE 'spacelimit'
      | ACCOUNT { LOCK | UNLOCK }
      | PGUSER

• Change the username.

  ALTER USER user_name
      RENAME TO new_name;

• Change the value of a specified parameter associated with the user.

  ALTER USER user_name
      SET configuration_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT };

• Reset the value of a specified parameter associated with the user.

  ALTER USER user_name
      RESET { configuration_parameter | ALL };

Parameter Description

• user_name

  Specifies the current username.

  Value range: an existing username

• 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: ~!@#$%^&*()-_=+|[{}];:,<.>/?

    Value range: a string

• old_password

  Specifies the old password.

• ACCOUNT LOCK | ACCOUNT 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 "Parameter Description" in CREATE ROLE and ALTER ROLE.

Example

See Example in CREATE USER.