GRANT object_privileges

Syntax

Purpose

To grant privileges for a particular object to users, roles, and PUBLIC. To grant system privileges and roles, use the GRANT system_privileges_and_roles statement described in the previous section of this chapter. Table 7-7 summarizes the object privileges that you can grant on each type of object.

If you grant a privilege to a user, Oracle adds the privilege to the user's privilege domain. The user can immediately exercise the privilege.

If you grant a privilege to a role, Oracle adds the privilege to the role's privilege domain. Users who have been granted and have enabled the role can immediately exercise the privilege. Other users who have been granted the role can enable the role and exercise the privilege.

If you grant a privilege to PUBLIC, Oracle adds the privilege to the privilege domain of each user. All users can immediately exercise the privilege.

Table 7-8 lists object privileges and the operations that they authorize. You can grant any of these system privileges with the GRANT statement.

For information on granting system privileges and roles, see "GRANT system_privileges_and_roles". For information on revoking object grants, see "REVOKE schema_object_privileges".

Prerequisites

You must own the object or the owner of the object must have granted you the object privileges with the GRANT OPTION. This rule applies to users with the DBA role.

Keywords and Parameters

object_priv	is an object privilege to be granted. You can substitute any of the values shown in Table 7-7. See also Table 7-8. Restriction: A privilege cannot appear more than once in the list of privileges to be granted.
ALL [PRIVILEGES]	grants all the privileges for the object that you have been granted with the GRANT OPTION. The user who owns the schema containing an object automatically has all privileges on the object with the GRANT OPTION. (The keyword PRIVILEGES is optional.)
column	specifies a table or view column on which privileges are granted. You can specify columns only when granting the INSERT, REFERENCES, or UPDATE privilege. If you do not list columns, the grantee has the specified privilege on all columns in the table or view.
ON	identifies the object on which the privileges are granted. Directory schema objects and Java source and resource schema objects are identified separately because they reside in separate namespaces.
	object	identifies the schema object on which the privileges are granted. If you do not qualify object with schema, Oracle assumes the object is in your own schema. The object can be one of the following types (see Table 7-7): table, view, or materialized view / snapshot sequence procedure, function, or package user-defined type synonym for any of the above items directory, library, operator, or indextype a Java source, class, or resource
		Note: You cannot grant privileges directly to a single partition of a partitioned table. For information on how to grant privileges to a single partition indirectly, refer to Oracle8i Concepts.
	DIRECTORY	identifies a directory schema object on which privileges are granted by the DBA. You cannot qualify directory_name with a schema name. See "CREATE DIRECTORY".
	JAVA SOURCE | RESOURCE	identifies a Java source or resource schema object on which privileges are granted. See "CREATE JAVA".
TO	identifies users or roles to which the object privilege is granted. Restriction: A user or role cannot appear more than once in the TO clause.
	PUBLIC	grants object privileges to all users.
WITH GRANT OPTION	allows the grantee to grant the object privileges to other users and roles. Restriction: You can specify this clause only when granting to a user or to PUBLIC, not when granting to a role.

Table 7-7  Object Privileges

Object Privilege	Table	View	Sequence	Procedures, Functions, Packagesa	Materialized View / Snapshot	Directory	Library	User- defined Type	Operator	Indextype
ALTER	X		X
DELETE	X	X			Xb
EXECUTE				X			X	X	X	X
INDEX	X
INSERT	X	X			Xb
READ						X
REFERENCES	X
SELECT	X	X	X		X
UPDATE	X	X			Xb
aOracle treats a Java class, source, or resource as if it were a procedure for purposes of granting object privileges. bThe DELETE, INSERT, and UPDATE privileges can be granted only to updatable materialized views.

Table 7-8  Object Privileges and the Operations They Authorize

Object Privilege	Allows Grantee to . . .
The following table privileges authorize operations on a table. Any one of following object privileges allows the grantee to lock the table in any lock mode with the LOCK TABLE statement.
ALTER	Change the table definition with the ALTER TABLE statement.
DELETE	Remove rows from the table with the DELETE statement. Note: You must grant the SELECT privilege on the table along with the DELETE privilege.
INDEX	Create an index on the table with the CREATE INDEX statement.
INSERT	Add new rows to the table with the INSERT statement.
REFERENCES	Create a constraint that refers to the table. You cannot grant this privilege to a role.
SELECT	Query the table with the SELECT statement.
UPDATE	Change data in the table with the UPDATE statement.
	Note: You must grant the SELECT privilege on the table along with the UPDATE privilege.
The following view privileges authorize operations on a view. Any one of the following object privileges allows the grantee to lock the view in any lock mode with the LOCK TABLE statement. To grant a privilege on a view, you must have that privilege with the GRANT OPTION on all of the view's base tables.
DELETE	Remove rows from the view with the DELETE statement.
INSERT	Add new rows to the view with the INSERT statement.
SELECT	Query the view with the SELECT statement.
UPDATE	Change data in the view with the UPDATE statement.
The following sequence privileges authorize operations on a sequence.
ALTER	Change the sequence definition with the ALTER SEQUENCE statement.
SELECT	Examine and increment values of the sequence with the CURRVAL and NEXTVAL pseudocolumns.
The following procedure, function, and package privilege authorizes operations on procedures, functions, or packages. This privilege also applies to Java sources, classes, and resources, which Oracle treats as though they were procedures for purposes of granting object privileges.
EXECUTE	Compile the procedure or function or execute it directly, or access any program object declared in the specification of a package.
	Note: Users do not need this privilege to execute a procedure, function, or package indirectly.
	See Also: Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.
The following snapshot privilege authorizes operations on a snapshot.
SELECT	Query the snapshot with the SELECT statement.
Synonym privileges are the same as the privileges for the base object. Granting a privilege on a synonym is equivalent to granting the privilege on the base object. Similarly, granting a privilege on a base object is equivalent to granting the privilege on all synonyms for the object. If you grant a user a privilege on a synonym, the user can use either the synonym name or the base object name in the SQL statement that exercises the privilege.
The following directory privilege provides secured access to the files stored in the operating system directory to which the directory object serves as a pointer. The directory object contains the full pathname of the operating system directory where the files reside. Because the files are actually stored outside the database, Oracle server processes also need to have appropriate file permissions on the file system server. Granting object privileges on the directory database object to individual database users, rather than on the operating system, allows Oracle to enforce security during file operations.
READ	Read files in the directory.
The following object type privilege authorizes operations on an object type
EXECUTE	Use and reference the specified object and to invoke its methods.
The following indextype privilege authorizes operations on indextypes.
EXECUTE	Reference an indextype.
The following operator privilege authorizes operations on user-defined operators.
EXECUTE	Reference an operator.

Examples

To grant READ on directory BFILE_DIR1 to user SCOTT, with the GRANT OPTION, issue the following statement:

GRANT READ ON DIRECTORY bfile_dir1 TO scott

WITH GRANT OPTION;

To grant all privileges on the table BONUS to the user JONES with the GRANT OPTION, issue the following statement:

GRANT ALL ON bonus TO jones 

WITH GRANT OPTION; 

JONES can subsequently perform the following operations:

• Exercise any privilege on the BONUS table

• Grant any privilege on the BONUS table to another user or role

To grant SELECT and UPDATE privileges on the view GOLF_HANDICAP to all users, issue the following statement:

GRANT SELECT, UPDATE 

ON golf_handicap TO PUBLIC; 

All users can subsequently query and update the view of golf handicaps.

To grant SELECT privilege on the ESEQ sequence in the schema ELLY to the user BLAKE, issue the following statement:

GRANT SELECT 

ON elly.eseq TO blake; 

BLAKE can subsequently generate the next value of the sequence with the following statement:

SELECT elly.eseq.NEXTVAL 

FROM DUAL; 

To grant BLAKE the REFERENCES privilege on the EMPNO column and the UPDATE privilege on the EMPNO, SAL, and COMM columns of the EMP table in the schema SCOTT, issue the following statement:

GRANT REFERENCES (empno), UPDATE (empno, sal, comm) 

ON scott.emp
TO blake; 

BLAKE can subsequently update values of the EMPNO, SAL, and COMM columns. BLAKE can also define referential integrity constraints that refer to the EMPNO column. However, because the GRANT statement lists only these columns, BLAKE cannot perform operations on any of the other columns of the EMP table.

For example, BLAKE can create a table with a constraint:

CREATE TABLE dependent 

(dependno   NUMBER, 
 dependname VARCHAR2(10), 
 employee   NUMBER 
CONSTRAINT in_emp REFERENCES scott.emp(empno) );

The constraint IN_EMP ensures that all dependents in the DEPENDENT table correspond to an employee in the EMP table in the schema SCOTT.