set term ON
set linesize 1000
set pagesize 3500
set verify     off
set feedback   off

alter session set nls_numeric_characters = ',.';

CLEAR BUFFER
CLEAR SQL

SPOOL &&ruta_fichero

set term OFF

@@header_dbinfo 107 DC

PROMPT
PROMPT
PROMPT Lista de roles
PROMPT ---------------

CLEAR COLUMNS
COLUMN ROLE                     FORMAT A30 HEADING "ROLE"
COLUMN PASSWORD_REQUIRED        FORMAT A15 HEADING "PASS_REQUIRED"

SELECT ROLE, PASSWORD_REQUIRED
FROM DBA_ROLES
WHERE ROLE NOT IN
('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE',
'IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS','LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','SCHEDULER_ADMIN','HS_ADMIN_ROLE','OEM_ADVISOR','OEM_MONITOR',
'WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT','JAVA_ADMIN',
'JAVA_DEPLOY','XDBADMIN','AUTHENTICATEDUSER','XDBWEBSERVICES','CTXAPP','OLAP_DBA','OLAP_USER','WKUSER','LBAC_DBA')
ORDER BY ROLE;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de roles concedidos a roles
PROMPT ----------------------------------

CLEAR COLUMNS
COLUMN GRANTEE      FORMAT A30 HEADING "ROLE"
COLUMN GRANTED_ROLE FORMAT A30 HEADING "GRANTED_ROLE"
COLUMN ADMIN_OPTION FORMAT A7  HEADING "ADM_OPT"
COLUMN DEFAULT_ROLE FORMAT A7  HEADING "DEFAULT_ROLE"

SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT ROLE FROM DBA_ROLES
WHERE ROLE NOT IN
('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE',
'IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS','LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','SCHEDULER_ADMIN','HS_ADMIN_ROLE','OEM_ADVISOR','OEM_MONITOR',
'WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT','JAVA_ADMIN',
'JAVA_DEPLOY','XDBADMIN','AUTHENTICATEDUSER','XDBWEBSERVICES','CTXAPP','OLAP_DBA','OLAP_USER','WKUSER','LBAC_DBA')
)
ORDER BY GRANTEE, GRANTED_ROLE;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de privilegios de sistema concedidos a roles
PROMPT ---------------------------------------------------

CLEAR COLUMNS
COLUMN GRANTEE      FORMAT A30 HEADING "ROLE"
COLUMN PRIVILEGE    FORMAT A40 HEADING "PRIVILEGE"
COLUMN ADMIN_OPTION FORMAT A7  HEADING "ADM_OPT"

SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT ROLE FROM DBA_ROLES WHERE ROLE NOT IN 
('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE',
'IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS','LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','SCHEDULER_ADMIN','HS_ADMIN_ROLE','OEM_ADVISOR','OEM_MONITOR',
'WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT','JAVA_ADMIN',
'JAVA_DEPLOY','XDBADMIN','AUTHENTICATEDUSER','XDBWEBSERVICES','CTXAPP','OLAP_DBA','OLAP_USER','WKUSER','LBAC_DBA')
)
ORDER BY GRANTEE, PRIVILEGE;

------------------------------------------------------------------------------------------

PROMPT
PROMPT
PROMPT Lista de privilegios de objeto concedidos a roles
PROMPT --------------------------------------------------

CLEAR COLUMNS
COLUMN ROLE       FORMAT A30 HEADING "ROLE"
COLUMN OWNER      FORMAT A15 HEADING "OWNER"
COLUMN TABLE_NAME FORMAT A30 HEADING "TABLE_NAME"
COLUMN GRANTOR    FORMAT A15 HEADING "GRANTOR"
COLUMN PRIVILEGE  FORMAT A40 HEADING "PRIVILEGE"
COLUMN GRANTABLE  FORMAT A7  HEADING "ADM_OPT"

SELECT ROLE, OWNER, TABLE_NAME, GRANTABLE, PRIVILEGE
FROM ROLE_TAB_PRIVS
WHERE ROLE NOT IN 
('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE',
'IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS','LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','SCHEDULER_ADMIN','HS_ADMIN_ROLE','OEM_ADVISOR','OEM_MONITOR',
'WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT','JAVA_ADMIN',
'JAVA_DEPLOY','XDBADMIN','AUTHENTICATEDUSER','XDBWEBSERVICES','CTXAPP','OLAP_DBA','OLAP_USER','WKUSER','LBAC_DBA')
ORDER BY ROLE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE;




CLEAR COLUMNS
SPOOL OFF
UNDEFINE ruta_fichero