Object Privileges

Object privileges allow users to perform particular and controlled actions on specific objects. They allow for a finer grain of control with respect to the objects that can be accessed and the functions that can be performed with those objects. You can allow a user to SELECT, INSERT, UPDATE, and DELETE from just a single table.

The only two privileges that you can grant concerning the manipulation of an existing sequence are SELECT and ALTER.

Table 17.2 shows all the object privileges that you should know for the exam.

Table 17.2. Oracle 9i Object Privileges

Privilege	Description
SELECT	Permits grantee user to select data from granter's table
INSERT	Permits grantee user to insert data into granter's table
UPDATE	Permits grantee user to update rows in granter's table
DELETE	Permits grantee user to delete data from granter's table
ALTER	Permits grantee user to alter the definition of a table or sequence
INDEX	Permits grantee user to create an index on granter's table
REFERENCES	Permits grantee user to create or alter a table to create a foreign key constraint against the data in the referenced table
EXECUTE	Permits grantee user to run a stored procedure or function in the granter's schema

Further granularity of control over REFERENCES (creating foreign key constraints on a table outside a user's own schema) and INSERT can be restricted to specifying a certain subset of updatable columns. SELECT can be restricted further by the creation of a view on the allowable columns and granting SELECT on the view rather than on the table underlying the view.

A grant to any synonym is automatically converted to a grant on the table underlying that synonym. A grant on a view relates only to that view.

The GRANT statement is a little different for object privileges than it is for system privileges because you have to include the objects on which you are granting the privileges:

 GRANT UPDATE ON mydata TO amandya; GRANT SELECT ON addresses TO amandya WITH GRANT OPTION; 

As you can see, object privileges can also be granted WITH GRANT OPTION so that the user to whom they have been granted can further grant those same privileges to someone else.

Revoking privileges likewise requires the object on which the privilege was granted to be included in the revocation statement:

 REVOKE SELECT ON addresses FROM lonny; 

You can revoke privileges only from those users to whom you have granted privileges.

Unlike the WITH ADMIN option on system privileges, with object privileges, cascading effects can be observed when using the WITH GRANT OPTION.

For example, Lynn is granted UPDATE privileges on Lonny's address table WITH GRANT OPTION. She grants UPDATE privileges to Freeman. Later, the UPDATE privileges granted to Lynn are revoked, and that revoke is cascaded to Freeman as well.

Now that you know how to grant and revoke privileges on different levels to users in the database, you might find that you need to monitor the user actions in the database. The following section provides information on auditing.

Make sure that you can distinguish between object privileges and system privileges in Oracle. The easiest way to do this is to memorize what the object privileges are and to assume that everything else is a system privilege.