Oracle8i SQL Reference
Release 3 (8.1.7)
Part Number A85397-01

Library
Product
Contents
Index

Go to previous page Go to beginning of chapter Go to next page

SQL Statements:
DROP SEQUENCE to UPDATE, 13 of 27

GRANT

Purpose

Use the GRANT statement to grant:

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_system_privileges_and_roles_clause::=


grant_object_privileges_clause::=


object_clause::=


grantee_clause::=


Keywords and Parameters

grant_system_privileges_and_roles_clause

system_privileges 

Specify the system privilege you want to grant. Table 11-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: 

 

  

role 

Specify the role you want to grant. You can grant an Oracle predefined role or a user-defined role. Table 11-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

 

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_clause

object_privileges 

Specify the object privilege you want to grant. You can substitute any of the values shown in Table 11-3. See also Table 11-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 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: Oracle8i Reference for information on the data dictionary views

 

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. 

object_clause  

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.  

 

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 / snapshot

  • 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 Oracle8i 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 11-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 (which are identical to SNAPSHOTS) 

    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. 

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 outlines that can be used in any schema that uses outlines 

    ALTER ANY OUTLINE

 

Modify outlines. 

    DROP ANY OUTLINE

 

Drop outlines 

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 (which are identical to MATERIALIZED VIEWS) 

    CREATE SNAPSHOT

 

Create snapshots in grantee's schema 

    CREATE ANY SNAPSHOT

 

Create snapshots in any schema 

    ALTER ANY SNAPSHOT

 

Alter any snapshot in the database 

    DROP ANY SNAPSHOT

 

Drop snapshots in any schema 

    GLOBAL QUERY REWRITE

 

Enable rewrite using a snapshot, or create a function-based index, when that snapshot or index references tables or views in any schema. 

    QUERY REWRITE

 

Enable rewrite using a snapshot, or create a function-based index, when that snapshot or index references tables and views that are in the grantee's own schema. 

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 

    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 the 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 

    UPDATE ANY TABLE

 

Update rows in tables and views in any schema 

    SELECT ANY TABLE

 

Query tables, views, or snapshots 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. 

USERS 

    CREATE USER

 

Create users. This privilege also allows the creator to

Assign quotas on any tablespace,

Set default and temporary tablespaces, and

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, and

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 

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 

    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. 

    SYSDBA

 

Perform STARTUP and SHUTDOWN operations

ALTER DATABASE: open, mount, back up, or change character set

CREATE DATABASE

ARCHIVELOG and RECOVERY

Includes the RESTRICTED SESSION privilege 

    SYSOPER

 

Perform STARTUP and SHUTDOWN operations

ALTER DATABASE OPEN/MOUNT/BACKUP

ARCHIVELOG and RECOVERY

Includes the RESTRICTED SESSION privilege 
Table 11-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_PRIVILEGES data dictionary 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.

 

EXP_FULL_DATABASE IMP_FULL_DATABASE 

These roles are provided for convenience in using the Import and Export utilities.

 

AQ_USER_ROLE

AQ_ADMINISTRATOR_ROLE  

You need these roles to use Oracle's Advanced Queuing functionality.

 

SNMPAGENT 

This role is used by Enterprise Manager/Intelligent Agent.

 

RECOVERY_CATALOG_OWNER 

You need this role to create a user who owns a recovery catalog.

 

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.

 

Oracle also creates other roles that authorize you to administer the database. On many operating systems, these roles are called OSOPER and OSDBA. Their names may be different on your operating system.  
Table 11-3  Object Privileges Available for Particular Objects
Object Privilege  Table  View  Sequence  Procedures, Functions, Packagesa  Materialized View  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 11-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. 

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.

 

 

  

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

Granting a System Privilege to a User Example

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

GRANT CREATE SESSION 


TO richard;

Granting a System Privilege to a Role Example

To grant the CREATE TABLE system privilege to the role travel_agent, issue the following statement: 

GRANT CREATE TABLE 


TO travel_agent;

travel_agent's privilege domain now contains the CREATE TABLE system privilege.

Granting a Role to a Role Example

The following statement grants the travel_agent role to the EXECUTIVE role: 

GRANT travel_agent 


TO executive;

travel_agent is now granted to executive. executive's privilege domain contains the CREATE TABLE system privilege.

Granting a Role with the Admin Option Example

To grant the executive role with the ADMIN OPTION to THOMAS, issue the following statement: 

GRANT executive 


TO thomas 
WITH ADMIN OPTION;

thomas can now perform the following operations with the executive role:

Granting an Object Privilege on a Directory Example

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;

Granting Object Privileges on a Table to a User Example

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:

Granting Object Privileges on a View Example

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.

Granting Object Privileges to a Sequence in Another Schema Example

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;

Granting Multiple Object Privileges on Individual Columns Example

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.

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index