SQL Statements: DROP SEQUENCE to ROLLBACK, 13 of 20

GRANT

Purpose

Use the GRANT statement to grant:

System privileges to users and roles
Roles to users and roles. Both privileges and roles are either local, global, or external. Table 16-1 lists the system privileges (organized by the database object operated upon). Table 16-2 lists Oracle predefined roles.

Object privileges for a particular object to users, roles, and PUBLIC. Table 16-3 summarizes the object privileges that you can grant on each type of object. Table 16-4 lists object privileges and the operations that they authorize.

Note:

You can authorize database users to use roles through means other than the database and the GRANT statement. For example, some operating systems have facilities that let you grant roles to Oracle users with the initialization parameter OS_ROLES. If you choose to grant roles to users through operating system facilities, you cannot also grant roles to users with the GRANT statement, although you can use the GRANT statement to grant system privileges to users and system privileges and roles to other roles.

See Also:

CREATE USER and CREATE ROLE for definitions of local, global, and external privileges
Oracle9i Database Administrator's Guide for information about other authorization methods
REVOKE for information on revoking grants

Additional Topics

Prerequisites

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege.

To grant a role, you must either have been granted the role with the ADMIN OPTION or have been granted the GRANT ANY ROLE system privilege, or you must have created the role.

To grant an object privilege, 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.

Syntax

grant::=

Text description of grant

grant_system_privileges::=

Text description of statements_919.gif follows

Text description of grant_system_privileges

grant_object_privileges::=

Text description of statements_920.gif follows

Text description of grant_object_privileges

on_object_clause::=

Text description of statements_921.gif follows

Text description of on_object_clause

grantee_clause::=

Text description of statements_922.gif follows

Text description of grantee_clause

Keywords and Parameters

grant_system_privileges

system_privilege

Specify the system privilege you want to grant. Table 16-1 lists the system privileges (organized by the database object operated upon).

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 domains of each user. All users can immediately perform operations authorized by the privilege.

Oracle provides a shortcut for specifying all system privileges at once:

ALL PRIVILEGES: Specify ALL PRIVILEGES to grant all the system privileges listed in Table 16-1, " System Privileges", except the SELECT ANY DICTIONARY privilege.

role

Specify the role you want to grant. You can grant an Oracle predefined role or a user-defined role. Table 16-2 lists the predefined roles.

If you grant a role to a user, Oracle makes the role available to the user. The user can immediately enable the role and exercise the privileges in the role's privilege domain.
If you grant a role to another role, Oracle adds the granted role's privilege domain to the grantee role's privilege domain. Users who have been granted the grantee role can enable it and exercise the privileges in the granted role's privilege domain.
If you grant a role to PUBLIC, Oracle makes the role available to all users. All users can immediately enable the role and exercise the privileges in the roles privilege domain.

See Also:
CREATE ROLE for information on creating a user-defined role

IDENTIFIED BY Clause

Use the IDENTIFIED BY clause to specifically identify an existing user by password or to create a nonexistent user. This clause is not valid if the grantee is a role or PUBLIC. If the user specified in the grantee_clause does not exist, Oracle creates the user with the password and with the privileges and roles specified in this clause.

See Also:

CREATE USER for restrictions on usernames and passwords

WITH ADMIN OPTION

Specify WITH ADMIN OPTION to enable the grantee to:

Grant the role to another user or role, unless the role is a GLOBAL role
Revoke the role from another user or role
Alter the role to change the authorization needed to access it
Drop the role

If you grant a system privilege or role to a user without specifying WITH ADMIN OPTION, and then subsequently grant the privilege or role to the user WITH ADMIN OPTION, the user has the ADMIN OPTION on the privilege or role.

To revoke the ADMIN OPTION on a system privilege or role from a user, you must revoke the privilege or role from the user altogether and then grant the privilege or role to the user without the ADMIN OPTION.

grantee_clause

TO grantee_clause identifies users or roles to which the system privilege, role, or object privilege is granted.

Restriction: A user, role, or PUBLIC cannot appear more than once in TO grantee_clause.

PUBLIC

Specify PUBLIC to grant the privileges to all users.

Restrictions on granting system privileges and roles:

A privilege or role cannot appear more than once in the list of privileges and roles to be granted.
You cannot grant a role to itself.
You cannot grant a role IDENTIFIED GLOBALLY to anything.
You cannot grant a role IDENTIFIED EXTERNALLY to a global user or global role.
You cannot grant roles circularly. For example, if you grant the role banker to the role teller, you cannot subsequently grant teller to banker.

grant_object_privileges

object_privilege

Specify the object privilege you want to grant. You can specify any of the values shown in Table 16-3. See also Table 16-4.

Restriction: A privilege cannot appear more than once in the list of privileges to be granted.

ALL [PRIVILEGES]

Specify ALL to grant 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 provided for semantic clarity and is optional.)

column

Specify the table or view column on which privileges are to be 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.

For information on existing column object grants, query the USER_,ALL_, and DBA_COL_PRIVS data dictionary view.

See Also:

Oracle9i Database Reference for information on the data dictionary views

on_object_clause

The on_object_clause 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.

WITH GRANT OPTION

Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.

Restriction: You can specify WITH GRANT OPTION only when granting to a user or to PUBLIC, not when granting to a role.

WITH HIERARCHY OPTION

Specify WITH HIERARCHY OPTION to grant the specified object privilege on all subobjects of object, including subobjects created subsequent to this statement (such as subviews created under a view).

Note:

This clause is meaningful only in combination with the SELECT object privilege.

object

Specify the schema object on which the privileges are to be 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:

Table, view, or materialized view
Sequence
Procedure, function, or package
User-defined type
Synonym for any of the above items
Directory, library, operator, or indextype

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 Oracle9i Database Concepts.

DIRECTORY directory_name

Specify a directory schema object on which privileges are to be granted. You cannot qualify directory_name with a schema name.

See Also:

CREATE DIRECTORY

JAVA SOURCE | RESOURCE

The JAVA clause lets you specify a Java source or resource schema object on which privileges are to be granted.

See Also:

CREATE JAVA

Listings of System and Object Privileges

Table 16-1 System Privileges

System Privilege Name	Operations Authorized
Note: When you grant a privilege on "`ANY`" object (for example, `CREATE` `ANY` `CLUSTER`), you give the user access to that type of object in all schemas, including the `SYS` schema. If you want to prohibit access to objects in the SYS schema, set the initialization parameter `O7_DICTIONARY_ACCESSIBILITY` to `FALSE`. Then privileges granted on "`ANY`" object will allow access to any schema except `SYS`.
CLUSTERS
`CREATE CLUSTER`	Create clusters in grantee's schema
`CREATE ANY CLUSTER`	Create a cluster in any schema. Behaves similarly to `CREATE` `ANY` `TABLE`.
`ALTER ANY CLUSTER`	Alter clusters in any schema
`DROP ANY CLUSTER`	Drop clusters in any schema
CONTEXTS
`CREATE ANY CONTEXT`	Create any context namespace
`DROP ANY CONTEXT`	Drop any context namespace
DATABASE
`ALTER DATABASE`	Alter the database
`ALTER SYSTEM`	Issue `ALTER` `SYSTEM` statements
`AUDIT SYSTEM`	Issue `AUDIT` `sql_statements` statements
DATABASE LINKS
`CREATE DATABASE LINK`	Create private database links in grantee's schema
`CREATE PUBLIC DATABASE LINK`	Create public database links
`DROP PUBLIC DATABASE LINK`	Drop public database links
DIMENSIONS
`CREATE DIMENSION`	Create dimensions in the grantee's schema
`CREATE ANY DIMENSION`	Create dimensions in any schema
`ALTER ANY DIMENSION`	Alter dimensions in any schema
`DROP ANY DIMENSION`	Drop dimensions in any schema
DIRECTORIES
`CREATE ANY DIRECTORY`	Create directory database objects
`DROP ANY DIRECTORY`	Drop directory database objects
INDEXTYPES
`CREATE INDEXTYPE`	Create an indextype in the grantee's schema
`CREATE ANY INDEXTYPE`	Create an indextype in any schema
`ALTER ANY INDEXTYPE`	Modify indextypes in any schema
`DROP ANY INDEXTYPE`	Drop an indextype in any schema
`EXECUTE ANY INDEXTYPE`	Reference an indextype in any schema
INDEXES
`CREATE ANY INDEX`	Create in any schema a domain index or an index on any table in any schema
`ALTER ANY INDEX`	Alter indexes in any schema
`DROP ANY INDEX`	Drop indexes in any schema
`QUERY REWRITE`	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables and views that are in the grantee's own schema
`GLOBAL QUERY REWRITE`	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables or views in any schema
LIBRARIES
`CREATE LIBRARY`	Create external procedure/function libraries in grantee's schema
`CREATE ANY LIBRARY`	Create external procedure/function libraries in any schema
`DROP LIBRARY`	Drop external procedure/function libraries in the grantee's schema
`DROP ANY LIBRARY`	Drop external procedure/function libraries in any schema
MATERIALIZED VIEWS
`CREATE MATERIALIZED VIEW`	Create a materialized view in the grantee's schema
`CREATE ANY MATERIALIZED VIEW`	Create materialized views in any schema
`ALTER ANY MATERIALIZED VIEW`	Alter materialized views in any schema
`DROP ANY MATERIALIZED VIEW`	Drop materialized views in any schema
`QUERY REWRITE`	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables and views that are in the grantee's own schema
`GLOBAL QUERY REWRITE`	Enable rewrite using a materialized view, or create a function-based index, when that materialized view or index references tables or views in any schema
`ON COMMIT REFRESH`	Create a refresh-on-commit materialized view on any table in the database Alter a refresh-on-demand materialized on any table in the database to refresh-on-commit
OPERATORS
`CREATE OPERATOR`	Create an operator and its bindings in the grantee's schema
`CREATE ANY OPERATOR`	Create an operator and its bindings in any schema
`DROP ANY OPERATOR`	Drop an operator in any schema
`EXECUTE ANY OPERATOR`	Reference an operator in any schema
OUTLINES
`CREATE ANY OUTLINE`	Create public outlines that can be used in any schema that uses outlines
`ALTER ANY OUTLINE`	Modify outlines
`DROP ANY OUTLINE`	Drop outlines
`SELECT ANY OUTLINE`	Create a clone private outline from a public outline
PROCEDURES
`CREATE PROCEDURE`	Create stored procedures, functions, and packages in grantee's schema
`CREATE ANY PROCEDURE`	Create stored procedures, functions, and packages in any schema
`ALTER ANY PROCEDURE`	Alter stored procedures, functions, or packages in any schema
`DROP ANY PROCEDURE`	Drop stored procedures, functions, or packages in any schema
`EXECUTE ANY PROCEDURE`	Execute procedures or functions (standalone or packaged) Reference public package variables in any schema
PROFILES
`CREATE PROFILE`	Create profiles
`ALTER PROFILE`	Alter profiles
`DROP PROFILE`	Drop profiles
ROLES
`CREATE ROLE`	Create roles
`ALTER ANY ROLE`	Alter any role in the database
`DROP ANY ROLE`	Drop roles
`GRANT ANY ROLE`	Grant any role in the database
ROLLBACK SEGMENTS
`CREATE ROLLBACK SEGMENT`	Create rollback segments
`ALTER ROLLBACK SEGMENT`	Alter rollback segments
`DROP ROLLBACK SEGMENT`	Drop rollback segments
SEQUENCES
`CREATE SEQUENCE`	Create sequences in grantee's schema
`CREATE ANY SEQUENCE`	Create sequences in any schema
`ALTER ANY SEQUENCE`	Alter any sequence in the database
`DROP ANY SEQUENCE`	Drop sequences in any schema
`SELECT ANY SEQUENCE`	Reference sequences in any schema
SESSIONS
`CREATE SESSION`	Connect to the database
`ALTER RESOURCE COST`	Set costs for session resources
`ALTER SESSION`	Issue `ALTER` `SESSION` statements
`RESTRICTED SESSION`	Logon after the instance is started using the SQL*Plus `STARTUP` `RESTRICT` statement
SNAPSHOTS. See MATERIALIZED VIEWS
SYNONYMS
`CREATE SYNONYM`	Create synonyms in grantee's schema
`CREATE ANY SYNONYM`	Create private synonyms in any schema
`CREATE PUBLIC SYNONYM`	Create public synonyms
`DROP ANY SYNONYM`	Drop private synonyms in any schema
`DROP PUBLIC SYNONYM`	Drop public synonyms
TABLES Note: For external tables, the only valid privileges are `CREATE` `ANY` `TABLE`, `ALTER` `ANY` `TABLE`, `DROP` `ANY` `TABLE`, and `SELECT` `ANY` `TABLE`.
`CREATE TABLE`	Create tables in grantee's schema
`CREATE ANY TABLE`	Create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table.
`ALTER ANY TABLE`	Alter any table or view in any schema
`BACKUP ANY TABLE`	Use the Export utility to incrementally export objects from the schema of other users
`DELETE ANY TABLE`	Delete rows from tables, table partitions, or views in any schema
`DROP ANY TABLE`	Drop or truncate tables or table partitions in any schema
`INSERT ANY TABLE`	Insert rows into tables and views in any schema
`LOCK ANY TABLE`	Lock tables and views in any schema
`SELECT ANY TABLE`	Query tables, views, or materialized views in any schema
`UPDATE ANY TABLE`	Update rows in tables and views in any schema
TABLESPACES
`CREATE TABLESPACE`	Create tablespaces
`ALTER TABLESPACE`	Alter tablespaces
`DROP TABLESPACE`	Drop tablespaces
`MANAGE TABLESPACE`	Take tablespaces offline and online and begin and end tablespace backups
`UNLIMITED TABLESPACE`	Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.
TRIGGERS
`CREATE TRIGGER`	Create a database trigger in grantee's schema
`CREATE ANY TRIGGER`	Create database triggers in any schema
`ALTER ANY TRIGGER`	Enable, disable, or compile database triggers in any schema
`DROP ANY TRIGGER`	Drop database triggers in any schema
`ADMINISTER DATABASE TRIGGER`	Create a trigger on `DATABASE`. (You must also have the `CREATE` `TRIGGER` or `CREATE` `ANY` `TRIGGER` privilege.)
TYPES
`CREATE TYPE`	Create object types and object type bodies in grantee's schema
`CREATE ANY TYPE`	Create object types and object type bodies in any schema
`ALTER ANY TYPE`	Alter object types in any schema
`DROP ANY TYPE`	Drop object types and object type bodies in any schema
`EXECUTE ANY TYPE`	Use and reference object types and collection types in any schema, and invoke methods of an object type in any schema if you make the grant to a specific user. If you grant `EXECUTE` `ANY` `TYPE` to a role, users holding the enabled role will not be able to invoke methods of an object type in any schema.
`UNDER ANY TYPE`	Create subtypes under any nonfinal object types.
USERS
`CREATE USER`	Create users. This privilege also allows the creator to: Assign quotas on any tablespace Set default and temporary tablespaces Assign a profile as part of a `CREATE` `USER` statement
`ALTER USER`	Alter any user. This privilege authorizes the grantee to: Change another user's password or authentication method Assign quotas on any tablespace Set default and temporary tablespaces Assign a profile and default roles
`BECOME USER`	Become another user. (Required by any user performing a full database import.)
`DROP USER`	Drop users
VIEWS
`CREATE VIEW`	Create views in grantee's schema
`CREATE ANY VIEW`	Create views in any schema
`DROP ANY VIEW`	Drop views in any schema
`UNDER ANY VIEW`	Create subviews under any object views
MISCELLANEOUS
`ANALYZE ANY`	Analyze any table, cluster, or index in any schema
`AUDIT ANY`	Audit any object in any schema using `AUDIT` `schema_objects` statements
`COMMENT ANY TABLE`	Comment on any table, view, or column in any schema
`EXEMPT ACCESS POLICY`	Bypass fine-grained access control
	Caution: This is a very powerful system privilege, as it lets the grantee bypass application-driven security policies. Database administrators should use caution when granting this privilege.
`FORCE ANY TRANSACTION`	Force the commit or rollback of any in-doubt distributed transaction in the local database Induce the failure of a distributed transaction
`FORCE TRANSACTION`	Force the commit or rollback of grantee's in-doubt distributed transactions in the local database
`GRANT ANY PRIVILEGE`	Grant any system privilege
`RESUMABLE`	Enable resumable space allocation
`SELECT ANY DICTIONARY`	Query any data dictionary object in the `SYS` schema. This privilege lets you selectively override the default `FALSE` setting of the `O7_DICTIONARY_ACCESSIBILITY` initialization parameter.
	Note: This privilege must be granted individually. It is not included in `GRANT` `ALL` `PRIVILEGES`, nor can it be granted through a role.
`SYSDBA`	Perform `STARTUP` and `SHUTDOWN` operations `ALTER` `DATABASE`: open, mount, back up, or change character set `CREATE` `DATABASE` `ARCHIVELOG` and `RECOVERY` `CREATE` `SPFILE` Includes the `RESTRICTED` `SESSION` privilege
`SYSOPER`	Perform `STARTUP` and `SHUTDOWN` operations `ALTER` `DATABASE` `OPEN \| MOUNT \| BACKUP` `ARCHIVELOG` and `RECOVERY` `CREATE` `SPFILE` Includes the `RESTRICTED` `SESSION` privilege

Table 16-2 Oracle Predefined Roles

Predefined Role	Purpose
`CONNECT, RESOURCE, and DBA`	These roles are provided for compatibility with previous versions of Oracle. You can determine the privileges encompassed by these roles by querying the `DBA_SYS_PRIVS` data dictionary view. See Also: Oracle9i Database Reference for a description of this view
	Note: Oracle Corporation recommends that you design your own roles for database security rather than relying on these roles. These roles may not be created automatically by future versions of Oracle.
`DELETE_CATALOG_ROLE EXECUTE_CATALOG_ROLE SELECT_CATALOG_ROLE`	These roles are provided for accessing data dictionary views and packages. See Also: Oracle9i Database Administrator's Guide for more information on these roles
`EXP_FULL_DATABASE IMP_FULL_DATABASE`	These roles are provided for convenience in using the Import and Export utilities. See Also: Oracle9i Database Utilities for more information on these roles
`AQ_USER_ROLE` `AQ_ADMINISTRATOR_ROLE`	You need these roles to use Oracle's Advanced Queuing functionality. See Also: Oracle9i Application Developer's Guide - Advanced Queuing for more information on these roles
`SNMPAGENT`	This role is used by Enterprise Manager/Intelligent Agent. See Also: Oracle Enterprise Manager Administrator's Guide
`RECOVERY_CATALOG_OWNER`	You need this role to create a user who owns a recovery catalog. See Also: Oracle9i User-Managed Backup and Recovery Guide for more information on recovery catalogs
`HS_ADMIN_ROLE`	A DBA using Oracle's heterogeneous services feature needs this role to access appropriate tables in the data dictionary and to manipulate them with the `DBMS_HS` package. See Also: Oracle9i Distributed Database Systems and Oracle9i Supplied PL/SQL Packages and Types Reference for more information

Table 16-3 Object Privileges Available for Particular Objects

Object Privilege	Table	View	Sequence	Procedures, Functions, Packages^a	Materialized View	Directory	Library	User- defined Type	Operator	Indextype
`ALTER`	X		X
`DELETE`	X	X			X^b
`EXECUTE`				X			X	X	X	X
`INDEX`	X
`INSERT`	X	X			X^b
`ON` `COMMIT` `REFRESH`	X
`QUERY` `REWRITE`	X
`READ`						X
`REFERENCES`	X	X
`SELECT`	X	X	X		X
`UNDER`		X						X
`UPDATE`	X	X			X^b
`WRITE`						X
^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 16-4 Object Privileges and the Operations They Authorize

Object Privilege	Operations Authorized
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. Note: For external tables, the only valid object privileges are `ALTER` and `SELECT`.
`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.
`REFERENCES`	Define foreign key constraints on the view.
`SELECT`	Query the view with the `SELECT` statement.
`UNDER`	Create a subview under this view. You can grant this object privilege only if you have the `UNDER` `ANY` `VIEW` privilege `WITH` `GRANT` `OPTION` on the immediate superview of this view.
`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, and 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: Oracle9i Database Concepts and Oracle9i Application Developer's Guide - Fundamentals
The following materialized view privilege authorizes operations on a materialized view.
`ON COMMIT REFRESH`	Create a refresh-on-commit materialized on the specified table.
`QUERY REWRITE`	Create a materialized view for query rewrite using the specified table.
`SELECT`	Query the materialized view 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 to 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 privileges provide 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 path name 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.
`WRITE`	Write files in the directory. This privilege is useful only in connection with external tables. It allows the grantee to determine whether the external table agent can write a log file, or a bad file to the directory. Restriction: This privilege does not allow the grantee to write to a `BFILE`.
The following object type privilege authorizes operations on an object type
`EXECUTE`	Use and reference the specified object and to invoke its methods.
`UNDER`	Create a subtype under this type. You can grant this object privilege only if you have the `UNDER` `ANY` `TYPE` privilege `WITH` `GRANT` `OPTION` on the immediate supertype of this type.
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

Granting a System Privilege to a User Example

To grant the CREATE SESSION system privilege to the sample user hr, allowing hr to log on to Oracle, issue the following statement:

GRANT CREATE SESSION 

TO hr;

Granting System Privileges to a Role Example

To grant appropriate system privileges to a data warehouse manager role (which was created in the "CREATE ROLE Example") :

GRANT
     CREATE ANY MATERIALIZED VIEW
   , ALTER ANY MATERIALIZED VIEW
   , DROP ANY MATERIALIZED VIEW
   , QUERY REWRITE
   , GLOBAL QUERY REWRITE
   TO dw_manager
   WITH ADMIN OPTION;

dw_manager's privilege domain now contains the system privileges related to materialized views.

Granting a Role with the Admin Option Example

To grant the dw_manager role with the ADMIN OPTION to the sample user sh, issue the following statement:

GRANT dw_manager 

TO sh 
WITH ADMIN OPTION;

User sh can now perform the following operations with the dw_manager role:

Enable the role and exercise any privileges in the role's privilege domain, including the CREATE MATERIALIZED VIEW system privilege
Grant and revoke the role to and from other users
Drop the role

Granting Object Privileges to a Role Example

To grant the SELECT object privileges to a data warehouse user role (which was created in the "CREATE ROLE Example") :

GRANT SELECT ON sh.sales TO warehouse_user;

Granting a Role to a Role Example

The following statement grants the dw_user role to the dw_manager role (both roles were created in the "CREATE ROLE Example"):

GRANT dw_user TO dw_manager;

The dw_manager role now contains all of the privileges in the domain of the dw_user role.

Granting an Object Privilege on a Directory Example

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

GRANT READ ON DIRECTORY bfile_dir TO oe

WITH GRANT OPTION;

Granting Object Privileges on a Table to a User Example

To grant all privileges on the table oe.bonuses (created in "MERGE Example") to the user hr with the GRANT OPTION, issue the following statement:

GRANT ALL ON bonuses TO hr 

WITH GRANT OPTION;

hr can subsequently perform the following operations:

Exercise any privilege on the bonuses table
Grant any privilege on the bonuses table to another user or role

Granting Object Privileges on a View Example

To grant SELECT and UPDATE privileges on the view emp_view (created in "Basic View Example") to all users, issue the following statement:

GRANT SELECT, UPDATE 

ON emp_view TO PUBLIC;

All users can subsequently query and update the view of employee details.

Granting Object Privileges to a Sequence in Another Schema Example

To grant SELECT privilege on the orders_seq sequence in the schema oe to the user hr, issue the following statement:

GRANT SELECT 

ON oe.orders_seq TO hr;

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

SELECT oe.orders_seq.NEXTVAL 

FROM DUAL;

Granting Multiple Object Privileges on Individual Columns Example

To grant to user oe the REFERENCES privilege on the employee_id column and the UPDATE privilege on the employee_id, salary, and commission_pct columns of the employees table in the schema hr, issue the following statement:

GRANT REFERENCES (employee_id), 
      UPDATE (employee_id, salary, commission_pct) 

ON hr.employees
TO oe;

oe can subsequently update values of the employee_id, salary, and commission_pct columns. oe can also define referential integrity constraints that refer to the employee_id column. However, because the GRANT statement lists only these columns, oe cannot perform operations on any of the other columns of the employees table.

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

CREATE TABLE dependent 

(dependno   NUMBER, 
 dependname VARCHAR2(10), 
 employee   NUMBER 
CONSTRAINT in_emp REFERENCES oe.employees(employee_id) );

The constraint in_emp ensures that all dependents in the dependent table correspond to an employee in the employees table in the schema hr.