Oracle System Privileges

Oracle System Privileges Version 11.2.0.3

General Information

Note: System privileges are privileges that do not relate to a specific schema or object.

Data Dictionary Objects

ALL_SYS_PRIVS	SESSION_PRIVS	USER_SYS_PRIVS
DBA_SYS_PRIVS	SYSTEM_PRIVILEGE_MAP

Administer

Administer Any SQL Tuning Set
Administer Database Trigger (database level trigger)
Administer Resource Manager
Administer SQL Management Object
Administer SQL Tuning Set
Flashback Archive Administer
Grant Any Object Privilege
Grant Any Privilege
Grant Any Role
Manage Scheduler
Manage Tablespace

Advanced Queuing

Dequeue Any Queue
Enqueue Any Queue
Manage Any Queue

Advisor Framework

Advisor
Administer SQL Tuning Set
Administer Any SQL Tuning Set
Administer SQL Management Object
Alter Any SQL Profile
Create Any SQL Profile (deprecated in 11.2.0.2: Use Administer SQL Management Object)
Drop Any SQL Profile

Alter Any Privileges

Alter Any Assembly
Alter Any Cluster
Alter Any Cube
Alter Any Cube Dimension
Alter Any Dimension
Alter Any Edition
Alter Any Evaluation Context
Alter Any Index
Alter Any Indextype
Alter Any Library
Alter Any Materialized View
Alter Any Mining Model
Alter Any Operator
Alter Any Outline
Alter Any Procedure
Alter Any Role
Alter Any Rule
Alter Any Rule Set
Alter Any Sequence
Alter Any SQL Profile
Alter Any Table
Alter Any Trigger
Alter Any Type

Alter Privileges

Alter Database
Alter Database Link
Alter Profile
Alter Public Database Link
Alter Resource Cost
Alter Rollback Segment
Alter Session
Alter System
Alter Tablespace
Alter User

Analyze Privileges

Analyze Any
Analyze Any Dictionary

Assembly Privileges

Alter Any Assembly
Create Any Assembly
Create Assembly
Drop Any Assembly
Execute Any Assembly
Execute Assembly

Audit Privileges

Audit Any
Audit System

Backup Privileges

Backup Any Table

Clusters

Alter Any Cluster
Create Any Cluster
Create Cluster
Drop Any Cluster

Comment Privileges

Comment Any Mining Model
Comment Any Table

Contexts

Create Any Context
Drop Any Context

Create Any Privileges

Create Any Assembly
Create Any Cluster
Create Any Context
Create Any Cube
Create Any Cube Build Process
Create Any Cube Dimension
Create Any Dimension
Create Any Directory
Create Any Edition
Create Any Evaluation Context
Create Any Index
Create Any Indextype
Create Any Job
Create Any Library
Create Any Materialized View
Create Any Measure Folder
Create Any Mining Model
Create Any Operator
Create Any Outline
Create Any Procedure
Create Any Rule
Create Any Rule Set
Create Any Sequence
Create Any SQL Profile (deprecated in 11.2.0.2: Use Administer SQL Management Object)
Create Any Synonym
Create Any Table
Create Any Trigger
Create Any Type
Create Any View

Create Privileges

Create Assembly
Create Cluster
Create Cube
Create Cube Build Process
Create Cube Dimension
Create Database Link
Create Dimension
Create Evaluation Context
Create External Job
Create Indextype
Create Job
Create Library
Create Materialized View
Create Measure Folder
Create Mining Model
Create Operator
Create Procedure
Create Profile
Create Public Database Link
Create Public Synonym
Create Role
Create Rollback Segment
Create Rule
Create Rule Set
Create Sequence
Create Session
Create Synonym
Create Table
Create Tablespace
Create Trigger
Create Type
Create User
Create View

Database

Alter Database
Alter System
Audit System

Database Links

Alter Database Link
Alter Public Database Link
Create Database Link
Create Public Database Link
Drop Public Database Link

Debug

Debug Any Procedure
Debug Connect Session

Delete

Delete Any Cube Dimension
Delete Any Measure Folder
Delete Any Table

Dimensions

Alter Any Dimension
Create Any Dimension
Create Dimension
Drop Any Dimension

Directories

Create Any Directory
Drop Any Directory

Drop Any Privileges

Drop Any Assembly
Drop Any Cluster
Drop Any Context
Drop Any Cube
Drop Any Cube Build Process
Drop Any Cube Dimension
Drop Any Dimension
Drop Any Directory
Drop Any Edition
Drop Any Evaluation Context
Drop Any Index
Drop Any Indextype
Drop Any Library
Drop Any Materialized View
Drop Any Measure Folder
Drop Any Mining Model
Drop Any Operator
Drop Any Outline
Drop Any Procedure
Drop Any Role
Drop Any Rule
Drop Any Rule Set
Drop Any Sequence
Drop Any SQL Profile
Drop Any Synonym
Drop Any Table
Drop Any Trigger
Drop Any Type
Drop Any View

Drop Privileges

Drop Profile
Drop Public Database Link
Drop Public Synonym
Drop Rollback Segment
Drop Tablespace
Drop User

Editions

Alter Any Edition
Create Any Edition
Drop Any Edition

Enterprise Manager

EM Express Connect

Evaluation Context

Alter Any Evaluation Context
Create Any Evaluation Context
Create Evaluation Context
Drop Any Evaluation Context
Execute Any Evaluation Context

Execute Privileges

Execute Any Assembly
Execute Any Class
Execute Any Evaluation Context
Execute Any Indextype
Execute Any Library
Execute Any Operator
Execute Any Procedure
Execute Any Program
Execute Any Rule
Execute Any Rule Set
Execute Any Type
Execute Assembly

Exempt Privileges

Exempt Access Policy
Exempt Identity Policy

Export & Import

Export Full Database
Import Full Database

Fine Grained Access Control

Exempt Access Policy (bypasses FGAC)

File Group

Manage Any File Group
Manage File Group
Read Any File Group

Flashback

Flashback Any Table
Flashback Archive Administer

Force

Force Any Transaction
Force Transaction

Grant

Grant Any Object Privilege
Grant Any Privilege
Grant Any Role

Indexes

Alter Any Index
Create Any Index
Drop Any Index

Indextype

Alter Any Indextype
Create Any Indextype
Create Indextype
Drop Any Indextype
Execute Any Indextype

Insert

Insert Any Cube Dimension
Insert Any Measure Folder
Insert Any Table

Job Scheduler

Create Any Job
Create External Job
Create Job
Execute Any Class
Execute Any Program
Manage Scheduler

Libraries

Alter Any Library
Create Any Library
Create Library
Drop Any Library
Execute Any Library

Locks

Lock Any Table

Materialized Views

Alter Any Materialized View
Create Any Materialized View
Create Materialized View
Drop Any Materialized View
Flashback Any Table
Global Query Rewrite
On Commit Refresh
Query Rewrite

Measure Folders

Create Any Measure Folder
Create Measure Folder
Delete Any Measure Folder
Drop Any Measure Folder
Insert Any Measure Folder

Mining Models

Alter Any Mining Model
Comment Any Mining Model
Create Any Mining Model
Create Mining Model
Drop Any Mining Model
Select Any Mining Model

Notification Privilege

Change Notification

OLAP Cubes

Alter Any Cube
Create Any Cube
Create Cube
Drop Any Cube
Select Any Cube
Update Any Cube

OLAP Cube Build

Alter Any Cube Build Process
Create Any Cube Build Process
Create Cube Build Process
Drop Any Cube Build Process
Update Any Cube Build Process

OLAP Cube Dimensions

Alter Any Cube Dimension
Create Any Cube Dimension
Create Cube Dimension
Delete Any Cube Dimension
Drop Any Cube Dimension
Insert Any Cube Dimension
Select Any Cube Dimension
Update Any Cube Dimension

OLAP Cube Measure Folders

Create Any Measure Folder
Create Measure Folder
Delete Any Measure Folder
Drop Any Measure Folder
Insert Any Measure Folder

Operator

Alter Any Operator
Create Any Operator
Create Operator
Drop Any Operator
Execute Any Operator

Outlines

Alter Any Outline
Create Any Outline
Drop Any Outline

Plan Management

Administer SQL Management Object

Procedures

Alter Any Procedure
Create Any Procedure
Create Procedure
Drop Any Procedure
Execute Any Procedure

Profiles

Alter Profile
Create Profile
Drop Profile

Query Rewrite

Global Query Rewrite
Query Rewrite

Real Application Testing

Keep DATE TIME
Keep SYSGUID

Refresh

On Commit Refresh

Resumable

Resumable

Roles

Alter Any Role
Create Role
Drop Any Role
Grant Any Role

Rollback Segment

Alter Rollback Segment
Create Rollback Segment
Drop Rollback Segment

Scheduler

Manage Scheduler

Select

Select Any Cube
Select Any Cube Dimension
Select Any Dictionary
Select Any Mining Model
Select Any Sequence
Select Any Table
Select Any Transaction

Sequence

Alter Any Sequence
Create Any Sequence
Create Sequence
Drop Any Sequence
Select Any Sequence

Session

Alter Resource Cost
Alter Session
Create Session
Restricted Session

Synonym

Create Any Synonym
Create Public Synonym
Create Synonym
Drop Any Synonym
Drop Public Synonym

System Privileges

SYSDBA
SYSOPER

Tables

Alter Any Table
Backup Any Table
Comment Any Table
Create Any Table
Create Table
Delete Any Table
Drop Any Table
Flashback Any Table
Insert Any Table
Lock Any Table
Select Any Table
Under Any Table
Update Any Table

Tablespaces

Alter Tablespace
Create Tablespace
Drop Tablespace
Manage Tablespace
Unlimited Tablespace

Transactions

Force Any Transaction
Force Transaction

Translation Profile

Create Any SQL Translation Profile
Drop Any SQL Translation Profile

Triggers

Administer Database Trigger
Alter Any Trigger
Create Any Trigger
Create Trigger
Drop Any Trigger

Types

Alter Any Type
Create Any Type
Create Type
Drop Any Type
Execute Any Type
Under Any Type

Update

Update Any Cube
Update Any Cube Build Process
Update Any Cube Dimension
Update Any Table

Under

Under Any Table
Under Any Type
Under Any View

User

Alter User
Become User
Create User
Drop User

View

Create Any View
Create View
Drop Any View
Flashback Any Table
Merge Any View
Under Any View

Granting System Privileges

Grant A Single Privilege

GRANT <privilege_name> TO <schema_name>;

GRANT create table TO uwclass;

Grant Multiple Privileges

GRANT <privilege_name, privilege_name, ...> TO <schema_name>;

GRANT create table, create view, create procedure TO uwclass;

Revoking System Privileges

Revoke A Single Privilege

REVOKE <privilege_name> FROM <schema_name>;

REVOKE create table FROM uwclass;

Revoke Multiple Privileges

REVOKE <privilege_name, privilege_name, ...> FROM <schema_name>;

REVOKE create table, create view FROM uwclass;

Determine User Privs

This query will list the system privileges assigned to a user

SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
SELECT NULL grantee, username, granted_role
FROM dba_users
WHERE username LIKE UPPER('%&uname%')
UNION
SELECT grantee, granted_role
FROM dba_role_privs
UNION
SELECT grantee, privilege
FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or

SELECT path
FROM (
SELECT grantee,
    sys_connect_by_path(privilege, ':')||':'||grantee path
FROM (
    SELECT grantee, privilege, 0 role
    FROM dba_sys_privs
    UNION ALL
    SELECT grantee, granted_role, 1 role
    FROM dba_role_privs)
CONNECT BY privilege=prior grantee
START WITH role = 0)
WHERE grantee IN (
SELECT username
FROM dba_users
WHERE lock_date IS NULL
AND password != 'EXTERNAL'
AND username != 'SYS')
OR grantee='PUBLIC'
/

Dangerous Demo

Execute Any Procedure

SELECT *
FROM dba_sys_privs
WHERE privilege LIKE '%CREATE ANY PROC%';

conn owb/owb

CREATE OR REPLACE PROCEDURE <any owner>.do_sql(sqlin VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE sqlin;
END;
/

BEGIN
<any user>.do_sql('drop table emp cascade constraints');
END;
/