Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba sys privs
Oracle System Privilege Grants to Roles
SQL>
SQL> SELECT privilege what_granted,
2 admin_option, grantee
3 FROM sys.dba_sys_privs
4 WHERE NOT EXISTS
5 (SELECT "x"
6 FROM sys.dba_users
7 WHERE username = grantee)
8 and rownum < 50
9 ORDER BY 1,2;
WHAT_GRANTED ADM GRANTEE
---------------------------------------- --- ------------------------------
ADMINISTER ANY SQL TUNING SET YES DBA
ALTER ANY INDEXTYPE YES DBA
ALTER ANY LIBRARY YES DBA
ALTER ANY PROCEDURE YES DBA
ALTER ANY RULE SET YES DBA
ALTER ANY SQL PROFILE YES DBA
ALTER DATABASE YES DBA
ALTER PROFILE YES DBA
ALTER ROLLBACK SEGMENT YES DBA
ALTER SESSION YES DBA
BECOME USER NO IMP_FULL_DATABASE
WHAT_GRANTED ADM GRANTEE
---------------------------------------- --- ------------------------------
BECOME USER YES DBA
CHANGE NOTIFICATION YES DBA
CREATE ANY DIRECTORY YES DBA
CREATE ANY JOB YES SCHEDULER_ADMIN
CREATE ANY LIBRARY YES DBA
CREATE ANY MATERIALIZED VIEW NO IMP_FULL_DATABASE
CREATE ANY SYNONYM NO IMP_FULL_DATABASE
CREATE DIMENSION YES DBA
CREATE JOB NO OEM_ADVISOR
CREATE ROLE YES DBA
CREATE RULE YES DBA
WHAT_GRANTED ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE RULE SET YES DBA
CREATE SESSION YES DBA
CREATE SYNONYM NO RECOVERY_CATALOG_OWNER
DEBUG ANY PROCEDURE YES DBA
DELETE ANY TABLE YES DBA
DEQUEUE ANY QUEUE YES DBA
DROP ANY INDEX YES DBA
DROP ANY INDEXTYPE YES DBA
DROP ANY LIBRARY NO IMP_FULL_DATABASE
DROP ANY MATERIALIZED VIEW YES DBA
DROP ANY TRIGGER NO IMP_FULL_DATABASE
WHAT_GRANTED ADM GRANTEE
---------------------------------------- --- ------------------------------
DROP ANY TRIGGER YES DBA
DROP ROLLBACK SEGMENT YES DBA
DROP TABLESPACE YES DBA
EXECUTE ANY EVALUATION CONTEXT YES DBA
EXECUTE ANY LIBRARY YES DBA
EXECUTE ANY PROCEDURE NO IMP_FULL_DATABASE
EXECUTE ANY PROCEDURE YES DBA
EXPORT FULL DATABASE YES DBA
FORCE ANY TRANSACTION YES DBA
INSERT ANY TABLE YES DBA
MANAGE ANY QUEUE NO OEM_MONITOR
WHAT_GRANTED ADM GRANTEE
---------------------------------------- --- ------------------------------
READ ANY FILE GROUP NO EXP_FULL_DATABASE
SELECT ANY SEQUENCE YES DBA
SELECT ANY TABLE YES DBA
UNDER ANY TYPE YES DBA
UPDATE ANY TABLE YES DBA
49 rows selected.
SQL>
Oracle System Privilege Grants to Users
SQL>
SQL> SELECT b.privilege what_granted,
2 b.admin_option, a.username
3 FROM sys.dba_users a, sys.dba_sys_privs b
4 WHERE a.username = b.grantee
5 and rownum< 50
6 ORDER BY 1,2;
WHAT_GRANTED ADM USERNAME
---------------------------------------- --- ------------------------------
ALTER ANY EVALUATION CONTEXT YES SYS
ALTER ANY RULE SET YES SYS
ALTER DATABASE NO FLOWS_020100
ALTER SESSION NO XDB
ALTER SESSION NO FLOWS_020100
ALTER SESSION NO HR
ALTER SYSTEM NO FLOWS_020100
ANALYZE ANY NO SYS
CREATE ANY CONTEXT YES FLOWS_020100
CREATE ANY DIRECTORY NO FLOWS_020100
CREATE ANY EVALUATION CONTEXT YES SYS
WHAT_GRANTED ADM USERNAME
---------------------------------------- --- ------------------------------
CREATE ANY RULE SET YES SYS
CREATE ANY SYNONYM NO FLOWS_FILES
CREATE CLUSTER YES FLOWS_020100
CREATE DATABASE LINK NO HR
CREATE DIMENSION YES FLOWS_020100
CREATE INDEXTYPE YES FLOWS_020100
CREATE LIBRARY NO MDSYS
CREATE MATERIALIZED VIEW NO SYSTEM
CREATE MATERIALIZED VIEW YES FLOWS_020100
CREATE PUBLIC SYNONYM NO MDSYS
CREATE ROLE NO FLOWS_020100
WHAT_GRANTED ADM USERNAME
---------------------------------------- --- ------------------------------
CREATE RULE YES SYS
CREATE RULE SET YES SYS
CREATE SEQUENCE NO HR
CREATE SESSION NO MDSYS
CREATE SESSION NO XDB
CREATE SESSION NO DIP
CREATE SESSION NO sqle
CREATE SESSION NO OUTLN
CREATE SYNONYM NO HR
CREATE TABLE NO MDSYS
CREATE TABLE YES FLOWS_020100
WHAT_GRANTED ADM USERNAME
---------------------------------------- --- ------------------------------
DEQUEUE ANY QUEUE YES SYS
DROP ANY RULE SET YES SYS
ENQUEUE ANY QUEUE YES SYS
EXECUTE ANY PROCEDURE NO FLOWS_020100
EXECUTE ANY RULE SET YES SYS
EXECUTE ANY TYPE NO SYS
LOCK ANY TABLE NO SYS
MANAGE ANY QUEUE YES SYS
SELECT ANY TABLE YES SYS
UNLIMITED TABLESPACE NO DBSNMP
UNLIMITED TABLESPACE NO XDB
WHAT_GRANTED ADM USERNAME
---------------------------------------- --- ------------------------------
UNLIMITED TABLESPACE NO FLOWS_FILES
UNLIMITED TABLESPACE NO PLSQL
UNLIMITED TABLESPACE NO OUTLN
UNLIMITED TABLESPACE NO MDSYS
UPDATE ANY TABLE NO SYS
49 rows selected.
SQL>
SQL>
User System Privileges
SQL>
SQL> SET FEEDBACK OFF
SQL> SET PAGESIZE 20
SQL> SET LINESIZE 77
SQL> SET HEADING ON
SQL>
SQL> COLUMN username FORMAT A12 HEADING "User"
SQL> COLUMN privilege FORMAT A20 HEADING "Privilege"
SQL> TTITLE LEFT "User System Privileges" RIGHT "Page " FORMAT 999 SQL.PNO SKIP 2
SQL>
SQL> SELECT username, privilege
2 FROM dba_users, dba_sys_privs
3 WHERE username = grantee
4 ORDER BY username, privilege;
User System Privileges Page 1
User Privilege
------------ --------------------
ANONYMOUS CREATE SESSION
CTXSYS ALTER SESSION
CTXSYS CREATE PUBLIC SYNONY
M
CTXSYS CREATE SESSION
CTXSYS CREATE SYNONYM
CTXSYS CREATE VIEW
CTXSYS DROP PUBLIC SYNONYM
CTXSYS UNLIMITED TABLESPACE
DBSNMP CREATE PROCEDURE
DBSNMP CREATE TABLE
DBSNMP SELECT ANY DICTIONAR
Y
User System Privileges Page 2
User Privilege
------------ --------------------
DBSNMP UNLIMITED TABLESPACE
DEFINER UNLIMITED TABLESPACE
DIP CREATE SESSION
FLOWS_020100 ALTER DATABASE
FLOWS_020100 ALTER SESSION
FLOWS_020100 ALTER SYSTEM
FLOWS_020100 ALTER USER
FLOWS_020100 CREATE ANY CONTEXT
FLOWS_020100 CREATE ANY DIRECTORY
FLOWS_020100 CREATE CLUSTER
FLOWS_020100 CREATE DATABASE LINK
FLOWS_020100 CREATE DIMENSION
FLOWS_020100 CREATE INDEXTYPE
FLOWS_020100 CREATE JOB
FLOWS_020100 CREATE LIBRARY
User System Privileges Page 3
User Privilege
------------ --------------------
FLOWS_020100 CREATE MATERIALIZED
VIEW
FLOWS_020100 CREATE OPERATOR
FLOWS_020100 CREATE PROCEDURE
FLOWS_020100 CREATE PUBLIC SYNONY
M
FLOWS_020100 CREATE ROLE
FLOWS_020100 CREATE SEQUENCE
FLOWS_020100 CREATE SYNONYM
FLOWS_020100 CREATE TABLE
FLOWS_020100 CREATE TABLESPACE
FLOWS_020100 CREATE TRIGGER
FLOWS_020100 CREATE TYPE
SQL>
SQL> SET FEEDBACK ON