****************************************************************************************************************************************
set linesize 160
column username format a15
column default_tablespace format a20
column temp_tbs format a15
column profile format a35
column granted_role format a30
SELECT U.USERNAME AS USERNAME,
U.PROFILE,
U.ACCOUNT_STATUS,
R.GRANTED_ROLE AS GRANTED_ROLE
FROM DBA_USERS U, DBA_ROLE_PRIVS R
WHERE U.USERNAME = R.GRANTEE (+)
ORDER BY U.PROFILE, U.USERNAME;
USERNAME PROFILE ACCOUNT_STATUS GRANTED_ROLE
--------------- ----------------------------------- ------------------ ------------------------------
ADDM ADDM_PROFILE OPEN ADDM_READONLY
DBA_MON DB_ADMINISTRATION_PROFILE OPEN DB_MONITOR
DBSNMP DB_ADMINISTRATION_PROFILE OPEN OEM_MONITOR
SYS DB_ADMINISTRATION_PROFILE OPEN HS_ADMIN_SELECT_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN DATAPUMP_EXP_FULL_DATABASE
SYS DB_ADMINISTRATION_PROFILE OPEN DBFS_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN SELECT_CATALOG_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN DB_AUDIT_JOBS
SYS DB_ADMINISTRATION_PROFILE OPEN CONNECT
SYS DB_ADMINISTRATION_PROFILE OPEN DB_SUPPORT_READONLY
SYS DB_ADMINISTRATION_PROFILE OPEN EXP_FULL_DATABASE
SYS DB_ADMINISTRATION_PROFILE OPEN DATAPUMP_IMP_FULL_DATABASE
SYS DB_ADMINISTRATION_PROFILE OPEN HS_ADMIN_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN DB_AUDIT_READER
SYS DB_ADMINISTRATION_PROFILE OPEN DB_MONITOR
SYS DB_ADMINISTRATION_PROFILE OPEN GTS_READONLY
SYS DB_ADMINISTRATION_PROFILE OPEN DBA
SYS DB_ADMINISTRATION_PROFILE OPEN AQ_ADMINISTRATOR_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN OEM_MONITOR
SYS DB_ADMINISTRATION_PROFILE OPEN DB_CATALOG_ROLEV11204
SYS DB_ADMINISTRATION_PROFILE OPEN GTS_OWNER_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN RECOVERY_CATALOG_OWNER
SYS DB_ADMINISTRATION_PROFILE OPEN GTS_APPLICATION
SYS DB_ADMINISTRATION_PROFILE OPEN IMP_FULL_DATABASE
SYS DB_ADMINISTRATION_PROFILE OPEN HS_ADMIN_EXECUTE_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN OEM_ADVISOR
SYS DB_ADMINISTRATION_PROFILE OPEN GTS_UPDATE
SYS DB_ADMINISTRATION_PROFILE OPEN RESOURCE
SYS DB_ADMINISTRATION_PROFILE OPEN EXECUTE_CATALOG_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN LOGSTDBY_ADMINISTRATOR
SYS DB_ADMINISTRATION_PROFILE OPEN ADM_PARALLEL_EXECUTE_TASK
SYS DB_ADMINISTRATION_PROFILE OPEN DB_AUDIT_OWNER
SYS DB_ADMINISTRATION_PROFILE OPEN SCHEDULER_ADMIN
SYS DB_ADMINISTRATION_PROFILE OPEN GATHER_SYSTEM_STATISTICS
SYS DB_ADMINISTRATION_PROFILE OPEN AQ_USER_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN DELETE_CATALOG_ROLE
SYS DB_ADMINISTRATION_PROFILE OPEN DB_AUDITOR
SYSTEM DB_ADMINISTRATION_PROFILE OPEN DBA
SYSTEM DB_ADMINISTRATION_PROFILE OPEN AQ_ADMINISTRATOR_ROLE
GTS_USER DB_APPLICATION_USER_PROFILE OPEN GTS_APPLICATION
LONGTIA DB_APPLICATION_USER_PROFILE OPEN DB_SUPPORT_READONLY
SUPPREADONLY DB_APPLICATION_USER_PROFILE OPEN DB_SUPPORT_READONLY
VATTOOL_USER DB_APPLICATION_USER_PROFILE OPEN RESOURCE
VATTOOL_USER DB_APPLICATION_USER_PROFILE OPEN CONNECT
WSTEVEN DB_APPLICATION_USER_PROFILE OPEN DB_SUPPORT_READONLY
ZHANRUI DB_APPLICATION_USER_PROFILE OPEN DB_SUPPORT_READONLY
OPS$ORACLE DB_EXTERNAL_PROFILE OPEN DBA
GTS_OWNER DB_SCHEMA_OWNER_PROFILE LOCKED GTS_OWNER_ROLE
DB_TOOLS DB_STANDARD_90 OPEN
APPQOSSYS DEFAULT EXPIRED & LOCKED
AUDTASK DEFAULT LOCKED DB_AUDIT_JOBS
AUD_OWNER DEFAULT LOCKED DB_AUDIT_OWNER
DIP DEFAULT EXPIRED & LOCKED
ORACLE_OCM DEFAULT EXPIRED & LOCKED
OUTLN DEFAULT EXPIRED & LOCKED RESOURCE
VATTOOL_OWNER DEFAULT LOCKED CONNECT
ESMDBA ESMDBA_PROFILE OPEN SELECT_CATALOG_ROLE
HIDS_AUDITOR HIDS_PROFILE OPEN
58 rows selected.
***************************************************************************************************************************************
Check the Role - DB_SUPPORT_READONLY
SQL> set linesize 150
column role format a20
column granted_role format a20
column admin_option format a15
SELECT ROLE, GRANTED_ROLE, ADMIN_OPTION
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'DB_SUPPORT_READONLY';
ROLE GRANTED_ROLE ADMIN_OPTION
-------------------- -------------------- ---------------
DB_SUPPORT_READONLY GTS_READONLY NO
SQL> set linesize 150
column role format a20
column granted_role format a20
column admin_option format a15
SELECT ROLE, GRANTED_ROLE, ADMIN_OPTION
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'GTS_READONLY';
ROLE GRANTED_ROLE ADMIN_OPTION
-------------------- -------------------- ---------------
GTS_READONLY DB_CONNECT NO
set linesize 150
column role format a20
column granted_role format a20
column admin_option format a15
SELECT ROLE, GRANTED_ROLE, ADMIN_OPTION
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'DB_CONNECT';
no rows selected
set linesize 150
column role format a20
column privilege format a25
column admin_option format a13
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 role format a20
column privilege format a25
column admin_option format a13
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'GTS_READONLY'
ORDER BY grantee, privilege;
no rows selected
set linesize 150
column role format a20
column privilege format a25
column admin_option format a13
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'DB_CONNECT'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADMIN_OPTION
---------------------------------------- ---------------------------------------- ---------------
DB_CONNECT CREATE SESSION NO
DB_CONNECT CREATE SYNONYM NO
set linesize 150
set pagesize 1000
column role format a20
column owner format a10
column privilege format a15
column table_name format a35
column grantable format a10
SELECT ROLE,
OWNER,
TABLE_NAME,
PRIVILEGE ,
GRANTABLE
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'DB_SUPPORT_READONLY'
ORDER BY ROLE, OWNER, TABLE_NAME;
ROLE OWNER TABLE_NAME PRIVILEGE GRANTABLE
-------------------- ---------- ----------------------------------- --------------- ----------
DB_SUPPORT_READONLY SYS V_$LOCKED_OBJECT SELECT NO
DB_SUPPORT_READONLY SYS V_$SESSION SELECT NO
DB_SUPPORT_READONLY SYS V_$SQL SELECT NO
set linesize 150
set pagesize 1000
column role format a20
column owner format a10
column privilege format a15
column table_name format a35
column grantable format a10
SELECT ROLE,
OWNER,
TABLE_NAME,
PRIVILEGE ,
GRANTABLE
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'GTS_READONLY'
ORDER BY ROLE, OWNER, TABLE_NAME;
ROLE OWNER TABLE_NAME PRIVILEGE GRANTABLE
-------------------- ---------- ----------------------------------- --------------- ----------
GTS_READONLY GTS_OWNER SEQ_IMPORT_REPORT SELECT NO
GTS_READONLY GTS_OWNER SEQ_INVOICE_KEY_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_JOB_CONFIG SELECT NO
GTS_READONLY GTS_OWNER SEQ_JOB_LOG SELECT NO
GTS_READONLY GTS_OWNER SEQ_READ_REPORT SELECT NO
GTS_READONLY GTS_OWNER SEQ_TABLEPSW_KEY_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_TABLE_KEY_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_TBL_DATAPATH_CONFIG_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_TBL_EDITLOG_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_TBL_HOLIDAY_KEY_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_TBL_REDITLOG_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_TBL_SFTP_CONFIG_ID SELECT NO
GTS_READONLY GTS_OWNER SEQ_VAT_DATA SELECT NO
GTS_READONLY GTS_OWNER TBL_BRANCH SELECT NO
GTS_READONLY GTS_OWNER TBL_BUSINESS_RULE SELECT NO
GTS_READONLY GTS_OWNER TBL_BUSINESS_RULE_EDITLOG SELECT NO
GTS_READONLY GTS_OWNER TBL_DATAPATH_CONFIG SELECT NO
GTS_READONLY GTS_OWNER TBL_DEPARTMENT SELECT NO
GTS_READONLY GTS_OWNER TBL_FILE_CONFIG SELECT NO
GTS_READONLY GTS_OWNER TBL_GOODS_NO SELECT NO
GTS_READONLY GTS_OWNER TBL_HOLIDAY SELECT NO
GTS_READONLY GTS_OWNER TBL_IMPORT_REPORT SELECT NO
GTS_READONLY GTS_OWNER TBL_INVOICE SELECT NO
GTS_READONLY GTS_OWNER TBL_INVOICEITEM SELECT NO
GTS_READONLY GTS_OWNER TBL_JOB_CONFIG SELECT NO
GTS_READONLY GTS_OWNER TBL_JOB_LOG SELECT NO
GTS_READONLY GTS_OWNER TBL_MAIL_CONFIG SELECT NO
GTS_READONLY GTS_OWNER TBL_MENU SELECT NO
GTS_READONLY GTS_OWNER TBL_PROPERTY SELECT NO
GTS_READONLY GTS_OWNER TBL_READ_REPORT SELECT NO
GTS_READONLY GTS_OWNER TBL_ROLE SELECT NO
GTS_READONLY GTS_OWNER TBL_ROLE_EDITLOG SELECT NO
GTS_READONLY GTS_OWNER TBL_ROLE_MENU SELECT NO
GTS_READONLY GTS_OWNER TBL_SFTP_CONFIG SELECT NO
GTS_READONLY GTS_OWNER TBL_TASK_CONFIG SELECT NO
GTS_READONLY GTS_OWNER TBL_USER SELECT NO
GTS_READONLY GTS_OWNER TBL_USER_EDITLOG SELECT NO
GTS_READONLY GTS_OWNER TBL_USER_PSW SELECT NO
GTS_READONLY GTS_OWNER TBL_VAT_DATA SELECT NO
GTS_READONLY GTS_OWNER TBL_VAT_DATA_TEMP SELECT NO
GTS_READONLY GTS_OWNER TBL_VAT_DOCUMENT SELECT NO
GTS_READONLY GTS_OWNER TBL_VAT_DOCUMENT_ITEM SELECT NO
42 rows selected.
set linesize 150
set pagesize 1000
column role format a20
column owner format a10
column privilege format a15
column table_name format a35
column grantable format a10
SELECT ROLE,
OWNER,
TABLE_NAME,
PRIVILEGE ,
GRANTABLE
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'DB_CONNECT'
ORDER BY ROLE, OWNER, TABLE_NAME;
no rows selected
***************************************************************************************************************************************
Check the Role - GTS_APPLICATION
SQL> set linesize 150
column role format a20
column granted_role format a20
column admin_option format a15
SELECT ROLE, GRANTED_ROLE, ADMIN_OPTION
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'GTS_APPLICATION';
ROLE GRANTED_ROLE ADMIN_OPTION
-------------------- -------------------- ---------------
GTS_APPLICATION GTS_READONLY NO
GTS_APPLICATION GTS_UPDATE NO
SQL> set linesize 150
column role format a20
column granted_role format a20
column admin_option format a15
SELECT ROLE, GRANTED_ROLE, ADMIN_OPTION
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'GTS_READONLY';
ROLE GRANTED_ROLE ADMIN_OPTION
-------------------- -------------------- ---------------
GTS_READONLY DB_CONNECT NO
SQL> set linesize 150
column role format a20
column granted_role format a20
column admin_option format a15
SELECT ROLE, GRANTED_ROLE, ADMIN_OPTION
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'GTS_UPDATE';
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_APPLICATION'
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_UPDATE'
ORDER BY grantee, privilege;
no rows selected
set linesize 150
set pagesize 1000
column role format a20
column owner format a10
column privilege format a15
column table_name format a35
column grantable format a10
SELECT ROLE,
OWNER,
TABLE_NAME,
PRIVILEGE ,
GRANTABLE
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'GTS_UPDATE'
ORDER BY ROLE, OWNER, TABLE_NAME;
no rows selected
********************************************************************************************************************************
Check the Role - GTS_OWNER_ROLE
set linesize 150
column role format a20
column granted_role format a20
column admin_option format a15
SELECT ROLE, GRANTED_ROLE, ADMIN_OPTION
FROM ROLE_ROLE_PRIVS
WHERE ROLE = 'GTS_OWNER_ROLE';
no rows selected
SQL> set linesize 150
column role format a20
column privilege format a25
column admin_option format a13
SELECT grantee, privilege, admin_option
FROM DBA_SYS_PRIVS
WHERE grantee = 'GTS_OWNER_ROLE'
ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADMIN_OPTION
-------------------- ------------------------- -------------
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
set pagesize 1000
column role format a20
column owner format a10
column privilege format a15
column table_name format a35
column grantable format a10
SELECT ROLE,
OWNER,
TABLE_NAME,
PRIVILEGE ,
GRANTABLE
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'GTS_OWNER_ROLE'
ORDER BY ROLE, OWNER, TABLE_NAME;
no rows selected