Oracle7 Server SQL Reference

GRANT (System Privileges and Roles)

Purpose

To grant system privileges and roles to users and roles. To grant object privileges, use the GRANT command (Object Privileges) described in the next section of this chapter.

Prerequisites

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted 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 GRANT ANY ROLE system privilege or have created the role.

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

Syntax

Keywords and Parameters

system_priv

is a system privilege to be granted.

role

is a role to be granted.

identifies users or roles to which system privileges and roles are granted.

PUBLIC grants system privileges or roles to all users.

WITH ADMIN OPTION

allows the grantee to grant the system privilege or role to other users or roles. If you grant a role with ADMIN OPTION, the grantee can also alter or drop the role.

Usage Notes

You can use this form of the GRANT command to grant both system privileges and roles to users, roles, and PUBLIC:

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

If you grant a role to a user: Oracle7 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: Oracle7 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: Oracle7 makes the role available to all users. All users can immediately enable the role and exercise the privileges in the roles privilege domain.

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

You cannot grant roles circularly. For example, if you grant the role BANKER to the role TELLER, you cannot subsequently grant TELLER to BANKER. Also, you cannot grant a role to itself.

System Privileges

Table 4 - 11 lists system privileges and the operations that they authorize. You can grant any of these system privileges with the GRANT command.

System Privilege Operations Authorized
ALTER ANY CLUSTER Allows grantee to alter any cluster in any schema.
ALTER ANY INDEX Allows grantee to alter any index in any schema
ALTER ANY PROCEDURE Allows grantee to alter any stored procedure, function, or package in any schema.
ALTER ANY ROLE Allows grantee to alter any role in the database.
ALTER ANY SEQUENCE Allows grantee to alter any sequence in the database.
ALTER ANY SNAPSHOT Allows grantee to alter any snapshot in the database.
ALTER ANY TABLE Allows grantee to alter any table or view in the schema.
ALTER ANY TRIGGER Allows grantee to enable, disable, or compile any database trigger in any schema.
ALTER DATABASE Allows grantee to alter the database.
ALTER PROFILE Allows grantee to alter profiles.
ALTER RESOURCE COST Allows grantee to set costs for session resources.
ALTER ROLLBACK SEGMENT Allows grantee to alter rollback segments.
ALTER SESSION Allows grantee to issue ALTER SESSION statements.
ALTER SYSTEM Allows grantee to issue ALTER SYSTEM statements.
ALTER TABLESPACE Allows grantee to alter tablespaces.
ALTER USER Allows grantee to 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.
ANALYZE ANY Allows grantee to analyze any table, cluster, or index in any schema.
AUDIT ANY Allows grantee to audit any object in any schema using AUDIT (Schema Objects) statements.
AUDIT SYSTEM Allows grantee to issue AUDIT (SQL Statements) statements.
BACKUP ANY TABLE Allows grantee to use the Export utility to incrementally export objects from the schema of other users.
BECOME USER Allows grantee to become another user. (Required by any user performing a full database import.)
COMMENT ANY TABLE Allows grantee to comment on any table, view, or column in any schema.
CREATE ANY CLUSTER Allows grantee to create a cluster in any schema. Behaves similarly to CREATE ANY TABLE.
CREATE ANY INDEX Allows grantee to create an index in any schema on any table in any schema.
CREATE ANY PROCEDURE Allows grantee to create stored procedures, functions, and packages in any schema.
CREATE ANY SEQUENCE Allows grantee to create a sequence in any schema.
CREATE ANY SNAPSHOT Allows grantee to create snapshots in any schema.
CREATE ANY SYNONYM Allows grantee to create private synonyms in any schema.
CREATE ANY TABLE Allows grantee to create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table.
CREATE ANY TRIGGER Allows grantee to create a database trigger in any schema associated with a table in any schema.
CREATE ANY VIEW Allows grantee to create views in any schema.
CREATE CLUSTER Allows grantee to create clusters in own schema.
CREATE DATABASE LINK Allows grantee to create private database links in own schema.
CREATE PROCEDURE Allows grantee to create stored procedures, functions, and packages in own schema.
CREATE PROFILE Allows grantee to create profiles.
CREATE PUBLIC DATABASE LINK Allows grantee to create public database links.
CREATE PUBLIC SYNONYM Allows grantee to create public synonyms.
CREATE ROLE Allows grantee to create roles.
CREATE ROLLBACK SEGMENT Allows grantee to create rollback segments.
CREATE SEQUENCE Allows grantee to create sequences in own schema.
CREATE SESSION Allows grantee to connect to the database.
CREATE SNAPSHOT Allows grantee to create snapshots in own schema.
CREATE SYNONYM Allows grantee to create synonyms in own schema.
CREATE TABLE Allows grantee to create tables in own schema. To create a table, the grantee must also have space quota on the tablespace to contain the table.
CREATE TABLESPACE Allows grantee to create tablespaces.
CREATE TRIGGER Allows grantee to create a database trigger in own schema.
CREATE USER Allows grantee to 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.
CREATE VIEW Allows grantee to create views in own schema.
DELETE ANY TABLE Allows grantee to delete rows from tables or views in any schema or truncate tables in any schema.
DROP ANY CLUSTER Allows grantee to drop clusters in any schema.
DROP ANY INDEX Allows grantee to drop indexes in any schema.
DROP ANY PROCEDURE Allows grantee to drop stored procedures, functions, or packages in any schema.
DROP ANY ROLE Allows grantee to drop roles.
DROP ANY SEQUENCE Allows grantee to drop sequences in any schema.
DROP ANY SNAPSHOT Allows grantee to drop snapshots in any schema.
DROP ANY SYNONYM Allows grantee to drop private synonyms in any schema.
DROP ANY TABLE Allows grantee to drop tables in any schema.
DROP ANY TRIGGER Allows grantee to drop database triggers in any schema.
DROP ANY VIEW Allows grantee to drop views in any schema
DROP PROFILE Allows grantee to drop profiles.
DROP PUBLIC DATABASE LINK Allows grantee to drop public database links.
DROP PUBLIC SYNONYM Allows grantee to drop public synonyms.
DROP ROLLBACK SEGMENT Allows grantee to drop rollback segments.
DROP TABLESPACE Allows grantee to drop tablespaces.
DROP USER Allows grantee to drop users.
EXECUTE ANY PROCEDURE Allows grantee to execute procedures or functions (stand-alone or packaged) or reference public package variables in any schema.
FORCE ANY TRANSACTION Allows grantee to for the commit or rollback of any in-doubt distributed transaction in the local database. Also allows the grantee to induce the failure of a distributed transaction.
FORCE TRANSACTION Allows grantee to force the commit or rollback of own in-doubt distributed transactions in the local database.
GRANT ANY PRIVILEGE Allows grantee to grant any system privilege.
GRANT ANY ROLE Allows grantee to grant any role in the database.
INSERT ANY TABLE Allows grantee to insert rows into tables and views in any schema.
LOCK ANY TABLE Allows grantee to lock tables and views in any schema.
MANAGE TABLESPACE Allows grantee to take tablespaces offline and online and begin and end tablespace backups.
READUP Allows grantee to query data having an access class higher than the grantee's session label. This privilege is only available in Trusted Oracle7.
RESTRICTED SESSION Allows grantee to logon after the instance is started using the Server Manager STARTUP RESTRICT command.
SELECT ANY SEQUENCE Allows grantee to reference sequences in any schema.
SELECT ANY TABLE Allows grantee to query tables, views, or snapshots in any schema.
UNLIMITED TABLESPACE Allows grantee to use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the grantee's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.
UPDATE ANY TABLE Allows grantee to update rows in tables and views in any schema.
WRITEDOWN Allows grantee to create, alter, and drop schema objects and to insert, update, and delete rows having access classes lower than the grantee's session label. This privilege is only available in Trusted Oracle7.
WRITEUP Allows grantee to create, alter, and drop schema objects and to insert, update, and delete rows having access classes higher than the grantee's session label. This privilege is only available in Trusted Oracle7.

Table 4 - 11. (continued) System Privileges

Roles Defined by Oracle7

Some roles are created automatically by Oracle7. When you create a database, Oracle7 creates these roles and grants them certain system privileges. Table 4 - 12 lists each predefined role and its system privileges.

Role

System Privileges and Roles Granted

CONNECT

ALTER SESSION CREATE CLUSTER CREATE DATABASE LINK CREATE SEQUENCE CREATE SESSION CREATE SYNONYM CREATE TABLE CREATE VIEW

RESOURCE

CREATE CLUSTER CREATE PROCEDURE CREATE SEQUENCE CREATE TABLE CREATE TRIGGER

DBA

All systems privileges WITH ADMIN OPTION EXP_FULL_DATABASEroleIMP_FULL_DATABASErole

EXP_FULL_DATABASE

SELECT ANY TABLE BACKUP ANY TABLE INSERT, UPDATE, DELETE ON sys.incexp sys.incvid sys.incfil

IMP_FULL_DATABASE

BECOME USER WRITEDOWN(in Trusted Oracle7)

Table 4 - 12. Roles defined by Oracle7

Note: If you grant or revoke the RESOURCE or DBA role to or from a user, Oracle7 implicitly grants or revokes the UNLIMITED TABLESPACE system privilege to or from the user.

The CONNECT, RESOURCE, and DBA are provided for compatibility with previous versions of Oracle7. The SQL script SQL.BSQ creates these roles, grants privileges to them, and grants the DBA role with ADMIN OPTION to the users SYS and SYSTEM. This script is available on your distribution media, although its exact name and location may vary depending on your operating system. It is recommended that you to design your own roles for database security, rather than rely on these roles. These roles may not be automatically created by future versions of Oracle7.

The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are provided for convenience in using the Import and Export utilities. The SQL script CATEXP.SQL creates these roles, grants privileges to them, and grants them to the DBA role. This script is available on your distribution media, although its exact name and location may vary depending on your operating system.

DBA Role

Because the DBA role has all system privileges, a common misperception is that no other privileges are required to administer privileges on objects in the database. Although this is generally true, you may still need to grant object privileges to a user granted the DBA role. For example, for USER1 granted the DBA role to create a foreign key constraint against USER2's tables, USER2 must grant the REFERENCES object privilege on the tables to USER1.

ADMIN OPTION

A grant with the ADMIN OPTION supersedes a previous identical grant without the ADMIN OPTION. If you grant a system privilege or role to user without the ADMIN OPTION, and then subsequently grant the privilege or role to the user with the ADMIN OPTION, the user has the ADMIN OPTION on the privilege or role.

A grant without the ADMIN OPTION does not supersede a previous grant with the ADMIN OPTION. 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.

Granting Roles Through Your Operating System

Some operating systems have facilities that grant operating system privileges to operating system users. You can use such facilities to grant roles to Oracle7 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 command, although you can use the GRANT command to grant system privileges to users and system privileges and roles to other roles.

Example I

To grant the CREATE SESSION system privilege to RICHARD, allowing RICHARD to logon to Oracle7, issue the following statement:

GRANT CREATE SESSION

	TO richard

Example II

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.

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.

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:

enable the role and exercise any privileges in the role's privilege domain, including the CREATE TABLE system privilege

grant and revoke the role to and from other users

drop the role

System Privilege	Operations Authorized
`ALTER ANY CLUSTER`	Allows grantee to alter any cluster in any schema.
`ALTER ANY INDEX`	Allows grantee to alter any index in any schema
`ALTER ANY PROCEDURE`	Allows grantee to alter any stored procedure, function, or package in any schema.
`ALTER ANY ROLE`	Allows grantee to alter any role in the database.
`ALTER ANY SEQUENCE`	Allows grantee to alter any sequence in the database.
`ALTER ANY SNAPSHOT`	Allows grantee to alter any snapshot in the database.
`ALTER ANY TABLE`	Allows grantee to alter any table or view in the schema.
`ALTER ANY TRIGGER`	Allows grantee to enable, disable, or compile any database trigger in any schema.
`ALTER DATABASE`	Allows grantee to alter the database.
`ALTER PROFILE`	Allows grantee to alter profiles.
`ALTER RESOURCE COST`	Allows grantee to set costs for session resources.
`ALTER ROLLBACK SEGMENT`	Allows grantee to alter rollback segments.
`ALTER SESSION`	Allows grantee to issue ALTER SESSION statements.
`ALTER SYSTEM`	Allows grantee to issue ALTER SYSTEM statements.
`ALTER TABLESPACE`	Allows grantee to alter tablespaces.
`ALTER USER`	Allows grantee to 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.
`ANALYZE ANY`	Allows grantee to analyze any table, cluster, or index in any schema.
`AUDIT ANY`	Allows grantee to audit any object in any schema using AUDIT (Schema Objects) statements.
`AUDIT SYSTEM`	Allows grantee to issue AUDIT (SQL Statements) statements.
`BACKUP ANY TABLE`	Allows grantee to use the Export utility to incrementally export objects from the schema of other users.
`BECOME USER`	Allows grantee to become another user. (Required by any user performing a full database import.)
`COMMENT ANY TABLE`	Allows grantee to comment on any table, view, or column in any schema.
`CREATE ANY CLUSTER`	Allows grantee to create a cluster in any schema. Behaves similarly to CREATE ANY TABLE.
`CREATE ANY INDEX`	Allows grantee to create an index in any schema on any table in any schema.
`CREATE ANY PROCEDURE`	Allows grantee to create stored procedures, functions, and packages in any schema.
`CREATE ANY SEQUENCE`	Allows grantee to create a sequence in any schema.
`CREATE ANY SNAPSHOT`	Allows grantee to create snapshots in any schema.
`CREATE ANY SYNONYM`	Allows grantee to create private synonyms in any schema.
`CREATE ANY TABLE`	Allows grantee to create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table.
`CREATE ANY TRIGGER`	Allows grantee to create a database trigger in any schema associated with a table in any schema.
`CREATE ANY VIEW`	Allows grantee to create views in any schema.
`CREATE CLUSTER`	Allows grantee to create clusters in own schema.
`CREATE DATABASE LINK`	Allows grantee to create private database links in own schema.
`CREATE PROCEDURE`	Allows grantee to create stored procedures, functions, and packages in own schema.
`CREATE PROFILE`	Allows grantee to create profiles.
`CREATE PUBLIC DATABASE LINK`	Allows grantee to create public database links.
`CREATE PUBLIC SYNONYM`	Allows grantee to create public synonyms.
`CREATE ROLE`	Allows grantee to create roles.
`CREATE ROLLBACK SEGMENT`	Allows grantee to create rollback segments.
`CREATE SEQUENCE`	Allows grantee to create sequences in own schema.
`CREATE SESSION`	Allows grantee to connect to the database.
`CREATE SNAPSHOT`	Allows grantee to create snapshots in own schema.
`CREATE SYNONYM`	Allows grantee to create synonyms in own schema.
`CREATE TABLE`	Allows grantee to create tables in own schema. To create a table, the grantee must also have space quota on the tablespace to contain the table.
`CREATE TABLESPACE`	Allows grantee to create tablespaces.
`CREATE TRIGGER`	Allows grantee to create a database trigger in own schema.
`CREATE USER`	Allows grantee to 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.
`CREATE VIEW`	Allows grantee to create views in own schema.
`DELETE ANY TABLE`	Allows grantee to delete rows from tables or views in any schema or truncate tables in any schema.
`DROP ANY CLUSTER`	Allows grantee to drop clusters in any schema.
`DROP ANY INDEX`	Allows grantee to drop indexes in any schema.
`DROP ANY PROCEDURE`	Allows grantee to drop stored procedures, functions, or packages in any schema.
`DROP ANY ROLE`	Allows grantee to drop roles.
`DROP ANY SEQUENCE`	Allows grantee to drop sequences in any schema.
`DROP ANY SNAPSHOT`	Allows grantee to drop snapshots in any schema.
`DROP ANY SYNONYM`	Allows grantee to drop private synonyms in any schema.
`DROP ANY TABLE`	Allows grantee to drop tables in any schema.
`DROP ANY TRIGGER`	Allows grantee to drop database triggers in any schema.
`DROP ANY VIEW`	Allows grantee to drop views in any schema
`DROP PROFILE`	Allows grantee to drop profiles.
`DROP PUBLIC DATABASE LINK`	Allows grantee to drop public database links.
`DROP PUBLIC SYNONYM`	Allows grantee to drop public synonyms.
`DROP ROLLBACK SEGMENT`	Allows grantee to drop rollback segments.
`DROP TABLESPACE`	Allows grantee to drop tablespaces.
`DROP USER`	Allows grantee to drop users.
`EXECUTE ANY PROCEDURE`	Allows grantee to execute procedures or functions (stand-alone or packaged) or reference public package variables in any schema.
`FORCE ANY TRANSACTION`	Allows grantee to for the commit or rollback of any in-doubt distributed transaction in the local database. Also allows the grantee to induce the failure of a distributed transaction.
`FORCE TRANSACTION`	Allows grantee to force the commit or rollback of own in-doubt distributed transactions in the local database.
`GRANT ANY PRIVILEGE`	Allows grantee to grant any system privilege.
`GRANT ANY ROLE`	Allows grantee to grant any role in the database.
`INSERT ANY TABLE`	Allows grantee to insert rows into tables and views in any schema.
`LOCK ANY TABLE`	Allows grantee to lock tables and views in any schema.
`MANAGE TABLESPACE`	Allows grantee to take tablespaces offline and online and begin and end tablespace backups.
`READUP`	Allows grantee to query data having an access class higher than the grantee's session label. This privilege is only available in Trusted Oracle7.
`RESTRICTED SESSION`	Allows grantee to logon after the instance is started using the Server Manager STARTUP RESTRICT command.
`SELECT ANY SEQUENCE`	Allows grantee to reference sequences in any schema.
`SELECT ANY TABLE`	Allows grantee to query tables, views, or snapshots in any schema.
`UNLIMITED TABLESPACE`	Allows grantee to use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the grantee's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.
`UPDATE ANY TABLE`	Allows grantee to update rows in tables and views in any schema.
`WRITEDOWN`	Allows grantee to create, alter, and drop schema objects and to insert, update, and delete rows having access classes lower than the grantee's session label. This privilege is only available in Trusted Oracle7.
`WRITEUP`	Allows grantee to create, alter, and drop schema objects and to insert, update, and delete rows having access classes higher than the grantee's session label. This privilege is only available in Trusted Oracle7.