Alter Role

Valid in: SQL, ESQL

The Alter Role statement changes the attributes associated with a role identifier.

Use add privileges to give the user additional privileges. Use drop privileges to remove privileges from the user. You cannot use either add privileges, or drop privileges if with_option is specified in the with_clause.

Syntax

The Alter Role statement has the following format:

[EXEC SQL] ALTER ROLE role_id {, role_id}
[ADD PRIVILEGES ( priv {,priv} ) | DROP PRIVILEGES ( priv {,priv} )]
[WITH with_option {,with_option}]

with_option = NOPASSWORD | PASSWORD = 'role_password' | EXTERNAL_PASSWORD
  | NO PRIVILEGES | PRIVILEGES = ( priv {,priv} )
                       | NOSECURITY_AUDIT | SECURITY_AUDIT

• role_id

  Must exist in the installation. If one or more of the specified role identifiers do not exist, the DBMS Server issues a warning, but all valid role identifiers are processed.

  To create roles, use the Create Role statement. For more information about role identifiers, see the Database Administrator Guide.

• priv

  Specifies one of the following subject privileges, which apply to the user regardless of the database to which the user is connected. If the privileges clause is omitted, the default is NOPRIVILEGES.

  • CREATEDB

    Allows users to create databases.

  • TRACE

    Allows the user to use tracing and debugging features.

  • SECURITY

    Allows the user to perform security-related functions (such as creating and dropping users).

  • OPERATOR

    Allows the user to perform database backups and other database maintenance operations.

  • MAINTAIN_LOCATIONS

    Allows the user to create and change the characteristics of database and file locations.

  • AUDITOR

    Allows the user to register or remove audit logs and to query audit logs.

  • MAINTAIN_AUDIT

    Allows the user to change the alter user security audit and alter profile security audit privileges. Also allows the user to enable, disable, or alter security audit.

  • MAINTAIN_USERS

    Allows the user to perform various user-related functions, such as creating or altering users, profiles, group and roles, and to grant or revoke database and installation resource controls.

• NOSECURITY_AUDIT | SECURITY_AUDIT

  Specifies audit options, as follows:

  • NOSECURITY_AUDIT

    Uses the security_audit level for the user using the role.

  • SECURITY_AUDIT

    Audits all activity for anyone who uses the role, regardless of any SECURITY_AUDIT level set for an individual user.

    Default: NOSECURITY_AUDIT if the security_audit clause is omitted.

    Caution! If no password is specified, any session has access to the specified role identifier and its associated permissions.

Embedded Usage

In an embedded Alter Role statement, the preprocessor does not validate the syntax of the with clause.

Permissions

You must have maintain_users privileges and be connected to the iidbdb database. Additional privileges are required to perform certain operations, as summarized in the table below:

Locking

The Alter Role statement locks pages in the iirole catalog of the iidbdb. This can cause sessions attempting to connect to the server to suspend until the statement is completed.

Related Statements

Create Role

Drop Role

Examples: Alter Role

The following examples change the attributes associated with a role identifier:

1. Change the password for the role identifier, new_accounts, to eggbasket.

   alter role new_accounts with
   password = 'eggbasket';

2. Remove the password associated with the identifier, chk_inventory.

   alter role chk_inventory with nopassword;

3. In an application, change the password for the role identifier, mon_end_report to goodnews.

   exec sql alter role mon_end_report with
   password = goodnews;

4. Alter a role to remove a privilege and audits all activity performed when the role is active.

   alter role sysdba
   drop privileges (trace)
   with security_audit;