ALTER ROLE

Function

ALTER ROLE modifies role attributes.

Precautions

None

Syntax

• Modify the permissions of a role.

  AlterRole ::= ALTER ROLE role_name [ [ WITH ] option [ ... ] ];

  The option clause for granting permissions 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]
        | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY 'password' [ REPLACE 'old_password' | EXPIRED ]
        | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' | DISABLE | EXPIRED }
        | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' ] | DISABLE }
        | VALID BEGIN 'timestamp'
        | VALID UNTIL 'timestamp'
        | RESOURCE POOL 'respool'
        | PERM SPACE 'spacelimit'
        | PGUSER

• Rename a role.

  AlterRole ::= ALTER ROLE role_name
      RENAME TO new_name;

• Lock or unlock.

  AlterRole ::= ALTER ROLE role_name ACCOUNT { LOCK | UNLOCK };

• Set parameters for a role.

  AlterRole ::= ALTER ROLE role_name [ IN DATABASE database_name ]
      SET configuration_parameter {{ TO | = } { value | DEFAULT } | FROM CURRENT};

• Reset parameters for a role.

  AlterRole ::= ALTER ROLE role_name
      [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};

Parameter Description

• role_name

  Specifies a role name.

  Value range: an existing username

• IN DATABASE database_name

  Modifies the parameters of a role in a specified database.

• SET configuration_parameter

  Sets parameters for a role. Session parameters modified by ALTER ROLE apply to a specified role and take effect in the next session triggered by the role.

  Value range:

  For details about the values of configuration_parameter and value, see SET.

  DEFAULT: clears the value of configuration_parameter. configuration_parameter will inherit the default value of the new session generated for the role.

  FROM CURRENT: uses the value of configuration_parameter of the current session.

• RESET configuration_parameter/ALL

  Clears the value of configuration_parameter. The statement has the same effect as that of SET configuration_parameter TO DEFAULT.

  Value range: ALL indicates that the values of all parameters are cleared.

• 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 role cannot be modified.

• PASSWORD/IDENTIFIED BY 'password'

  Resets or changes the user password. Except the initial user, other administrators and common users need to enter the correct old password when changing their own passwords. Only the initial user, the system administrator (sysadmin), or users who have the permission to create users (CREATEROLE) can reset the password of a common user without entering the old password. The initial user can reset passwords of system administrators. System administrators cannot reset passwords of other system administrators.

• EXPIRED

  Invalidates the password. Only initial users, system administrators (sysadmin), and users who have the permission to create users (CREATEROLE) can invalidate user passwords. System administrators can invalidate their own passwords or the passwords of other system administrators. The password of the initial user cannot be invalidated.

  The user whose password is invalid can log in to the database but cannot perform the query operation. The query operation can be performed only after the password is changed or the administrator resets the password.

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

Example

See Example in CREATE ROLE.

Helpful Links

CREATE ROLE，DROP ROLE，SET