Oracle PL/SQL/System Tables Views/dba sys privs
Query dba_sys_privs table
SQL>
SQL> set verify off
SQL>
SQL> column priv format a30 heading "Privilege"
SQL> column wadmin format a6 heading "Admin?"
SQL>
SQL> select privilege priv, initcap(admin_option) wadmin
2 from dba_sys_privs
3 where rownum < 50
4 /
Privilege Admin?
------------------------------ ------
CREATE VIEW No
ALTER SESSION No
CREATE SESSION No
CREATE SYNONYM No
CREATE SEQUENCE No
CREATE DATABASE LINK No
UNLIMITED TABLESPACE No
ADVISOR Yes
AUDIT ANY Yes
DROP USER Yes
RESUMABLE Yes
ALTER USER Yes
CREATE JOB Yes
ANALYZE ANY Yes
BECOME USER Yes
CREATE ROLE Yes
CREATE RULE Yes
CREATE TYPE Yes
CREATE USER Yes
CREATE VIEW Yes
ALTER SYSTEM Yes
AUDIT SYSTEM Yes
CREATE TABLE Yes
DROP PROFILE Yes
ALTER PROFILE Yes
ALTER SESSION Yes
DROP ANY ROLE Yes
DROP ANY RULE Yes
DROP ANY TYPE Yes
DROP ANY VIEW Yes
QUERY REWRITE Yes
ALTER ANY ROLE Yes
ALTER ANY RULE Yes
ALTER ANY TYPE Yes
ALTER DATABASE Yes
CREATE ANY JOB Yes
CREATE CLUSTER Yes
CREATE LIBRARY Yes
CREATE PROFILE Yes
CREATE SESSION Yes
CREATE SYNONYM Yes
CREATE TRIGGER Yes
DROP ANY INDEX Yes
DROP ANY TABLE Yes
GRANT ANY ROLE Yes
LOCK ANY TABLE Yes
MERGE ANY VIEW Yes
UNDER ANY TYPE Yes
UNDER ANY VIEW Yes
49 rows selected.
SQL>
SQL> --
select distinct privilege from dba_sys_privs
VARCHAR2(3)
SQL> select distinct privilege
2 from dba_sys_privs
3 where rownum < 50
4 order by privilege;
PRIVILEGE
----------------------------------------
ADVISOR
ALTER SYSTEM
ALTER USER
ANALYZE ANY
AUDIT ANY
AUDIT SYSTEM
BECOME USER
CREATE JOB
CREATE ROLE
CREATE RULE
CREATE TABLE
PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE USER
CREATE VIEW
DROP USER
RESUMABLE
16 rows selected.
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