In this seventh part of a ten-part series on the security features of Oracle Database XE, you'll learn how to use object privileges, table privileges, and more. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
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
Object Privilege
Capability
ALTER
Can alter a table or sequence definition.
DELETE
Can delete rows from a table, view, or materialized view.
EXECUTE
Can execute a function or procedure, with or without a package.
DEBUG
Can allow access to PL/SQL code in triggers defined on a table, or SQL statements that reference a table. For object types, this privilege allows access to all public and private variables, methods, and types defined on the object type.
FLASHBACK
Can allow flashback queries on tables, views, and materialized views using retained undo information
INDEX
Can create an index on a table.
INSERT
Can insert rows into a table, view, or materialized view.
ON COMMIT REFRESH
Can create a refresh-on-commit materialized view based on a table.
QUERY REWRITE
Can create a materialized view that can be used by Oracle to rewrite a query based on a table.
READ
Can read the contents of an operating system directory using an Oracle DIRECTORYdefinition.
REFERENCES
Can create a foreign key constraint that references another table’s primary key or unique key.
SELECT
Can read rows from a table, view, or materialized view, in addition to reading current or next values from a sequence.
UNDER
Can create a view based on an existing view.
UPDATE
Can update rows in a table, view, or materialized view.
WRITE
Can write information to an operating system directory using an Oracle DIRECTORYdefinition.
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.
