||Oracle Tips by Burleson
When system privileges are passed to others
using the WITH ADMIN OPTION, revoking the system privileges from the
original user will not cascade. The system privileges granted to
others must be revoked directly. In contrast, when object privileges
are passed on to others using the WITH GRANT OPTION, they are
revoked when the grantor’s privileges are revoked.
It is important to remember that only object
privileges will cascade when revoked; system privileges will not.
When the WITH ADMIN OPTION or WITH GRANT
OPTION has been included in a grant to another user, it cannot be
revoked directly. You must revoke the privilege and then issue
another grant without the WITH ADMIN OPTION or WITH GRANT
The command-line syntax for revoking a
system privilege is:
REVOKE create table
FROM admin_Remote DBA;
In order to revoke an object privilege, you
must either be the owner of the object, have granted that privilege
to that user with the WITH GRANT OPTION, or have the GRANT ANY
PRIVILEGE system privilege.
You can revoke object and system privileges
with Server Manager or at the command line in SQL*Plus. The
command-line syntax for revoking an object privilege is:
See Code Depot
object privilege REFERENCES has been granted, you must specify
CASCADE CONSTRAINTS in order to drop the foreign-key constraints
that were created.