Revoking dependent privileges

Revoking a privilege or authority, such as the SYSADM authority, from one user (an authorization ID or role) can result in the automatic removal of that privilege from other users and the privileges that it has granted. To prevent this, you can assign the REVOKE DEP PRIV parameter different values to control whether or not dependent privileges or authorities should be removed.

To specify the REVOKE DEP PRIV parameter, use one of the following approaches:

• Set REVOKE DEP PRIV to SQLSTMT (the default) if you want to use the dependent privileges clause on the REVOKE statement to control the revocation of dependent privileges.
  • Specify the NOT INCLUDING DEPENDENT PRIVILEGES clause on the REVOKE statement when you need to revoke a privilege or authority from a user but retain all the grants that are already made by that user. However, if the same privilege is later granted to that user again and subsequently revoked with the INCLUDING DEPENDENT PRIVILEGES clause specified, all dependent privileges including the grants made by the user earlier are removed.
  • Specify the INCLUDING DEPENDENT PRIVILEGES clause (the default) when you need to revoke a privilege or authority (other than ACCESSCTRL, DATAACCESS, and system DBADM) from a user and remove all the privileges or authorities that are already granted by that privilege or authority.
• Set REVOKE DEP PRIV to YES if you want to remove all dependent privileges or authorities whenever you revoke a privilege or authority other than ACCESSCTRL, DATAACCESS, and system DBADM.

  You will receive an error if you specify the NOT INCLUDING DEPENDENT PRIVILEGES clause on the REVOKE statement when you revoke a privilege or authority other than ACCESSCTRL, DATAACCESS, and system DBADM.

• Set REVOKE DEP PRIV to NO if you want to retain all dependent privileges or authorities whenever you revoke a privilege or authority.

  You will receive an error if you specify the INCLUDING DEPENDENT PRIVILEGES clause on the REVOKE statement.