DBSecWorx: System Privileges

Oracle System Privileges Versions All

Security Advisory

System privileges are privileges that do not relate to a specific schema object but, instead, relate to a class of objects, for example SELECT ANY TABLE, or an action a user or application can engage in while connected to the database, for example CREATE TABLE.

System Privileges can be granted to USERS, ROLES, and/or PUBLIC. The vast majority of system privileges need never be granted to any user including a DBA and the fact that they are granted, by default, by Oracle Corp. is a prima facia example of Oracle violating the "Least Privileges" principal in favor of backward compatibility.

Many System Privileges are essentially harmless such as COMMENT ANY TABLE but others such as GRANT ANY PRIVILEGE should be treated like a box of matches: They can be used to create a warm and comfortable environment or to burn down the neighborhood.

Security "best practices" dictate that you should NEVER grant a privilege to any user or schema, for any reason, that contains the word "ANY". The possibility of someone requesting the privilege is orders of magnitude higher than the possibility that there is a legitimate justification to grant it. We recommend never granting any system privilege to PUBLIC.

Recommended Security Rules

NEVER

grant any system privilege to PUBLIC
grant any system privilege containing the word "ANY" to any user, schema, role, or to PUBLIC
grant the administer option to any user, schema, role, or to PUBLIC (ADMIN_OPTION should always be "NO")
grant any privilege by inheritance (INHERITED should always be "NO")
grant any privilege containing the word EXEMPT

WITH GREAT CARE AFTER READING THE DOCS and FULLY APPRECIATING THE RISKS

grant a privilege that contains the word ADMINISTER
grant a privilege that contains the word MANAGE
grant a privilege that contains the word ALTER
grant a privilege that contains the word DEBUG
grant a privilege that contains the word DROP
grant a privilege that contains the word GRANT
grant a privilege that contains the word PUBLIC
grant a privilege that contains the word REVOKE
grant a privilege containing the word TRANSLATE or TRANSLATION
grant AUDIT SYSTEM
grant CREATE DATABASE LINK
grant LOGMINING
grant SET CONTAINER
grant CREATE USER or DROP USER

CAUTIONS

Never grant a SELECT privilege until you have tested the corresponding READ and determined that it will not work

Data Dictionary Objects

ALL_SYS_PRIVS	DBA_SYS_PRIVS	SYSTEM_PRIVILEGE_MAP
CDB_SYS_PRIVS	SESSION_PRIVS	USER_SYS_PRIVS

System Privilege Vulnerability Screening for Users and Schemas

The code at right should be run daily or weekly to identify existing or newly created vulnerabilities. Each example contains an explanation of the nature of the risk.

This code must be executed from a schema with escalated privileges so we recommend doing so with a DBMS_SCHEDULER job, never from cron. col grantee format a30 col admin_option format a12 col inherited format a9 col any format a3 For a legacy architecture database SELECT dsp.grantee, dsp.privilege, DECODE(dsp.admin_option, 'NO', '-') AS ADMIN_OPTION, DECODE(dsp.inherited, 'NO', '-') AS INHERITED, DECODE(INSTR(dsp.privilege, ' ANY ', 1, 1),0,'-', 'YES') AS "ANY" FROM dba_sys_privs dsp, dba_users du WHERE dsp.grantee = du.username AND dsp.grantee NOT IN ('ANONYMOUS', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', 'GGSYS', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'LBACSYS', 'MDDATA', 'MDSYS', 'OJVMSYS', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'REMOTE_SCHEDULER_AGENT', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYS$UMF', 'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'SYSTEM', 'WMSYS', 'XDB', 'XS$NULL') ORDER BY 1,2; For a container database SELECT csp.grantee, csp.privilege, DECODE(csp.admin_option, 'NO', '-') AS ADMIN_OPTION, DECODE(csp.inherited, 'NO', '-') AS INHERITED, DECODE(INSTR(csp.privilege, ' ANY ', 1, 1),0,'-', 'YES') AS "ANY", csp.con_id FROM cdb_sys_privs csp, cdb_users cu WHERE csp.grantee = cu.username AND csp.con_id = cu.con_id AND csp.grantee NOT IN ('ANONYMOUS', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', 'GGSYS', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'LBACSYS', 'MDDATA', 'MDSYS', 'OJVMSYS', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'REMOTE_SCHEDULER_AGENT', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYS$UMF', 'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'SYSTEM', 'WMSYS', 'XDB', 'XS$NULL') ORDER BY 6,1,2; Review every row in the listings created with the above statement and compare it with the Recommended Security Rules listed above. The "Best Practice" violations are highlighted in red. The following is an example from a version 19.3 container database SQL> SELECT csp.grantee, csp.privilege, 2 DECODE(csp.admin_option, 'NO', '-') AS ADMIN_OPTION, 3 DECODE(csp.inherited, 'NO', '-') AS INHERITED, 4 DECODE(INSTR(csp.privilege, ' ANY ', 1, 1),0,'-', 'YES') AS "ANY", csp.con_id 5 FROM cdb_sys_privs csp, cdb_users cu 6 WHERE csp.grantee = cu.username 7 AND csp.con_id = cu.con_id 8 AND csp.grantee NOT IN ('ANONYMOUS', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', 'GGSYS', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'LBACSYS', 'MDDATA', 'MDSYS', 'OJVMSYS', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'REMOTE_SCHEDULER_AGENT', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYS$UMF', 'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'SYSTEM', 'WMSYS', 'XDB', 'XS$NULL') 9 ORDER BY 6,1,2; GRANTEE PRIVILEGE ADMIN_OPTION INHERITED ANY CON_ID ------- ------------------------- ------------ --------- --- ------ C##DBSW CREATE PROCEDURE - YES - 1 C##DBSW CREATE SESSION - YES - 1 C##DBSW CREATE TABLE - YES - 1 C##DBSW SELECT ANY DICTIONARY - YES YES 1 GG CREATE SESSION - - - 3 GG CREATE TABLE - - - 3 HR ALTER SESSION - - - 3 HR CREATE DATABASE LINK - - - 3 HR CREATE SEQUENCE - - - 3 HR CREATE SESSION - - - 3 HR CREATE SYNONYM - - - 3 HR CREATE VIEW - - - 3 OE CREATE SESSION - - - 3 OE CREATE TABLE - - - 3 OE CREATE TRIGGER - - - 3 OE CREATE TYPE - - - 3 OE CREATE VIEW - - - 3 OE UNLIMITED TABLESPACE - - - 3 PM CREATE SESSION - - - 3 PM CREATE TABLE - - - 3 PM CREATE VIEW - - - 3 SCOTT CREATE SESSION - - - 3 SCOTT CREATE TABLE - - - 3 SH CREATE MATERIALIZED VIEW - - - 3 SH CREATE SESSION - - - 3 SH CREATE TABLE - - - 3 SH CREATE VIEW - - - 3 SH UNLIMITED TABLESPACE - - - 3 UWCLASS CREATE CLUSTER - - - 3 UWCLASS CREATE DATABASE LINK - - - 3 UWCLASS CREATE OPERATOR - - - 3 UWCLASS CREATE PROCEDURE - - - 3 UWCLASS CREATE ROLE - - - 3 UWCLASS CREATE SEQUENCE - - - 3 UWCLASS CREATE SESSION - - - 3 UWCLASS CREATE SYNONYM - - - 3 UWCLASS CREATE TABLE - - - 3 UWCLASS CREATE TRIGGER - - - 3 UWCLASS CREATE TYPE YES - - 3 UWCLASS CREATE VIEW - - - 3 UWCLASS SELECT ANY DICTIONARY - - YES 3 41 rows selected.

System Privileges By Category

In some sense granting any system privilege, even the most basic which is CREATE SESSION, carries with it some risk. Be sure to carefully review the Recommended Security Rules section above and modify your internal processes and procedures such that no privilege grant can take place without the internal equivalent of what in the wider world is called an "Environmental Assessment" where the potential risks are carefully and accurately assessed.

List all System Privileges SELECT * FROM system_privilege_map ORDER BY 1;

Administer

Administer Any SQL Tuning Set
Administer Database Trigger (database level trigger)
Administer Key Management
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 Any File Group
Manage Any Queue
Manage File Group
Manage Scheduler
Manage Tablespace

Advanced Queuing

Dequeue Any Queue
Enqueue Any Queue
Manage Any Queue

Advisor Framework

Administer Any SQL Tuning Set
Administer SQL Management Object
Administer SQL Tuning Set
Advisor
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 Analytic View
Alter Any Assembly
Alter Any Attribute Dimension
Alter Any Cluster
Alter Any Cube
Alter Any Cube Build Process
Alter Any Cube Dimension
Alter Any Dimension
Alter Any Edition
Alter Any Evaluation Context
Alter Any Hierarchy
Alter Any Index
Alter Any Indextype
Alter Any Library
Alter Any Materialized View
Alter Any Measure Folder
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 SQL Translation Profile
Alter Any Table
Alter Any Trigger
Alter Any Type

Alter Privileges

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

Analytic Views

Alter Any Analytic View
Create Any Analytic View
Create Analytic View
Drop Any Analytic View
Read Any Analytic View Cache
Write Any Analytic View Cache

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

Attribute Dimensions

Alter Any Attribute Dimension
Create Any Attribute Dimension
Create Attribute Dimension
Drop Any Attribute Dimension

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

Container Database

Create Pluggable Database
Set Container

Contexts

Create Any Context
Drop Any Context

Create Any Privileges

Create Any Analytic View
Create Any Assembly
Create Any Attribute Dimension
Create Any Cluster
Create Any Context
Create Any Credential
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 Hierarchy
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 SQL Translation Profile
Create Any Synonym
Create Any Table
Create Any Trigger
Create Any Type
Create Any View

Create Privileges

Create Analytic View
Create Assembly
Create Attribute Dimension
Create Cluster
Create Credential
Create Cube
Create Cube Build Process
Create Cube Dimension
Create Database Link
Create Dimension
Create Evaluation Context
Create External Job
Create Hierarchy
Create Indextype
Create Job
Create Library
Create Lockdown Profile
Create Materialized View
Create Measure Folder
Create Mining Model
Create Operator
Create Pluggable Database
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 SQL Translation Profile
Create Synonym
Create Table
Create Tablespace
Create Trigger
Create Type
Create User
Create View

Database

Alter Database
Alter System
Audit System
Create Pluggable Database

Database Links

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

Datastore

Text Datastore Access

Debug

Debug Any Procedure
Debug Connect Any
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 Analytic View
Drop Any Assembly
Drop Any Attribute Dimension
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 Hierarchy
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 SQL Translation Profile
Drop Any Synonym
Drop Any Table
Drop Any Trigger
Drop Any Type
Drop Any View

Drop Privileges

Drop Lockdown Profile
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
Drop Any Evaluation Context
Execute Any Evaluation Context
Create 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
Exempt Reaction 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
Purge DBA_RECYCLEBIN

Force

Force Any Transaction
Force Transaction

Grant

Grant Any Object Privilege
Grant Any Privilege
Grant Any Role

Hierarchies

Alter Any Hierarchy
Create Any Hierarchy
Create Hierarchy
Drop Any Hierarchy

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

Inherit

Inherit Any Privileges
Inherit Any Remote Privileges

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
Use Any Job Resource

Libraries

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

Locks

Lock Any Table

Lockdown Profile

Alter Lockdown Profile
Create Lockdown Profile
Drop Lockdown Profile

Log Mining

Logmining

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

Alter Any Measure Folder
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

Policies

Exempt Access Policy
Exempt Identity Policy
Exempt Reaction Policy

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

Read Any

Read Any Analytic View Cache
Read Any File Group
Read Any Table

Real Application Testing

Keep DATE TIME
Keep SYSGUID

Redaction

Exempt DDL Redaction Policy
Exempt DML Redaction Policy
Exempt Redaction Policy

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 Build Process
Select Any Cube Dimension
Select Any Dictionary
Select Any Measure Folder
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

SYSBACKUP
SYSDBA
SYSDG
SYSKM
SYSOPER
SYSRAC

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
Redefine 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

Alter Any SQL Translation Profile
Create Any SQL Translation Profile
Drop Any SQL Translation Profile
Translate Any SQL
Use 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

Under

Under Any Table
Under Any Type
Under Any View

Update

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

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

Write

Write Any Analytic View Cache

Grant 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;

Revoke 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 <user_name>;

REVOKE create table, create view FROM uwclass;

Related Queries

List all System Privileges SELECT * FROM session_privs ORDER BY 1;

List all System Privileges containing the word "ANY: SELECT * FROM session_privs WHERE privilege LIKE '% ANY %' ORDER BY 1;

This query will list the system privileges assigned to a user SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS" FROM ( SELECT NULL grantee, username AS 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' /