REVOKE (role) statement

This form of the REVOKE statement revokes roles from users, groups, or other roles.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • The WITH ADMIN OPTION on the role
  • SECADM authority
SECADM authority is required to revoke the ADMIN OPTION FOR role-name from an authorization-name or to revoke a role-name from an authorization-name that has the WITH ADMIN OPTION on that role.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE ADMIN OPTION FOR ROLE ,role-name FROM ,USERGROUPROLEauthorization-namePUBLIC BY ALL

Description

ADMIN OPTION FOR
Revokes the WITH ADMIN OPTION on role-name. The WITH ADMIN OPTION on role-name must be held by authorization-name or by PUBLIC, if PUBLIC is specified (SQLSTATE 42504). If the ADMIN OPTION FOR clause is specified, only the WITH ADMIN OPTION on ROLE role-name is revoked, not the role itself.
ROLE role-name
Specifies the role that is to be revoked. The role-name must identify an existing role at the current server (SQLSTATE 42704) that has been granted to authorization-name or to PUBLIC, if PUBLIC is specified (SQLSTATE 42504).
FROM
Specifies from whom the role is revoked.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group.
ROLE
Specifies that the authorization-name identifies an existing role at the current server (SQLSTATE 42704).
authorization-name,...
Lists the authorization IDs of one or more users, groups, or roles. The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).
PUBLIC
Revokes the specified roles from PUBLIC.
BY ALL
Revokes the role-name from each specified authorization-name that was explicitly granted that role, regardless of who granted it. This is the default behavior.

Rules

  • For each authorization-name specified, if none of the keywords USER, GROUP, or ROLE is specified, then for all rows for the specified object in the SYSCAT.ROLEAUTH catalog view where the grantee is authorization-name:
    • If GRANTEETYPE is 'U', USER is assumed.
    • If GRANTEETYPE is 'G', GROUP is assumed.
    • If GRANTEETYPE is 'R', ROLE is assumed.
    • If GRANTEETYPE does not have the same value, an error is returned (SQLSTATE 56092.
  • The role-name must not identify a role, or a role that contains role-name, if the role has either EXECUTE privilege on a routine or USAGE privilege on a sequence, and an SQL object other than a package is dependent on the routine or sequence (SQLSTATE 42893). The owner of the SQL object is either authorization-name or any user that is a member of authorization-name, where authorization-name is a role.

Notes

  • If a role is revoked from an authorization-name or from PUBLIC, all privileges that the role held are no longer available to the authorization-name or to PUBLIC through that role.
  • Revoking a role does not necessarily revoke the ability to perform a particular action by way of a privilege that was granted to that role. A user might still be able to proceed if other privileges are held by PUBLIC, by a group to which the user belongs, by another role granted to the user, or if the user has a higher level authority, such as DBADM.

Examples

  • Example 1: Revoke the role INTERN from the role DOCTOR and the role DOCTOR from the role SPECIALIST.
       REVOKE ROLE INTERN FROM ROLE DOCTOR

   REVOKE ROLE DOCTOR FROM ROLE SPECIALIST
  • Example 2: Revoke the role INTERN from PUBLIC.
       REVOKE ROLE INTERN FROM PUBLIC
  • Example 3: Revoke the role SPECIALIST from user BOB and group TORONTO.
       REVOKE ROLE SPECIALIST FROM USER BOB, GROUP TORONTO BY ALL