Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
DROP SEQUENCE to ROLLBACK, 13 of 20
Use the GRANT
statement to grant:
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.
See Also:
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.
grant::=
grant
grant_system_privileges
grant_object_privileges
on_object_clause
grantee_clause
Specify the system privilege you want to grant. Table 16-1 lists the system privileges (organized by the database object operated upon).
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.
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.
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.
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.
Specify WITH
ADMIN
OPTION
to enable the grantee to:
GLOBAL
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
.
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.
Specify PUBLIC
to grant the privileges to all users.
Restrictions on granting system privileges and roles:
IDENTIFIED
GLOBALLY
to anything.
IDENTIFIED
EXTERNALLY
to a global user or global role.
banker
to the role teller
, you cannot subsequently grant teller
to banker
.
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.
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, 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.
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.
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.
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).
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:
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.
Note:
Specify a directory schema object on which privileges are to be granted. You cannot qualify directory_name with a schema name.
The JAVA
clause lets you specify a Java source or resource schema object on which privileges are to be granted.
Predefined Role | Purpose |
---|---|
|
These roles are provided for compatibility with previous versions of Oracle. You can determine the privileges encompassed by these roles by querying the 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. |
|
These roles are provided for accessing data dictionary views and packages. See Also: Oracle9i 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: Oracle9i Database Utilities for more information on these roles |
|
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 |
|
This role is used by Enterprise Manager/Intelligent Agent. |
|
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 |
|
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 See Also: Oracle9i Distributed Database Systems and Oracle9i Supplied PL/SQL Packages and Types Reference for more information |
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 SESSIONTO hr;
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.
To grant the dw_manager
role with the ADMIN
OPTION
to the sample user sh
, issue the following statement:
GRANT dw_managerTO sh WITH ADMIN OPTION;
User sh
can now perform the following operations with the dw_manager
role:
CREATE
MATERIALIZED
VIEW
system privilege
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;
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.
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 oeWITH GRANT OPTION;
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 hrWITH GRANT OPTION;
hr
can subsequently perform the following operations:
bonuses
table
bonuses
table to another user or role
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, UPDATEON emp_view TO PUBLIC;
All users can subsequently query and update the view of employee details.
To grant SELECT
privilege on the orders_seq
sequence in the schema oe
to the user hr
, issue the following statement:
GRANT SELECTON oe.orders_seq TO hr;
hr
can subsequently generate the next value of the sequence with the following statement:
SELECT oe.orders_seq.NEXTVALFROM DUAL;
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
.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|