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.

  AlterUser ::= ALTER USER [IF EXISTS] user_name [ [ WITH ] option [ ... ] ];

  The option clause is as follows:

  option ::= { CREATEDB | NOCREATEDB }
      | { CREATEROLE | NOCREATEROLE }
      | { INHERIT | NOINHERIT }
      | { AUDITADMIN | NOAUDITADMIN }
      | { SYSADMIN | NOSYSADMIN }
      | {MONADMIN | NOMONADMIN}
      | {OPRADMIN | NOOPRADMIN}
      | {POLADMIN | NOPOLADMIN}
      | { USEFT | NOUSEFT }
      | { LOGIN | NOLOGIN }
      | { REPLICATION | NOREPLICATION }
      | {INDEPENDENT | NOINDEPENDENT}
      | {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'
      | RESOURCE POOL 'respool'
      | PERM SPACE 'spacelimit'
      | PGUSER

• Change the username.

  AlterUser ::= ALTER USER user_name
      RENAME TO new_name;

• Lock or unlock.

  AlterUser ::= ALTER USER user_name ACCOUNT { LOCK | UNLOCK };

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

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

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

  AlterUser ::= 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.

Helpful Links

CREATE ROLE, CREATE USER, DROP USER