Lesson 9

In this module you learned about system privileges and now should understand what a system privilege is, and should have a good idea of the various types of privileges that you can grant or have. You also should be able to perform the following tasks:

1. Define the two dominant types of privileges supported by Oracle
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. uery the data dictionary for information about system privileges

If you have access to the Oracle documentation, it would be well worth your time to read up on the system privileges that are available so that you understand what each one does. You'll find this information in the SQL Reference manual, in the section describing the GRANT command. There are about eight pages devoted to describing the various privileges. Read through them, so that someday when you need one, you will know that it exists.

In an Oracle database, privileges control access to both the actions a user can perform and the objects in the database. Privileges that control access to actions in the database are called system privileges, whereas privileges that control access to data and other objects are called object privileges. To make assignment and management of privileges easier for the DBA, a database role groups privileges together. To put it another way, a role is a named group of privileges. In addition, a role can itself have roles assigned to it. Privileges and roles are granted and revoked with the GRANT and REVOKE commands. The user group PUBLIC is neither a user nor a role, nor can it be dropped; however, when privileges are granted to PUBLIC, they are granted to every user of the database, both present and future.

System privileges grant the right to perform a specific type of action in the database, such as creating users, altering tablespaces, or dropping any view. Here is an example of granting a system privilege:

grant drop any table to scott
with admin option;

Object privileges are granted on a specific object in the database. The most common object privileges are SELECT, UPDATE, DELETE, and INSERT for tables, EXECUTE for a PL/SQL stored object, and INDEX for granting index-creation privileges on a table. In the following example, the user RJB can perform any DML on the JOBS table owned by the HR schema: The next module examines how to maximize user profiles.