Difference of Revoke System Privileges and Object Privileges

system privelege :

grant ... with admim option

revoke ... Not cascade

object privilege :

grant ... with grant optin

revoke ... cascade

[@more@]Revoking System Privileges There are no cascading effects when a system privilege is revoked, regardless of whether it was given the ADMIN OPTION.

Read through the following steps that illustrate this.

Scenario

1. The DBA grants the CREATE TABLE system privilege to Jeff with the ADMIN OPTION.

2. Jeff creates a table.

3. Jeff grants the CREATE TABLE system privilege to Emi.

4. Emi creates a table.

5. The DBA revokes the CREATE TABLE system privilege from Jeff.

The result

Jeff’s table still exists, but no new tables can be created.

Emi’s table still exists and she still has the CREATE TABLE system privilege.

Revoking Object Privileges

Cascading effects can be observed when revoking a system privilege that is related to a DML operation. For example, if the SELECT ANY TABLE privilege is granted to a user, and that user has created procedures that use the table, all procedures that are contained in the user’s schema must be recompiled before they can be used again.

Revoking object privileges will also cascade when given WITH GRANT OPTION.

Read through the following steps that illustrate this.

Scenario

• Jeff is granted the SELECT object privilege on EMPLOYEES with the GRANT OPTION.

• Jeff grants the SELECT privilege on EMPLOYEES to Emi.

• Later, the SELECT privilege is revoked from Jeff. This revoke is cascaded to Emi as well.