GRANT system_privileges_and_roles

Syntax

Purpose

To grant system privileges and roles to users and roles. Both privileges and roles are either local, global, or external (see "CREATE USER" and "CREATE ROLE" for definitions).

You can authorize database users to use roles through means other than the database and the GRANT statement. For example, some operating systems have facilities that grant operating system privileges to operating system users. You can use such facilities to grant roles to Oracle 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 statement, although you can use the GRANT statement to grant system privileges to users and system privileges and roles to other roles. For information about other authorization methods, see Oracle8i Administrator's Guide.

For information on granting object privileges, see "GRANT object_privileges".

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.

Keywords and Parameters

system_priv	is a system privilege to be granted. Table 7-5 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:
	ALL PRIVILEGES	is equivalent to specifying all the system privileges listed in Table 7-5, " System Privileges".
	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, you cannot subsequently grant TELLER to BANKER.
role	is a role to be granted. You can grant an Oracle predefined role or a user-defined role. Table 7-6 lists the predefined roles. For information on creating a user-defined role, see "CREATE ROLE".
	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.
TO	identifies users or roles to which system privileges and roles are granted. Restriction: A user, role, or PUBLIC cannot appear more than once in the TO clause.
PUBLIC	grants system privileges or roles to all users.
WITH ADMIN OPTION	enables 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.

Table 7-5  System Privileges

System Privilege	Allows grantee to . . .
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 7-6 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. See Oracle8i 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.
DELETE_CATALOG_ROLE EXECUTE_CATALOG_ROLE SELECT_CATALOG_ROLE	These roles are provided for accessing data dictionary views and packages. See Also: Oracle8i Administrator's Guide for more information on these roles.
EXP_FULL_DATABASE IMP_FULL_DATABASE	These roles are provided for convenience in using the Import and Export utilities. See Also: Oracle8i Utilities for more information on these roles.
AQ_USER_ROLE AQ_ADMINISTRATOR_ROLE	You need these roles to use Oracle's Advanced Queuing functionality. See Also: Oracle8i Application Developer's Guide - Advanced Queuing for more information on these roles.
SNMPAGENT	This role is used by Enterprise Manager/Intelligent Agent. See Also: Oracle Enterprise Manager Administrator's Guide.
RECOVERY_CATALOG_OWNER	You need this role to create a user who owns a recovery catalog. See Also: Oracle8i Backup and Recovery Guide for more information on recovery catalogs.
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. See Also: Oracle8i Distributed Database Systems and Oracle8i Supplied PL/SQL Packages Reference for more information.
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.

Examples

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; 

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