Lesson 8	Revoking system privileges
Objective	Revoke privileges from a user.

Revoking System Privileges

You can revoke a user's system privileges by issuing the REVOKE command. The following example shows the CREATE USER and CREATE SESSION privileges being revoked from the user named JEFF:

REVOKE CREATE USER, CREATE SESSION FROM jeff;

When you revoke a user's privilege, the revocation takes effect immediately. This is true even if the user is currently connected.

Format for grant Command

Here is the general format for the grant command for system privileges (see the Alphabetical Reference for the full syntax diagram):

grant {system privilege | role | all [privileges] }
[, {system privilege | role | all [privileges] }. . .]
to {user | role} [, {user | role}]. . .
[identified by password ]
[with admin option]

You can grant any system privilege or role to another user, to another role, or to public. The with admin option clause permits the grantee to bestow the privilege or role on other users or roles. The all clause grants the user or role all privileges except the SELECT ANY DICTIONARY system privilege. The grantor can revoke a role from a user as well.

Revoking Privileges

Privileges granted can be taken away. The revoke command is similar to the grant command:

revoke {system privilege | role | all [privileges] }
[, {system privilege | role | all [privileges] }. . .]
from {user | role} [, {user | role}]. . .

An individual with the DBA role can revoke CONNECT, RESOURCE, DBA, or any other privilege or role from anyone, including another DBA. This, of course, is dangerous, and is why DBA privileges should be given neither lightly nor to more than a tiny minority who really need them.

Note: Revoking everything from a given user does not eliminate that user from Oracle, nor does it destroy any tables that user had created; it simply prohibits that user’s access to them. Other users with access to the tables will still have exactly the same access they’ve always had.
To remove a user and all the resources owned by that user, use the drop user command, like this:

drop user username [cascade];

The cascade option drops the user along with all the objects owned by the user, including referential integrity constraints. The cascade option invalidates views, synonyms, stored procedures, functions, or packages that refer to objects in the dropped user’s schema. If you don’t use the cascade option and there are still objects owned by the user, Oracle does not drop the user and instead returns an error message.