Using Privileges in Oracle Database XE

Using Object Privileges

In contrast to a system privilege, an object privilege is a right to perform a particular type of action on a specific object, such as a table or a sequence that is not in the user’s own schema. As with system privileges, you use the GRANT and REVOKE commands to grant and revoke privileges on objects. Also with system privileges, you can grant object privileges to PUBLIC, and users with object privileges may pass them on to others by granting them with theWITH GRANT OPTIONclause.

A user with objects in his own schema automatically has all object privileges on those objects and can grant any object privilege on these objects to any user or another role, with or without theWITH GRANT OPTIONclause.

Table 31-8 shows the object privileges available for different types of objects; some privileges are only applicable to certain types of objects. For example, theINSERTprivilege only makes sense with tables, views, and materialized views; theEXECUTEprivilege, on the other hand, is applicable to functions, procedures, and packages, but not tables.

Table 31-8. Object Privileges   

Some of these object privileges overlap with system privileges. For example, if you don’t have theFLASHBACKobject privilege on a table, you can still perform flashback queries if you have theFLASHBACK ANY TABLE system privilege.

In the following example, the DBA grantsKELLYCfull access to the tableHR.EMPLOYEES, but only allowsKELLYCto pass on theSELECTobject privilege to other users:

SQL> grant insert, update, delete on hr.employees to kellyc;
Grant succeeded.
SQL> grant select on hr.employees to kellyc with grant option;
Grant succeeded.

Note that if theSELECT privilege on the tableHR.EMPLOYEESis revoked fromKELLYC, theSELECT privilege is also revoked from anyone she granted the privilege.