Listing All System Privilege Grants
set linesize 150
column grantee format a40
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
ORDER BY grantee;
SYSTEM CREATE MATERIALIZED VIEW NO
SYSTEM CREATE TABLE NO
SYSTEM GLOBAL QUERY REWRITE NO
SYSTEM SELECT ANY TABLE NO
SYSTEM UNLIMITED TABLESPACE YES
VATTOOL_OWNER CREATE ANY SEQUENCE NO
VATTOOL_OWNER CREATE ANY TABLE NO
VATTOOL_OWNER CREATE ANY VIEW NO
VATTOOL_OWNER DELETE ANY TABLE NO
VATTOOL_OWNER EXECUTE ANY PROCEDURE NO
VATTOOL_OWNER INSERT ANY TABLE NO
VATTOOL_OWNER SELECT ANY TABLE NO
VATTOOL_OWNER UNLIMITED TABLESPACE NO
VATTOOL_OWNER UPDATE ANY TABLE NO
VATTOOL_USER CREATE SYNONYM NO
VATTOOL_USER DELETE ANY TABLE NO
VATTOOL_USER EXECUTE ANY PROCEDURE NO
VATTOOL_USER INSERT ANY TABLE NO
VATTOOL_USER SELECT ANY TABLE NO
VATTOOL_USER UNLIMITED TABLESPACE NO
VATTOOL_USER UPDATE ANY TABLE NO
620 rows selected.
*****************************************************************************************************************
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'VATTOOL_USER'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
VATTOOL_USER CREATE SYNONYM NO
VATTOOL_USER DELETE ANY TABLE NO
VATTOOL_USER EXECUTE ANY PROCEDURE NO
VATTOOL_USER INSERT ANY TABLE NO
VATTOOL_USER SELECT ANY TABLE NO
VATTOOL_USER UNLIMITED TABLESPACE NO
VATTOOL_USER UPDATE ANY TABLE NO
7 rows selected.
SQL> set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'SYSTEM'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
SYSTEM CREATE MATERIALIZED VIEW NO
SYSTEM CREATE TABLE NO
SYSTEM GLOBAL QUERY REWRITE NO
SYSTEM SELECT ANY TABLE NO
SYSTEM UNLIMITED TABLESPACE YES
SQL> set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'SYSMAN'
ORDER BY grantee, privilege;
no rows selected
SQL> set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'DBSNMP'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
DBSNMP CREATE PROCEDURE NO
DBSNMP CREATE TABLE NO
DBSNMP SELECT ANY DICTIONARY NO
DBSNMP UNLIMITED TABLESPACE NO
SQL> set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'ADDM'
ORDER BY grantee, privilege;
no rows selected
SQL> set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'GTS_OWNER_ROLE'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
GTS_OWNER_ROLE CREATE CLUSTER NO
GTS_OWNER_ROLE CREATE INDEXTYPE NO
GTS_OWNER_ROLE CREATE JOB NO
GTS_OWNER_ROLE CREATE MATERIALIZED VIEW NO
GTS_OWNER_ROLE CREATE PROCEDURE NO
GTS_OWNER_ROLE CREATE PUBLIC SYNONYM NO
GTS_OWNER_ROLE CREATE SEQUENCE NO
GTS_OWNER_ROLE CREATE SESSION NO
GTS_OWNER_ROLE CREATE SYNONYM NO
GTS_OWNER_ROLE CREATE TABLE NO
GTS_OWNER_ROLE CREATE TRIGGER NO
GTS_OWNER_ROLE CREATE TYPE NO
GTS_OWNER_ROLE CREATE VIEW NO
13 rows selected.
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'DB_SUPPORT_READONLY'
ORDER BY grantee, privilege;
no rows selected
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'DBA'
ORDER BY grantee, privilege;
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'HIDS_AUDITOR'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
HIDS_AUDITOR CREATE SESSION NO
HIDS_AUDITOR RESTRICTED SESSION NO
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'DB_MONITOR'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
DB_MONITOR CREATE SESSION NO
DB_MONITOR RESTRICTED SESSION NO
DB_MONITOR SELECT ANY DICTIONARY NO
DB_MONITOR SELECT ANY TABLE NO
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'OUTLN'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
OUTLN CREATE SESSION NO
OUTLN EXECUTE ANY PROCEDURE NO
OUTLN UNLIMITED TABLESPACE NO
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'DB_TOOLS'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
DB_TOOLS CREATE SESSION NO
DB_TOOLS RESTRICTED SESSION NO
DB_TOOLS SELECT ANY DICTIONARY NO
DB_TOOLS SELECT ANY TABLE NO
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'ESMDBA'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
---------------------------------------- ----------------------------------- ---
ESMDBA CREATE SESSION NO
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'SUPPREADONLY'
ORDER BY grantee, privilege;
no rows selected
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'DB_SUPPORT_READONLY'
ORDER BY grantee, privilege;
no rows selected
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'ADDM_READONLY'
ORDER BY grantee, privilege;
no rows selected
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'ADDM'
ORDER BY grantee, privilege;
no rows selected
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'GTS_USER'
ORDER BY grantee, privilege;
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'GTS_APPLICATION'
ORDER BY grantee, privilege;
no rows selected
USER_SYS_PRIVS
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM ROLE_SYS_PRIVS
WHERE grantee = 'DB_SUPPORT_READONLY'
ORDER BY grantee, privilege;
SELECT * FROM ROLE_SYS_PRIVS
WHERE ROLE = 'DB_SUPPORT_READONLY' ;
no rows selected
DB_MONITOR
ESMDBA
HIDS_AUDITOR
RESOURCE
DBA
AQ_ADMINISTRATOR_ROLE
DB_TOOLS
VATTOOL_OWNER
SYS
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'SYS'
ORDER BY grantee, privilege;
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'GTS_USER'
ORDER BY grantee, privilege;
set linesize 150
column grantee format a40
column grivilege format a35
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'ADDM'
ORDER BY grantee, privilege;
USERNAME PROFILE ACCOUNT_STATUS
------------------------- ------------------------------ ------------------
ADDM ADDM_PROFILE OPEN
DBA_MON DB_ADMINISTRATION_PROFILE OPEN
DBSNMP DB_ADMINISTRATION_PROFILE OPEN
SYS DB_ADMINISTRATION_PROFILE OPEN
SYSTEM DB_ADMINISTRATION_PROFILE OPEN
GTS_USER DB_APPLICATION_USER_PROFILE OPEN
LONGTIA DB_APPLICATION_USER_PROFILE OPEN
SUPPREADONLY DB_APPLICATION_USER_PROFILE OPEN
VATTOOL_USER DB_APPLICATION_USER_PROFILE OPEN
WSTEVEN DB_APPLICATION_USER_PROFILE OPEN
ZHANRUI DB_APPLICATION_USER_PROFILE OPEN
OPS$ORACLE DB_EXTERNAL_PROFILE OPEN
GTS_OWNER DB_SCHEMA_OWNER_PROFILE LOCKED
DB_TOOLS DB_STANDARD_90 OPEN
APPQOSSYS DEFAULT EXPIRED & LOCKED
AUDTASK DEFAULT LOCKED
AUD_OWNER DEFAULT LOCKED
DIP DEFAULT EXPIRED & LOCKED
ORACLE_OCM DEFAULT EXPIRED & LOCKED
OUTLN DEFAULT EXPIRED & LOCKED
VATTOOL_OWNER DEFAULT LOCKED
ESMDBA ESMDBA_PROFILE OPEN
HIDS_AUDITOR HIDS_PROFILE OPEN
23 rows selected.
The following query lists all the object privileges granted to the security_admin role:
SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
WHERE ROLE = 'SECURITY_ADMIN';