Revoking privileges with the REVOKE statement

You can use the REVOKE statement to remove the privileges that you explicitly grant to an ID or a role.

Procedure

You can revoke the privilege that you grant to an ID by issuing the following statement:
Begin general-use programming interface information.
REVOKE authorization-specification FROM auth-id

Generally, you can revoke only the privileges that you grant. If you revoke privileges on a procedure or package, the privileges are revoked from all versions of that procedure or package.

However, an ID with the SECADM or ACCESSCTRL authority can revoke a privilege that has been granted by another ID with the following statement: 

REVOKE authorization-specification FROM auth-id BY auth-id

If the SEPARATE SECURITY system parameter on panel DSNTIPP1 is set to NO (the default) during installation, an ID with the SYSADM or SYSCTRL authority can revoke a privilege that has been granted by another ID. In this case, the SYSADM authority implicitly has the privileges of the SECADM authority, and the SYSCTRL authority implicitly has the privileges of the ACCESSCTRL authority.

The BY clause specifies the authorization ID that originally granted the privilege. If two or more grantors grant the same privilege to an ID, executing a single REVOKE statement does not remove the privilege. To remove it, each grant of the privilege must be revoked.

The WITH GRANT OPTION clause of the GRANT statement allows an ID to pass the granted privilege to others. If the privilege is removed from the ID, its revocation can cascade to others depending on the setting of the REVOKE DEP PRIV system parameter. For example, when a privilege is removed from authorization ID X, it is also removed from any ID to which X granted it, unless that ID also has the privilege from some other source.

Examples

Example
Suppose that DBA01 grants DBCTRL authority with the GRANT option on database DB1 to DBUTIL1. Then DBUTIL1 grants the CREATETAB privilege on DB1 to PGMR01. If DBA01 revokes DBCTRL from DBUTIL1, PGMR01 loses the CREATETAB privilege. If PGMR01 also granted the CREATETAB privilege to OPER1 and OPER2, they also lose the privilege.
Example
Suppose that PGMR01 from the preceding example created table T1 while holding the CREATETAB privilege. If PGMR01 loses the CREATETAB privilege, table T1 is not dropped, and the privileges that PGMR01 has as owner of the table are not deleted. Furthermore, the privileges that PGMR01 grants on T1 are not deleted. For example, PGMR01 can grant SELECT on T1 to OPER1 as long as PGMR01 owns of the table. Even when the privilege to create the table is revoked, the table remains, the privilege remains, and OPER1 can still access T1.
Example
Consider the following REVOKE scenario:
  1. Grant #1: SYSADM, SA01, grants SELECT on TABLE1 to USER01 with the GRANT option.
  2. Grant #2: USER01 grants SELECT on TABLE1 to USER02 with the GRANT option.
  3. Grant #3: USER02 grants SELECT on TABLE1 back to SA01.
  4. USER02 then revokes SELECT on TABLE1 from SA01.
The cascade REVOKE process of Grant #3 determines if SA01 granted SELECT to anyone else. It locates Grant #1. Because SA01 did not have SELECT from any other source, this grant is revoked. The cascade REVOKE process then locates Grant #2 and revokes it for the same reason. In this scenario, the single REVOKE action by USER02 triggers and results in the cascade removal of all the grants even though SA01 has the SYSADM authority. The SYSADM authority is not considered.
End general-use programming interface information.
1 Db2 does not cascade a revoke of the SYSADM authority from the installation SYSADM authorization IDs.