Privileges

Oracle supports a rich selection of privileges that are assigned with the GRANT command, and removed with the REVOKE command.

System Privileges

System privileges are granted and revoked to users and roles and generally apply to an entire class or group of objects. To be able to GRANT or REVOKE a system privilege, the user must have been granted the privilege with the ADMIN OPTION, or have the GRANT ANY PRIVILEGE system privilege.

Table 1-17. System Privileges

Class	Privilege	Applies To
Clusters	Create Cluster	A cluster in its own schema
	Create Any Cluster	Any cluster in any schema
	Alter Any Cluster	Any cluster in any schema
	Drop Any Cluster	Any cluster in any schema
Contexts	Create Any Context	Any context namespace
	Drop Any Context	Any context namespace
Database	Alter Database	The database
	Alter System	ALTER SYSTEM statements
	Audit System	AUDIT sql statements
Database Links	Create Database Link	Private links in own schema
	Create Public Database Links	Public database links
	Drop Public Database Links	Public database links
Debugging	Debug Connect Session	Current Session can be connected to a JDWP (Java Debug Wire Protocol) debugger
	Debug Any Procedure	All PL/SQL and Java code in any database object
Dimensions	Create Dimension	Dimensions in own schema
	Create Any Dimension	Dimensions in any schema
	Alter Any Dimension	Dimensions in any schema
	Drop Any Dimension	Dimensions in any schema
Directories	Create Any Directory	Directory database objects
	Drop Any Directory	Directory database objects
Indextypes	Create Indextype	Indextypes in own schema
	Create Any Indextype	Indextypes in any schema
	Alter Any Indextype	Indextypes in any schema
	Drop Any Indextype	Indextypes in any schema
	Execute Any Indextype	Indextypes in any schema
Indexes	Create Any Index	Any table in any schema or a domain index in any schema
	Alter Any Index	Any schema
	Drop Any Index	Any schema
	Query Rewrite	Materialized views or function-based index in own schema
	Global Query Rewrite	Materialized views or function-based index in any schema
Libraries	Create Library	External procedure or function library in own schema
	Create Any Library	External procedure or function library in any schema
	Drop Any Library	External procedure or function library in any schema
Materialized Views	Create Materialized View	Materialized view in own schema
	Create Any Materialized View	Materialized view in any schema
	Alter Any Materialized View	Materialized view in any schema
	Drop Any Materialized View	Materialized view in any schema
	Query Rewrite	Materialized views or function-based index in own schema
	Global Query Rewrite	Materialized views or function-based index in any schema
	On Commit Refresh	Create a refresh on commit materialized view or alter a refresh on demand materialized view on any table in database
	Flashback Any Table	Any table, view, or materialized view in the database
Operators	Create Operator	Operator in own schema
	Create Any Operator	Operator in any schema
	Drop Any Operator	Operator in any schema
	Execute Any Operator	Operator in any schema
Outlines	Create Any Outline	Public outlines in any schema
	Alter Any Outline	Public outlines in any schema
	Alter Any Outline	Public outlines in any schema
	Select Any Outline	Create a private outline that is a clone of a public one
Procedures	Create Procedure	Stored procedures, functions, and packages in own schema
	Create Any Procedure	Stored procedures, functions, and packages in any schema
	Alter Any Procedure	Stored procedures, functions, and packages in any schema
	Drop Any Procedure	Stored procedures, functions, and packages in any schema
	Execute Any Procedure	Stored procedures, functions, and packages in any schema
Profiles	Create Profile	Profiles
	Alter Profile	Profiles
	Drop Profile	Profiles
Roles	Create Role	Create new roles
	Alter Any Role	Any role in the database
	Drop Any Role	Any role in the database
	Grant Any Role	Any role in the database
Rollback Segments	Create Rollback Segment	Rollback segments
	Alter Rollback Segments	Rollback segments
	Drop Rollback Segments	Rollback segments
Sequences	Create Sequence	Sequences in own schema
	Create Any Sequence	Sequences in any schema
	Alter Any Sequence	Sequences in any schema
	Drop Any Sequence	Sequences in any schema
	Select Any Sequence	Sequences in any schema
Sessions	Create Session	Connect to database
	Alter Resource Cost	Set resource costs for sessions
	Alter Session	Alter your current session parameters
	Restricted Session	Connect to database when RESTRICTED SESSION is in effect
Synonym	Create Synonym	Synonyms in own schema
	Create Any Synonym	Private synonyms in any schema
	Drop Any Synonym	Private synonyms in any schema
	Create Public Synonym	Public synonyms
	Drop Public Synonym	Public synonyms
Tables	Create Table	Tables in own schema
	Create Any Table	Tables in any schema
	Alter Any Table	Tables or views in any schema
	Back Up Any Table	Export objects from any schema
	Delete Any Table	Rows from tables, views, or table partitions in any schema
	Drop Any Table	Tables or table partitions in any schema (includes TRUNCATE)
	Insert Any Table	Rows into any table or view in any schema
	Lock Any Table	Tables or views in any schema
	Select Any Table	Tables or views in any schema
	Flashback Any Table	Any table, view, or materialized view in the database
	Update Any Table	Rows in any table or view in any schema
Tablespaces	Create Tablespace	Tablespaces
	Alter Tablespace	Tablespaces
	Drop Tablespace	Tablespaces
	Manage Tablespaces	Online and offline of tablespaces and begin or end backups of tablespaces
	Unlimited Tablespace	Unlimited storage on any tablespace. Overrides specific quotas
Triggers	Create Trigger	Triggers in own schema
	Create Any Trigger	Triggers in any schema
	Alter Any Trigger	Triggers in any schema
	Drop Any Trigger	Triggers in any schema
	Administer Database Trigger	Create trigger on database. Also requires Create Trigger or Create Any Trigger.
Types	Create Type	Object types and bodies in own schema
	Create Any Type	Object types and bodies in any schema
	Alter Any Type	Object types and bodies in any schema
	Drop Any Type	Object types and bodies in any schema
	Execute Any Type	Object types and bodies in any schema
	Under Any Type	Create subtypes
Users	Create User	Create users. Implicitly allows the setting of passwords, quotas, default, and temporary tablespaces and assigning of profiles for new users.
	Alter User	Alter settings for existing users. Implicitly allows the setting of passwords, quotas, default, and temporary tablespaces and assigning of profiles.
	Become User	Change to any other user (required for full database imports)
	Drop User	Drop any user
Views	Create View	Views in own schema
	Create Any View	Views in any schema
	Drop Any View	Views in any schema
	Under Any View	Create subviews of any view
	Flashback Any Table	Any table, view, or materialized view in the database
Miscellaneous	Analyze Any	Any table, cluster, or index in any schema
	Audit Any	Any object in any schema
	Comment Any Table	Any table, view, or column in any schema
	Exempt Access Policy	Bypass access control
	Force Transaction	Own in-doubt distributed transactions
	Force Any Transaction	Force the commit or rollback of any in-doubt distributed transaction
	Grant Any Object Privilege	Grant or revoke any object privilege
	Grant Any Privilege	Grant any system privilege
	Resumable	Enable resumable allocation of space
	Select Any Dictionary	Query SYS data dictionary objects. Overrides an init parameter of FALSE to 07_DICTIONARY_ACCESSIBILITY.
	SYSDBA	STARTUP and SHUTDOWN, ALTER DATABASE, CREATE DATABASE, ARCHIVELOG, RECOVERY, CREATE SPFILE, RESTRICTED SESSION
	SYSOPER	Similar to SYSDBA, but can't create a database, and can't change the character set

Warning

The grant of an ANY privilege gives the grantee the rights to that type of object in ALL schemas, including SYS, unless the database is started with an init parameter of:

07_DICTIONARY_ACCESSIBILITY = FALSE

When this parameter is set to FALSE, the ANY privilege applies to all schemas except SYS.

Object Privileges

In addition to System Privileges, Oracle supports assignment of privileges at the object level as well.

Table 1-18. Object Privileges

Privilege	Object	Explanation
Alter	Table	Modify the table definitions
	Sequence	Modify the sequence definition
Debug	Table	Use debugger on PL/SQL triggers on the table and SQL statements that reference the table
	View	Use debugger on PL/SQL triggers on the view and SQL statements that reference the view
	Procedures, Functions, and Packages	Use debugger to access all variables , methods , and types. Place breakpoints and stops.
Delete	Table	Delete rows
	View	Delete rows in the view
	Materialized View	Delete rows in the materialized view
Execute	Procedures, Functions, and Packages	Compile, or access public variables, methods and types through a debugger. Not required for indirect execution of the Procedure, function, or package.
	Library	Use the library and invoke its methods
	Operator	Reference the operator
	Indextype	Reference the Indextype
Flashback	Table	Issue a flashback query on the table
	View	Issue a flashback query on the view
	Materialized View	Issue a flashback query on the materialized view
Index	Table	Create indices on the table
Insert	Table	Add rows to the table
	View	Add rows to the view
	Materialized View	Add rows to the materialized view
On Commit Refresh	Table	Create a materialized refresh on commit on the table. (Note: the privilege is on the table, not on the resultant view.)
Query Rewrite	Table	Create a materialized view on the table for Query Rewrite. (Note: the privilege is on the table, not on the resultant view.)
Read	Directory	Gives read permission on files stored on the operating system directory referenced
References	Table	Create a constraint that references the table
	View	Create foreign key constraints that reference the view
Select	Table	Query the table. (You will need this privilege in addition to UPDATE and DELETE privileges if the database you're modifying is on a remote database.)
	View	Query the view
	Sequence	Get and increment the value of the sequence
	Materialized View	Query the materialized view
Under	View	Create subviews of the view
Update	Table	Modify data in the table using the UPDATE statement
	View	Modify data in the view
	Materialized View	Modify the data in the materialized view
Write	Directory	Gives write permission into the operating system directory referenced