GRANT (Object Privileges)

Purpose

Prerequisites

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the label at which the object privilege was granted to you and the creation label of the grantee user or role.

Syntax

Keywords and Parameters

object_priv

is an object privilege to be granted. You can substitute any of the following values:

• ALTER

• DELETE

• EXECUTE

• INDEX

• INSERT

• REFERENCES

• SELECT

• UPDATE

ALL PRIVILEGES

grants all the privileges for the object that you have been granted with the GRANT OPTION. The user who owns the schema containing an object automatically has all privileges on the object with the GRANT OPTION.

column

specifies a table or view column on which privileges are granted. You can only specify columns when granting the INSERT, REFERENCES, or UPDATE privilege. If you do not list columns, the grantee has the specified privilege on all columns in the table or view.

ON

identifies the object on which the privileges are granted. If you do not qualify object with schema, Oracle7 assumes the object is in your own schema. The object can be one of the following types:

• table

• view

• sequence

• procedure, function, or package

• snapshots

• synonym for a table, view, sequence, snapshot, procedure, function, or package

TO

identifies users or roles to which the object privilege is granted.

PUBLIC grants object privileges to all users.

WITH GRANT OPTION

allows the grantee to grant the object privileges to other users and roles. The grantee must be a user or PUBLIC, rather than a role.

Usage Notes

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

If you grant a privilege to a role: Oracle7 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: Oracle7 adds the privilege to the privilege domain of each user. All users can immediately exercise the privilege.

A privilege cannot appear more than once in the list of privileges to be granted. A user or role cannot appear more than once in the TO clause.

Object Privileges

Object Privilege	Tables	Views	Sequences	Procedure Functions Packages	Snapshots
ALTER	3		3
DELETE	3	3
EXECUTE				3
INDEX	3
INSERT	3	3
REFERENCES	3
SELECT	3	3	3		3
UPDATE	3	3

Table 4 - 13. Object Privileges

Table Privileges

ALTER

allows the grantee to change the table definition with the ALTER TABLE command.

DELETE

allows the grantee to remove rows from the table with the DELETE command.

INDEX

allows the grantee to create an index on the table with the CREATE INDEX command.

INSERT

allows the grantee to add new rows to the table with the INSERT command.

REFERENCES

allows the grantee to create a constraint that refers to the table. You cannot grant this privilege to a role.

SELECT

allows the grantee to query the table with the SELECT command.

UPDATE

allows the grantee to change data in the table with the UPDATE command.

Any one of above object privileges allows the grantee to lock the table in any lock mode with the LOCK TABLE command.

View Privileges

DELETE

allows the grantee to remove rows from the view with the DELETE command.

INSERT

allows the grantee to add new rows to the view with the INSERT command.

SELECT

allows the grantee to query the view with the SELECT command.

UPDATE

allows the grantee to change data in the view with the UPDATE command.

Any one of the above object privileges allows the grantee to lock the view in any lock mode with the LOCK TABLE command.

To grant a privilege on a view, you must have that privilege with the GRANT OPTION on all of the view's base tables.

Sequence Privileges

ALTER

allows the grantee to change the sequence definition with the ALTER SEQUENCE command.

SELECT

allows the grantee to examine and increment values of the sequence with the CURRVAL and NEXTVAL pseudocolumns.

Procedure, Function, and Package Privileges

EXECUTE

allows the grantee to execute the procedure or function or to access any program object declared in the specification of a package.

Snapshot Privileges

SELECT

allows the grantee to query the snapshot with the SELECT command.

Synonym Privileges

Example I

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

GRANT ALL 

	ON bonus 

	TO jones 

	WITH GRANT OPTION 

JONES can subsequently perform the following operations:

• exercise any privilege on the BONUS table

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

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

GRANT SELECT, UPDATE 

	ON golf_handicap 

	TO PUBLIC 

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

Example III

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 

Example IV

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, since 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.

Related Topics