REVOKE (role) statement
This form of the REVOKE statement revokes roles from users, groups, or other roles.
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).
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
- 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).
- Specifies from whom the role is revoked.
- Specifies that the authorization-name identifies a user.
- Specifies that the authorization-name identifies a group.
- Specifies that the authorization-name identifies an existing role at the current server (SQLSTATE 42704).
- 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).
- 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.
- 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.
- 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.
- 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
REVOKE ROLE SPECIALIST FROM USER BOB, GROUP TORONTO BY ALL