ttitle off
— ———————————————————————————————-
—
— Script: sec_detail.sql
— Auther: Navneet Upneja
— Version: V1.0
—
—
#
set pages 200
set lines 200
col username format a30
COL ACCOUNT_STATUS FORMAT A16 HEADING ‘STATUS’
col user_access format a50
break on username
SELECT USERNAME, account_status,
listagg(b.granted_role,’ ,’) within group(order by username) user_Access
FROM DBA_USERS ,dba_role_privs b
where username not in (‘SYS’,’SYSTEM’,’OUTLN’,
‘DIP’,
‘DBSNMP’,
‘APPQOSSYS’,
‘WMSYS’,
‘EXFSYS’,
‘CTXSYS’,
‘XDB’,
‘ANONYMOUS’,
‘ORDSYS’,
‘ORDDATA’,
‘ORDPLUGINS’,
‘SI_INFORMTN_SCHEMA’,
‘MDSYS’,
‘OLAPSYS’,
‘MDDATA’,
‘SPATIAL_WFS_ADMIN_USR’,
‘SPATIAL_CSW_ADMIN_USR’,
‘FLOWS_FILES’,
‘APEX_PUBLIC_USER’,
‘APEX_030200′
,’XS$NULL’
,’APEX_040200′)
and username=b.grantee (+)
group by username,account_status
order by username, account_status desc
/
undefine user_name
clear columns