Oracle7 Server Administrator's Guide

Identifying User Privileges

This section describes Oracle user privileges, and includes the following topics:

System Privileges

Object Privileges

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. Oracle also provides shortcuts for grouping privileges that are commonly granted or revoked together.

System Privileges

There are over 80 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations. Table 20 - 1 lists all system privileges and the operations that they permit.

Warning: System privileges are very powerful, and should be cautiously granted to roles and trusted users of the database.

System Privilege Operations Permitted
ANALYZE
ANALYZE ANY Analyze any table, cluster, or index in the database.
AUDIT
AUDIT ANY Audit any schema object in the database.
AUDIT SYSTEM Enable and disable statement and privilege audit options.
CLUSTER
CREATE CLUSTER Create a cluster in own schema.
CREATE ANY CLUSTER Create a cluster in any schema. Behaves similarly to CREATE ANY TABLE.
ALTER ANY CLUSTER Alter any cluster in the database.
DROP ANY CLUSTER Drop any cluster in the database.
DATABASE
ALTER DATABASE Alter the database; add files to the operating system via Oracle, regardless of operating system privileges.
DATABASE LINK
CREATE DATABASE LINK Create private database links in own schema.
INDEX
CREATE ANY INDEX Create an index in any schema on any table.
ALTER ANY INDEX Alter any index in the database.
DROP ANY INDEX Drop any index in the database.
PRIVILEGE
GRANT ANY PRIVILEGE Grant any system privilege (not object privileges).
PROCEDURE
CREATE PROCEDURE Create stored procedures, functions, and packages in own schema.
CREATE ANY PROCEDURE Create stored procedures, functions, and packages in any schema. (Requires that user also have ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, or GRANT ANY TABLE.)
ALTER ANY PROCEDURE Compile any stored procedure, function, or package in any schema.
DROP ANY PROCEDURE Drop any stored procedure, function, or package in any schema.
EXECUTE ANY PROCEDURE Execute any procedure or function (stand-alone or packaged), or reference any public package variable in any schema.
PROFILE
CREATE PROFILE Create profiles.
ALTER PROFILE Alter any profile in the database.
DROP PROFILE Drop any profile in the database.
ALTER RESOURCE COST Set costs for resources used in all user sessions.
PUBLIC DATABASE LINK
CREATE PUBLIC DATABASE LINK Create public database links.
DROP PUBLIC DATABASE LINK Drop public database links.
PUBLIC SYNONYM
CREATE PUBLIC SYNONYM Create public synonyms.
DROP PUBLIC SYNONYM Drop public synonyms.
ROLE
CREATE ROLE Create roles.
ALTER ANY ROLE Alter any role in the database.
DROP ANY ROLE Drop any role in the database.
GRANT ANY ROLE Grant any role in the database.
ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT Create rollback segments.
ALTER ROLLBACK SEGMENT Alter rollback segments.
DROP ROLLBACK SEGMENT Drop rollback segments.
SESSION
CREATE SESSION Connect to the database.
ALTER SESSION Issue ALTER SESSION statements.
RESTRICTED SESSION Connect when the database has been started using STARTUP RESTRICT. (The OSOPER and OSDBA roles contain this privilege.)
SEQUENCE
CREATE SEQUENCE Create a sequence in own schema.
CREATE ANY SEQUENCE Create any sequence in any schema.
ALTER ANY SEQUENCE Alter any sequence in any schema.
DROP ANY SEQUENCE Drop any sequence in any schema.
SELECT ANY SEQUENCE Reference any sequence in any schema.
SNAPSHOT
CREATE SNAPSHOT Create snapshots in own schema. (User must also have the CREATE TABLE privilege.)
CREATE SNAPSHOT Create snapshots in any schema. (User must also have the CREATE ANY TABLE privilege.)
ALTER SNAPSHOT Alter any snapshot in any schema.
DROP ANY SNAPSHOT Drop any snapshot in any schema.
SYNONYM
CREATE SYNONYM Create a synonym in own schema.
CREATE SYNONYM Create any synonym in any schema.
DROP ANY SYNONYM Drop any synonym in any schema.
SYSTEM
ALTER SYSTEM Issue ALTER SYSTEM statements.
TABLE
CREATE TABLE Create tables in own schema. Also allows grantee to create indexes (including those for integrity constraints) on table in own schema. (The grantee must have a quota for the tablespace or the UNLIMITED TABLESPACE privilege.)
CREATE ANY TABLE Create tables in any schema. (If grantee has CREATE ANY TABLE privilege and creates a table in another user's schema, the owner must have space quota on that tablespace. The table owner need not have the CREATE [ANY] TABLE privilege.)
ALTER ANY TABLE Alter any table in any schema and compile any view in any schema.
BACKUP ANY TABLE Perform an incremental export using the Export utility of tables in any schema.
DROP ANY TABLE Drop or truncate any table in any schema.
LOCK ANY TABLE Lock any table or view in any schema.
COMMENT ANY TABLE Comment on any table, view, or column in schema.
SELECT ANY TABLE Query any table, view, or snapshot in any schema.
INSERT ANY TABLE Insert rows into any table or view in any schema.
UPDATE ANY TABLE Update rows in any table or view in any schema.
DELETE ANY TABLE Delete rows from any table or view in any schema.
TABLESPACE
CREATE TABLE SPACE Create tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
ALTER TABLESPACE Alter tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
MANAGE TABLESPACE Take any tablespace offline, bring any tablespace online, and begin and end backups of any tablespace.
DROP TABLESPACE Drop tablespaces.
UNLIMITED TABLESPACE Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If revoked, the grantee's schema objects remain but further tablespace allocation is denied unless allowed by specific tablespace quotas. This system privilege can be granted only to users and not to roles. In general, specific tablespace quotas are assigned instead of granting this system privilege.
TRANSACTION
FORCE TRANSACTION Force the commit or rollback of own in-doubt distributed transaction in the local database.
FORCE ANY TRANSACTION Force the commit or rollback of any in-doubt distributed transaction in the local database.
TRIGGER
CREATE TRIGGER Create a trigger in own schema.
CREATE ANY TRIGGER Create any trigger in any schema associated with any table in any schema.
ALTER ANY TRIGGER Enable, disable, or compile any trigger in any schema.
DROP ANY TRIGGER Drop any trigger in any schema.
USER
CREATE ANY USER Create users; assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a CREATE USER statement.
BECOME ANY USER Become another user. (Required by any user performing a full database import.)
ALTER USER Alter other users: change any user's password or authentication method, assign tablespace quotas, set default and temporary tablespaces, assign profiles and default roles, in an ALTER USER statement. (Not required to alter own password.)
DROP USER Drop another user.
VIEW
CREATE VIEW Create a view in own schema.
CREATE ANY VIEW Create a view in any schema. (Requires that user also have ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, COMMENT ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, or GRANT ANY TABLE.)
DROP ANY VIEW Drop any view in any schema.

Table 20 - 1. System Privileges

Object Privileges

Each type of object has different privileges associated with it. Table 20 - 2 summarizes the object privileges available for each type of object.

Object Privilege Table View Sequence Procedure¹
ALTER _/ _/
DELETE _/ _/
EXECUTE _/
INDEX _/²
INSERT _/ _/
REFERENCES _/²
SELECT _/ _/³ _/
UPDATE _/ _/

Table 20 - 2. Object Privileges

¹ Includes stand-alone stored procedures and functions, and public package constructs.
² Privilege cannot be granted to a role.
³ Can also be granted for snapshots.

Not all types of schema objects are included in Table 20 - 2. Many of the schema objects not listed here (such as clusters, indexes, triggers, and database links) are controlled exclusively using system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER system privilege.

Table 20 - 3 lists the SQL statements permitted by the object privileges listed in Table 20 - 2.

Object Privilege SQL Statements Permitted
ALTER ALTER object (table or sequence)
DELETE DELETE FROM object (table or view)
EXECUTE EXECUTE object (procedure or function). References to public package variables
INDEX CREATE INDEX ON object (tables only)
INSERT INSERT INTO object (table or view)
REFERENCES CREATE or ALTER TABLE statement defining a FOREIGN KEY integrity constraint on object (tables only)
SELECT SELECT...FROM object (table, view, or snapshot). SQL statements using a sequence
UPDATE UPDATE object (table or view)

Table 20 - 3. SQL Statements Permitted by Object Privileges

Object Privilege Shortcut

The ALL and ALL PRIVILEGES shortcuts grant or revoke all available object privileges for a object. This shortcut is not a privilege, rather, it is a way of granting or revoking all object privileges with one word in GRANT and REVOKE statements. Note that if all object privileges are granted using the ALL shortcut, individual privileges can still be revoked.

Likewise, all individually granted privileges can be revoked using the ALL shortcut. However, if you REVOKE ALL, and revoking causes integrity constraints to be deleted (because they depend on a REFERENCES privilege that you are revoking), you must include the CASCADE CONSTRAINTS option in the REVOKE statement.

System Privilege	Operations Permitted
ANALYZE
ANALYZE ANY	Analyze any table, cluster, or index in the database.
AUDIT
AUDIT ANY	Audit any schema object in the database.
AUDIT SYSTEM	Enable and disable statement and privilege audit options.
CLUSTER
CREATE CLUSTER	Create a cluster in own schema.
CREATE ANY CLUSTER	Create a cluster in any schema. Behaves similarly to CREATE ANY TABLE.
ALTER ANY CLUSTER	Alter any cluster in the database.
DROP ANY CLUSTER	Drop any cluster in the database.
DATABASE
ALTER DATABASE	Alter the database; add files to the operating system via Oracle, regardless of operating system privileges.
DATABASE LINK
CREATE DATABASE LINK	Create private database links in own schema.
INDEX
CREATE ANY INDEX	Create an index in any schema on any table.
ALTER ANY INDEX	Alter any index in the database.
DROP ANY INDEX	Drop any index in the database.
PRIVILEGE
GRANT ANY PRIVILEGE	Grant any system privilege (not object privileges).
PROCEDURE
CREATE PROCEDURE	Create stored procedures, functions, and packages in own schema.
CREATE ANY PROCEDURE	Create stored procedures, functions, and packages in any schema. (Requires that user also have ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, or GRANT ANY TABLE.)
ALTER ANY PROCEDURE	Compile any stored procedure, function, or package in any schema.
DROP ANY PROCEDURE	Drop any stored procedure, function, or package in any schema.
EXECUTE ANY PROCEDURE	Execute any procedure or function (stand-alone or packaged), or reference any public package variable in any schema.
PROFILE
CREATE PROFILE	Create profiles.
ALTER PROFILE	Alter any profile in the database.
DROP PROFILE	Drop any profile in the database.
ALTER RESOURCE COST	Set costs for resources used in all user sessions.
PUBLIC DATABASE LINK
CREATE PUBLIC DATABASE LINK	Create public database links.
DROP PUBLIC DATABASE LINK	Drop public database links.
PUBLIC SYNONYM
CREATE PUBLIC SYNONYM	Create public synonyms.
DROP PUBLIC SYNONYM	Drop public synonyms.
ROLE
CREATE ROLE	Create roles.
ALTER ANY ROLE	Alter any role in the database.
DROP ANY ROLE	Drop any role in the database.
GRANT ANY ROLE	Grant any role in the database.
ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT	Create rollback segments.
ALTER ROLLBACK SEGMENT	Alter rollback segments.
DROP ROLLBACK SEGMENT	Drop rollback segments.
SESSION
CREATE SESSION	Connect to the database.
ALTER SESSION	Issue ALTER SESSION statements.
RESTRICTED SESSION	Connect when the database has been started using STARTUP RESTRICT. (The OSOPER and OSDBA roles contain this privilege.)
SEQUENCE
CREATE SEQUENCE	Create a sequence in own schema.
CREATE ANY SEQUENCE	Create any sequence in any schema.
ALTER ANY SEQUENCE	Alter any sequence in any schema.
DROP ANY SEQUENCE	Drop any sequence in any schema.
SELECT ANY SEQUENCE	Reference any sequence in any schema.
SNAPSHOT
CREATE SNAPSHOT	Create snapshots in own schema. (User must also have the CREATE TABLE privilege.)
CREATE SNAPSHOT	Create snapshots in any schema. (User must also have the CREATE ANY TABLE privilege.)
ALTER SNAPSHOT	Alter any snapshot in any schema.
DROP ANY SNAPSHOT	Drop any snapshot in any schema.
SYNONYM
CREATE SYNONYM	Create a synonym in own schema.
CREATE SYNONYM	Create any synonym in any schema.
DROP ANY SYNONYM	Drop any synonym in any schema.
SYSTEM
ALTER SYSTEM	Issue ALTER SYSTEM statements.
TABLE
CREATE TABLE	Create tables in own schema. Also allows grantee to create indexes (including those for integrity constraints) on table in own schema. (The grantee must have a quota for the tablespace or the UNLIMITED TABLESPACE privilege.)
CREATE ANY TABLE	Create tables in any schema. (If grantee has CREATE ANY TABLE privilege and creates a table in another user's schema, the owner must have space quota on that tablespace. The table owner need not have the CREATE [ANY] TABLE privilege.)
ALTER ANY TABLE	Alter any table in any schema and compile any view in any schema.
BACKUP ANY TABLE	Perform an incremental export using the Export utility of tables in any schema.
DROP ANY TABLE	Drop or truncate any table in any schema.
LOCK ANY TABLE	Lock any table or view in any schema.
COMMENT ANY TABLE	Comment on any table, view, or column in schema.
SELECT ANY TABLE	Query any table, view, or snapshot in any schema.
INSERT ANY TABLE	Insert rows into any table or view in any schema.
UPDATE ANY TABLE	Update rows in any table or view in any schema.
DELETE ANY TABLE	Delete rows from any table or view in any schema.
TABLESPACE
CREATE TABLE SPACE	Create tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
ALTER TABLESPACE	Alter tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
MANAGE TABLESPACE	Take any tablespace offline, bring any tablespace online, and begin and end backups of any tablespace.
DROP TABLESPACE	Drop tablespaces.
UNLIMITED TABLESPACE	Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If revoked, the grantee's schema objects remain but further tablespace allocation is denied unless allowed by specific tablespace quotas. This system privilege can be granted only to users and not to roles. In general, specific tablespace quotas are assigned instead of granting this system privilege.
TRANSACTION
FORCE TRANSACTION	Force the commit or rollback of own in-doubt distributed transaction in the local database.
FORCE ANY TRANSACTION	Force the commit or rollback of any in-doubt distributed transaction in the local database.
TRIGGER
CREATE TRIGGER	Create a trigger in own schema.
CREATE ANY TRIGGER	Create any trigger in any schema associated with any table in any schema.
ALTER ANY TRIGGER	Enable, disable, or compile any trigger in any schema.
DROP ANY TRIGGER	Drop any trigger in any schema.
USER
CREATE ANY USER	Create users; assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a CREATE USER statement.
BECOME ANY USER	Become another user. (Required by any user performing a full database import.)
ALTER USER	Alter other users: change any user's password or authentication method, assign tablespace quotas, set default and temporary tablespaces, assign profiles and default roles, in an ALTER USER statement. (Not required to alter own password.)
DROP USER	Drop another user.
VIEW
CREATE VIEW	Create a view in own schema.
CREATE ANY VIEW	Create a view in any schema. (Requires that user also have ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, COMMENT ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, or GRANT ANY TABLE.)
DROP ANY VIEW	Drop any view in any schema.

Object Privilege	Table	View	Sequence	Procedure¹
ALTER	_/		_/
DELETE	_/	_/
EXECUTE				_/
INDEX	_/²
INSERT	_/	_/
REFERENCES	_/²
SELECT	_/	_/³	_/
UPDATE	_/	_/

Object Privilege	SQL Statements Permitted
ALTER	ALTER object (table or sequence)
DELETE	DELETE FROM object (table or view)
EXECUTE	EXECUTE object (procedure or function). References to public package variables
INDEX	CREATE INDEX ON object (tables only)
INSERT	INSERT INTO object (table or view)
REFERENCES	CREATE or ALTER TABLE statement defining a FOREIGN KEY integrity constraint on object (tables only)
SELECT	SELECT...FROM object (table, view, or snapshot). SQL statements using a sequence
UPDATE	UPDATE object (table or view)