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.
About this task
You
can revoke the privilege that you grant to an ID by issuing the following
statement:
REVOKE authorization-specification FROM auth-idGenerally, 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-idIf 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.
- Grant #1: SYSADM, SA01, grants SELECT on TABLE1 to USER01 with the GRANT option.
- Grant #2: USER01 grants SELECT on TABLE1 to USER02 with the GRANT option.
- Grant #3: USER02 grants SELECT on TABLE1 back to SA01.
- USER02 then revokes SELECT on TABLE1 from SA01.