System Privilages

Oracle System Privileges

General Information

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

 Data Dictionary Objects Related To System Privileges

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 Administrator
• 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
• Drop Any SQL Profile

Alter Any Privileges

• Alter Any Cluster
• Alter Any Cube
• Alter Any Cube Dimension
• Alter Any Dimension
• 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 Profile
• Alter Resource Cost
• Alter Rollback Segment
• Alter Session
• Alter System
• Alter Tablespace
• Alter User

Analyze Privileges

• Analyze Any
• Analyze Any Dictionary

Audit Privileges

• Audit Any
• Audit System

Backup Privileges

• Backup Any Table

Change Privilege

• Change Notification

Clusters

• Alter Any Cluster
• Create Cluster
• Create Any 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 Cluster
• Create Any Context
• Create Any Cube
• Create Any Cube Build Process
• Create Any Cube Dimension
• Create Any Dimension
• Create Any Directory
• 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
• Create Any Synonym
• Create Any Table
• Create Any Trigger
• Create Any Type
• Create Any View

Create Privileges

• 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

• 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 Cluster
• Drop Any Context
• Drop Any Cube
• Drop Any Cube Build Process
• Drop Any Cube Dimension
• Drop Any Dimension
• Drop Any Directory
• 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

Evaluation Context

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

Execute Any Privileges

• 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

Export & Import

• Export Full Database
• Import Full Database

Fine Grained Access Control

• Exempt Access Policy

File Group

• Manage Any File Group
• Manage File Group
• Read Any File Group

Flashback

• Flashback Any Table
• Flashback Archive Administrator

Force

• Force Any Transaction
• Force Transaction

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

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

OLAP Cubes

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

OLAP Cube Build

• 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

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

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

Sys Privileges

• SYSDBA
• SYSOPER

Tablespace

• Alter Tablespace
• Create Tablespace
• Drop Tablespace
• Manage Tablespace
• Unlimited Tablespace

Table

• 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
• Update Any Table

Transaction

• Force Any Transaction
• Force Transaction

Trigger

• 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 PrivilegesGrant A PrivilegeGRANT <privilege_name> TO <schema_name>;GRANT create table TO uwclass; Revoking System PrivilegesRevoke A Single PrivilegeREVOKE <privilege_name> FROM <schema_name>;REVOKE create table FROM uwclass; Determine User Privs
This query will list the system privileges assigned to a userSELECT 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 ProcedureSELECT *
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;
/

**********************************************************