Lesson 1

In the previous module, you learned how to create users, and how to grant the CREATE SESSION privilege so that they could connect to the database. Privileges such as CREATE SESSION are referred to as system privileges. There are system privileges to control virtually every aspect of a user's interaction with the database. In this module, you will learn about additional privileges and how to manage them. When you are finished, you will be able to:

1. Explain the difference between system privileges and object privileges
2. Define the different types of system privileges
3. Grant and revoke privileges using the GRANT and REVOKE commands
4. Grant system privileges to all users
5. Allow other users to manage system privileges
6. Query the data dictionary for information about system privilegess

The DBA (database administrator) role has all system privileges including

1. unlimited space quotas and
2. the ability to grant all privileges to other users.

In this modules, DBA refers to the person who is the database administrator and has the DBA role, while DBA refers just to those privileges encompassed by the DBA role. SYSTEM is for use by a DBA user. Some of the rights that are reserved for the dba are never given to, or needed by, normal users. Little time will be spent here on those rights. Other rights typically used by dbas are also regularly used by and important to users. This subset of DBA privileges will be explained shortly. In Oracle, the DBA is granted the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, which in turn have privileges necessary for exporting and importing the full Oracle database.

revoke { object privilege | all [privileges]}
[(column [, column]. . . )]
[, { object privilege | all [privileges]}
[(column [, column]. . . )]]. . .]
on object
from {user | role} [,{user | role}]
[cascade constraints] [force];

revoke all

removes any of the privileges listed previously, from SELECT through INDEX. Revoking individual privileges will leave other privileges intact which had also been granted. The

with grant option 

is revoked along with the privilege to which it was attached. The revoke then cascades so users who received their access by means of the "with grant option" user also have their privileges revoked.
If a user defines referential integrity constraints on the object, Oracle drops these constraints if you revoke privileges on the object using the cascade constraints option. The force option applies to object types and revokes the EXECUTE privilege on object types with

1. table or
2. type dependencies.

All dependent objects will be marked as invalid and the data in dependent tables will become inaccessible until the necessary privilege is re-granted.