Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-02 |
|
|
View PDF |
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 18-1 lists the system privileges (organized by the database object operated upon). For information on supplied roles, refer to Oracle Database Security Guide.
Object privileges for a particular object to users, roles, and PUBLIC
. Table 18-2 lists object privileges and the operations that they authorize.
Notes on Authorizing Database Users You can authorize database users through means other than the database and the GRANT
statement.
Many Oracle Database privileges are granted through supplied PL/SQL and Java packages. For information on those privileges, refer to the documentation for the appropriate package.
Some operating systems have facilities that let you grant roles to Oracle Database users with the initialization parameter OS_ROLES
. If you choose to grant roles to users through operating system facilities, then 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.
Note on Automatic Storage Management A user authenticated AS
SYSASM
can use this statement to grant the system privileges SYSASM
, SYSOPER
, and SYSDBA
to a user in the Automatic Storage Management password file of the current node.
See Also:
CREATE USER and CREATE ROLE for definitions of local, global, and external privileges
Oracle Database Security Guide for information about other authorization methods and for information about privileges
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
, or you must have been granted the GRANT
ANY
OBJECT
PRIVILEGE
system privilege. If you have the GRANT
ANY
OBJECT
PRIVILEGE
, then you can grant the object privilege only if the object owner could have granted the same object privilege. In this case, the GRANTOR
column of the DBA_TAB_PRIVS
view displays the object owner rather than the user who issued the GRANT
statement.
Syntax
grant::=
(grant_system_privileges::=, grant_object_privileges::=)
(on_object_clause ::=, grantee_clause ::=)
on_object_clause ::=
grantee_clause ::=
Semantics
grant_system_privileges
Use these clauses to grant system privileges.
system_privilege
Specify the system privilege you want to grant. Table 18-1 lists the system privileges, organized by the database object operated upon.
If you grant a privilege to a user, then the database adds the privilege to the user's privilege domain. The user can immediately exercise the privilege.
If you grant a privilege to a role, then the database adds the privilege to the privilege domain of the role. 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
, then the database adds the privilege to the privilege domains of each user. All users can immediately perform operations authorized by the privilege.
Oracle Database provides the ALL
PRIVILEGES
shortcut for granting all the system privileges listed in Table 18-1, except the SELECT
ANY
DICTIONARY
privilege.
Specify the role you want to grant. You can grant an Oracle Database predefined role or a user-defined role.
If you grant a role to a user, then the database makes the role available to the user. The user can immediately enable the role and exercise the privileges in the privilege domain of the role.
If you grant a role to another role, then the database adds the privilege domain of the granted role to the privilege domain of the grantee role. 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
, then the database makes the role available to all users. All users can immediately enable the role and exercise the privileges in the privilege domain of the role.
Every role you grant to a user is a default role unless and until you issue an ALTER
USER
... DEFAULT
ROLE
statement. That statement determines the default roles. All other roles, granted previously or subsequently, are not default roles unless you make them so in another ALTER
USER
... DEFAULT
ROLE
statement.
See Also:
Oracle Database Security Guide for information on the Oracle predefined roles
CREATE ROLE for information on creating a user-defined role
IDENTIFIED BY Clause
This clause is valid only when granting system privileges, not when granting object privileges. 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, then the database 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 passwordsSpecify 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
, then 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 on Grantees 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 Privileges and roles are subject to the following restrictions:
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
, then you cannot subsequently grant teller
to banker
.
grant_object_privileges
Use these clauses to grant object privileges.
object_privilege
Specify the object privilege you want to grant. You can specify any of the values described Table 18-2.
Restriction on Object Privileges A privilege cannot appear more than once in the list of privileges to be granted.
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.
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, then 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_
, or DBA_COL_PRIVS
data dictionary view.
See Also:
Oracle Database Reference for information on the data dictionary views and "Granting Multiple Object Privileges on Individual Columns: Example"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.
If you can make this grant only because you have the GRANT
ANY
OBJECT
PRIVILEGE
system privilege—that is, you are not the owner of object
, nor do you have object_privilege
on object
WITH
GRANT
OPTION
—then the effect of this grant is that you are acting on behalf of the object owner. The *_TAB_PRIVS
data dictionary views will reflect that this grant was made by the owner of object
.
See Also:
"Revoke Operations that Use GRANT ANY OBJECT PRIVILEGE: Example" for more information on using the GRANT
ANY
OBJECT
PRIVILEGE
system privilege for revoke operations
Specify WITH
GRANT
OPTION
to enable the grantee to grant the object privileges to other users and roles.
Restriction on Granting WITH GRANT OPTION You can specify WITH
GRANT
OPTION
only when granting to a user or to PUBLIC
, not when granting to a role.
Specify WITH
HIERARCHY
OPTION
to grant the specified object privilege on all subobjects of object
, such as subviews created under a view, including subobjects created subsequent to this statement.
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
, then the database 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 preceding items
Directory, library, operator, or indextype
Java source, class, or resource
You cannot grant privileges directly to a single partition of a partitioned table.
See Also:
"Granting Object Privileges on a Table to a User: Example", "Granting Object Privileges on a View: Example", and "Granting Object Privileges to a Sequence in Another Schema: Example"DIRECTORY directory_name Specify a directory schema object on which privileges are to be granted. You cannot qualify directory_name
with a schema name.
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 JAVAListings of System and Object Privileges
Note:
When you grant a privilege onANY
object, such as CREATE
ANY
CLUSTER
, the result is determined by the value of the O7_DICTIONARY_ACCESSIBILITY
initialization parameter. By default, this parameter is set to FALSE
, so that ANY
privileges give the grantee access to that type of object in all schemas except the SYS
schema. If you set O7_DICTIONARY_ACCESSIBILITY
to TRUE
, then the ANY
privileges also give the grantee access, in the SYS
schema, to all objects except Oracle Scheduler objects. For security reasons, Oracle recommends that you use this setting only with great caution.Table 18-1 System Privileges
System Privilege Name | Operations Authorized |
---|---|
Advisor Framework Privileges: All of the advisor framework privileges are part of the DBA role. |
— |
Access the advisor framework through PL/SQL packages such as Refer to Oracle Database PL/SQL Packages and Types Reference for information on these packages. |
|
Create, drop, select (read), load (write), and delete a SQL tuning set owned by the grantee through the |
|
Create, drop, select (read), load (write), and delete a SQL tuning set owned by any user through the |
|
Accept a SQL Profile recommended by the SQL Tuning Advisor, which is accessed through Enterprise Manager or by the |
|
Drop an existing SQL Profile. |
|
Alter the attributes of an existing SQL Profile. |
|
— |
|
Create clusters in the grantee's schema. |
|
Create a cluster in any schema. Behaves similarly to |
|
Alter clusters in any schema. |
|
Drop clusters in any schema. |
|
— |
|
Create any context namespace. |
|
Drop any context namespace. |
|
— |
|
Alter the database. |
|
Issue |
|
Issue |
|
— |
|
Create private database links in the grantee's schema. |
|
Create public database links. |
|
Drop public database links. |
|
— |
|
Connect the current session to a debugger. |
|
Debug all PL/SQL and Java code in any database object. Display information on all SQL statements executed by the application. Note: Granting this privilege is equivalent to granting the |
|
DICTIONARIES |
— |
|
Analyze any data dictionary object. |
— |
|
Create dimensions in the grantee's schema. |
|
Create dimensions in any schema. |
|
Alter dimensions in any schema. |
|
Drop dimensions in any schema. |
|
— |
|
Create directory database objects. |
|
Drop directory database objects. |
|
— |
|
Create, alter, or drop any flashback data archive. |
|
— |
|
Create an indextype in the grantee's schema. |
|
Create an indextype in any schema and create a comment on an indextype in any schema. |
|
Modify indextypes in any schema. |
|
Drop an indextype in any schema. |
|
Reference an indextype in any schema. |
|
— |
|
Create in any schema a domain index or an index on any table in any schema. |
|
Alter indexes in any schema. |
|
Drop indexes in any schema. |
|
The following privileges are needed to execute procedures in the |
|
Create jobs, schedules, or programs in the grantee's schema. |
|
Create, alter, or drop jobs, schedules, or programs in any schema. Note: This extremely powerful privilege allows the grantee to execute code as any other user. It should be granted with caution. |
|
Create in the grantee's schema an executable scheduler job that runs on the operating system. |
|
Use any program in a job in the grantee's schema. |
|
Specify any job class in a job in the grantee's schema. |
|
Create, alter, or drop any job class, window, or window group. |
|
— |
|
Create external procedure or function libraries in the grantee's schema. |
|
Create external procedure or function libraries in any schema. |
|
Drop external procedure or function libraries in any schema. |
|
— |
|
Create a materialized view in the grantee's schema. |
|
Create materialized views in any schema. |
|
Alter materialized views in any schema. |
|
Drop materialized views in any schema. |
|
This privilege has been deprecated. No privileges are needed for a user to enable rewrite for a materialized view that references tables or views in the user's own schema. |
|
Enable rewrite using a materialized view when that materialized view references tables or views in any schema. |
|
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. |
|
Issue a SQL Flashback Query on any table, view, or materialized view in any schema. This privilege is not needed to execute the |
|
MINING MODELS: |
— |
Create mining models in the grantee's schema using the |
|
Create mining models in any schema using the |
|
Change the mining model name or the associated cost matrix of any model in any schema by using the applicable |
|
Drop any mining model in any schema by using the |
|
Score or view any model in any schema. Scoring is done either with the |
|
Create a comment on any model in any schema using the SQL |
|
OLAP CUBES: |
The following privileges are valid when you are using Oracle Database with the OLAP option. |
Create an OLAP cube in the grantee's schema. |
|
Create an OLAP cube in any schema. |
|
Alter an OLAP cube in any schema. |
|
Drop any OLAP cube in any schema. |
|
Query or view any OLAP cube in any schema. |
|
Update any cube in any schema. |
|
OLAP CUBE MEASURE FOLDERS: |
The following privileges are valid when you are using Oracle Database with the OLAP option. |
Create an OLAP measure folder in the grantee's schema. |
|
Create an OLAP measure folder in any schema. |
|
Delete from any OLAP measure folder in any schema. |
|
Drop any measure folder in any schema. |
|
Insert a measure into any measure folder in any schema. |
|
OLAP CUBE DIMENSIONS: |
The following privileges are valid when you are using Oracle Database with the OLAP option. |
Create an OLAP cube dimension in the grantee's schema. |
|
Create an OLAP cube dimension in any schema. |
|
Alter an OLAP cube dimension in any schema. |
|
Delete from an OLAP cube dimension in any schema. |
|
Drop an OLAP cube dimension in any schema. |
|
Insert into an OLAP cube dimension in any schema. |
|
View or query an OLAP cube dimension in any schema. |
|
Update an OLAP cube dimension in any schema. |
|
OLAP CUBE BUILD PROCESSES: |
— |
Create an OLAP cube build process in the grantee's schema. |
|
Create an OLAP cube build process in any schema. |
|
Drop an OLAP cube build process in any schema. |
|
Update an OLAP cube build process in any schema. |
|
— |
|
Create an operator and its bindings in the grantee's schema. |
|
Create an operator and its bindings in any schema and create a comment on an operator in any schema. |
|
Modify an operator in any schema. |
|
Drop an operator in any schema. |
|
Reference an operator in any schema. |
|
— |
|
Create public outlines that can be used in any schema that uses outlines. |
|
Modify outlines. |
|
Drop outlines. |
|
— |
|
Create stored procedures, functions, and packages in the grantee's schema. |
|
Create stored procedures, functions, and packages in any schema. |
|
Alter stored procedures, functions, or packages in any schema. |
|
Drop stored procedures, functions, or packages in any schema. |
|
Execute procedures or functions, either standalone or packaged. Reference public package variables in any schema. |
|
— |
|
Create profiles. |
|
Alter profiles. |
|
Drop profiles. |
|
— |
|
Create roles. |
|
Alter any role in the database. |
|
Drop roles. |
|
Grant any role in the database. |
|
— |
|
Create rollback segments. |
|
Alter rollback segments. |
|
Drop rollback segments. |
|
— |
|
Create sequences in the grantee's schema. |
|
Create sequences in any schema. |
|
Alter any sequence in the database. |
|
Drop sequences in any schema. |
|
Reference sequences in any schema. |
|
— |
|
Connect to the database. |
|
Set costs for session resources. |
|
Enable and disable the SQL trace facility. |
|
Logon after the instance is started using the SQL*Plus |
|
SNAPSHOTS: |
See |
— |
|
Create synonyms in the grantee's schema. |
|
Create private synonyms in any schema. |
|
Create public synonyms. |
|
Drop private synonyms in any schema. |
|
Drop public synonyms. |
|
Note: For external tables, the only valid privileges are |
|
Create a table in the grantee's schema. |
|
Create a table 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 or view in any schema. |
|
Use the Export utility to incrementally export objects from the schema of other users. |
|
Delete rows from tables, table partitions, or views in any schema. |
|
Drop or truncate tables or table partitions in any schema. |
|
Insert rows into tables and views in any schema. |
|
Lock tables and views in any schema. |
|
Query tables, views, or materialized views in any schema. |
|
Issue a SQL Flashback Query on any table, view, or materialized view in any schema. This privilege is not needed to execute the |
|
Update rows in tables and views in any schema. |
|
— |
|
Create tablespaces. |
|
Alter tablespaces. |
|
Drop tablespaces. |
|
Take tablespaces offline and online and begin and end tablespace backups. |
|
Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, then 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. |
|
— |
|
Create a database trigger in the grantee's schema. |
|
Create database triggers in any schema. |
|
Enable, disable, or compile database triggers in any schema. |
|
Drop database triggers in any schema. |
|
Create a trigger on |
|
— |
|
Create object types and object type bodies in the grantee's schema. |
|
Create object types and object type bodies in any schema. |
|
Alter object types in any schema. |
|
Drop object types and object type bodies in any schema. |
|
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 |
|
Create subtypes under any nonfinal object types. |
|
— |
|
Create users. This privilege also allows the creator to:
|
|
Alter any user. This privilege authorizes the grantee to:
|
|
Drop users |
|
— |
|
Create views in the grantee's schema. |
|
Create views in any schema. |
|
Drop views in any schema. |
|
Create subviews under any object views. |
|
Issue a SQL Flashback Query on any table, view, or materialized view in any schema. This privilege is not needed to execute the |
|
If a user has been granted the |
|
MISCELLANEOUS: |
— |
Analyze any table, cluster, or index in any schema. |
|
Audit any object in any schema using |
|
Allows users of the Data Pump Import utility (impdp) and the original Import utility (imp) to assume the identity of another user in order to perform operations that cannot be directly performed by a third party (for example, loading objects such as object privilege grants). Allows Streams administrators to create or alter capture users and apply users in a Streams environment. By default this privilege is part of the DBA role. Data Vault removes this privileges from the DBA role. Therefore, this privilege is needed by Streams only in an environment where Data Vault is installed. |
|
Create a registration on queries and receive database change notifications in response to DML or DDL changes to the objects associated with the registered queries. Refer to Oracle Database Advanced Application Developer's Guide for more information on database change notification. |
|
Comment on any table, view, or column in any schema. |
|
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 the commit or rollback of any in-doubt distributed transaction in the local database. Induce the failure of a distributed transaction. |
|
Force the commit or rollback of the grantee's in-doubt distributed transactions in the local database. |
|
Grant any object privilege that the object owner is permitted to grant. Revoke any object privilege that was granted by the object owner or by some other user with the |
|
Grant any system privilege. |
|
Enable resumable space allocation. |
|
Query any data dictionary object in the |
|
|
Query the contents of the Caution: This is a very powerful system privilege, as it lets the grantee view all data in the database, including past data. This privilege should be granted only to users who need to use the Oracle Flashback Transaction Query feature. |
Perform
Includes the |
|
Perform
Includes the |
|
These roles are provided for compatibility with previous versions of Oracle Database. You can determine the privileges encompassed by these roles by querying the Note: Oracle 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 Database. See Also: Oracle Database Reference for a description of the |
|
|
These roles are provided for accessing data dictionary views and packages. See Also: Oracle Database Administrator's Guide for more information on these roles |
|
These roles are provided for convenience in using the import and export utilities. See Also: Oracle Database Utilities for more information on these roles |
You need these roles to use Oracle Advanced Queuing. See Also: Oracle Streams Advanced Queuing User's Guide for more information on these roles |
|
This role is used by the Enterprise Manager Management Agent. See Also: Oracle Enterprise Manager Advanced Configuration for more information on these roles |
|
You need this role to create a user who owns a recovery catalog. See Also: Oracle Database Backup and Recovery User's Guide for more information on recovery catalogs |
Table 18-2 Object Privileges and the Operations They Authorize
Object Privilege | Operations Authorized |
---|---|
DIRECTORY PRIVILEGES |
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 Database 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 the database to enforce security during file operations. |
Read files in the directory. |
|
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 |
|
INDEXTYPE PRIVILEGE |
The following indextype privilege authorizes operations on indextypes. |
|
Reference an indextype. |
The following flashback data archive privilege authorizes operations on flashback data archives. |
|
|
Enable or disable historical tracking for a table. |
LIBRARY PRIVILEGE |
The following library privilege authorizes operations on a library. |
Use and reference the specified object and invoke its methods. |
|
MATERIALIZED VIEW PRIVILEGES |
The following materialized view privileges authorize operations on a materialized view. The |
Create a refresh-on-commit materialized view on the specified table. |
|
Create a materialized view for query rewrite using the specified table. |
|
Query the materialized view with the |
|
MINING MODEL PRIVILEGES |
The following mining model privileges authorize operations on a mining model. These privileges are not required for models within the users own schema. |
Change the mining model name or the associated cost matrix using the applicable |
|
Score or view the mining model. Scoring is done with the |
|
OBJECT TYPE PRIVILEGES |
The following object type privileges authorize operations on a database object type. |
Access, through a debugger, all public and nonpublic variables, methods, and types defined on the object type. Place a breakpoint or stop at a line or instruction boundary within the type body. |
|
Use and reference the specified object and invoke its methods. Access, through a debugger, public variables, types, and methods defined on the object type. |
|
Create a subtype under this type. You can grant this object privilege only if you have the |
|
OLAP PRIVILEGES |
The following object privileges are valid if you are using Oracle Database with the OLAP option. |
Insert members into the OLAP cube dimension or measures into the measures folder. |
|
Change the definition of the OLAP cube dimension or cube. |
|
Delete members from the OLAP cube dimension or measures from the measures folder. |
|
View or query the OLAP cube or cube dimension. |
|
Update measure values of the OLAP cube or attribute values of the cube dimension. |
|
OPERATOR PRIVILEGE |
The following operator privilege authorizes operations on user-defined operators. |
Reference an operator. |
|
PROCEDURE, FUNCTION, PACKAGE PRIVILEGES |
The following procedure, function, and package privileges authorize operations on procedures, functions, and packages. These privileges also apply to Java sources, classes, and resources, which Oracle Database treats as though they were procedures for purposes of granting object privileges. |
Access, through a debugger, all public and nonpublic variables, methods, and types defined on the object. Place a breakpoint or stop at a line or instruction boundary within the procedure, function, or package. This privilege grants access to the declarations in the method or package specification and body. |
|
|
Execute the procedure or function directly, or access any program object declared in the specification of a package, or compile the object implicitly during a call to a currently invalid or uncompiled function or procedure. This privilege does not allow the grantee to explicitly compile using Access, through a debugger, public variables, types, and methods defined on the procedure, function, or package. This privilege grants access to the declarations in the method or package specification only. Job scheduler objects are created using the Note: Users do not need this privilege to execute a procedure, function, or package indirectly. |
SEQUENCE PRIVILEGES |
The following sequence privileges authorize operations on a sequence. |
Change the sequence definition with the |
|
Examine and increment values of the sequence with the |
|
SYNONYM PRIVILEGES |
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, then the user can use either the synonym name or the base object name in the SQL statement that exercises the privilege. |
TABLE PRIVILEGES |
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 Note: For external tables, the only valid object privileges are |
Change the table definition with the |
|
Remove rows from the table with the Note: You must grant the |
|
Access, through a debugger:
|
|
Create an index on the table with the |
|
Add new rows to the table with the |
|
Create a constraint that refers to the table. You cannot grant this privilege to a role. |
|
Query the table with the |
|
Change data in the table with the Note: You must grant the |
|
VIEW PRIVILEGES |
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 To grant a privilege on a view, you must have that privilege with the |
Access, through a debugger:
|
|
Remove rows from the view with the |
|
Add new rows to the view with the |
|
Define foreign key constraints on the view. |
|
Query the view with the |
|
Create a subview under this view. You can grant this object privilege only if you have the |
|
Change data in the view with the |
Note 1: Job scheduler objects are created using the DBMS_SCHEDULER
package. After these objects are created, you can grant the EXECUTE
object privilege on job scheduler classes and programs. You can grant ALTER
privilege on job scheduler jobs, programs, and schedules.
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 Database, issue the following statement:
GRANT CREATE SESSION TO hr;
Granting System Privileges to a Role: Example The following statement grants appropriate system privileges to a data warehouse manager role, which was created in the "Creating a 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;
The dw_manager
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 privilege domain of the role, 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 The following example grants the SELECT
object privileges to a data warehouse user role, which was created in the "Creating a Role: Example":
GRANT SELECT ON sh.sales TO warehouse_user;
Granting a Role to a Role: Example The following statement grants the warehouse_user
role to the dw_manager
role. Both roles were created in the "Creating a Role: Example":
GRANT warehouse_user TO dw_manager;
The dw_manager
role now contains all of the privileges in the domain of the warehouse_user
role.
Granting an Object Privilege on a Directory: Example To grant READ
on directory bfile_dir
to user hr
, with the GRANT
OPTION
, issue the following statement:
GRANT READ ON DIRECTORY bfile_dir TO hr WITH GRANT OPTION;
Granting Object Privileges on a Table to a User: Example To grant all privileges on the table oe.bonuses
, which was created in "Merging into a Table: Example", to the user hr
with the GRANT
OPTION
, issue the following statement:
GRANT ALL ON bonuses TO hr WITH GRANT OPTION;
The user 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
, which was created in "Creating a 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 customers_seq
sequence in the schema oe
to the user hr
, issue the following statement:
GRANT SELECT ON oe.customers_seq TO hr;
The user hr
can subsequently generate the next value of the sequence with the following statement:
SELECT oe.customers_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;
The user oe
can subsequently update values of the employee_id
, salary
, and commission_pct
columns. User 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 hr.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
.