CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle System Privileges
Version 11.1
 
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 Privileges
Grant A Privilege GRANT <privilege_name> TO <schema_name>;
GRANT create table TO uwclass;
 
Revoking System Privileges
Revoke A Single Privilege REVOKE <privilege_name> FROM <schema_name>;
REVOKE create table 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;
/
 
Related Topics
Object Privileges
Roles
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [323 users online]    © 2010 psoug.org