/* ************************************************ */
/*                                                  */
/* format the report:                               */
/*        DBAUSERS - DBA User Report                */
/*                                                  */
/* name:    @c:\oracle\sql\dbausers.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

set wrap         off
set feedback     off
set pagesize     62
set trimout      on
set linesize     78
set newpage      0
set verify       on
set underline    =
set echo         off
set termout      off

whenever sqlerror exit failure
spool dbausers.lst

ttitle left "PAGE:" sql.pno format 999 -
center 'DBAUSERS - DBA User Report ' right CWS_DATE skip 2

column username              format A10
column default_tablespace    format A14 heading "DFLT TBLSPACE"
column connect_priv          format A7 heading "CONNECT"
column resource_priv         format A8 heading "RESOURCE"
column dba_priv              format A3 heading "DBA"
column temporary_tablespace  format A14 heading "TEMP TBLSPACE"
column created               format DATE

select username, 
    default_tablespace, 
    temporary_tablespace,
    '      1' connect_priv,
    '       1' resource_priv,
    '  1' dba_priv, created
from dba_users d
where username = 
         (select grantee 
            from dba_role_privs 
           where grantee = d.username and granted_role = 'DBA') and
      username = 
         (select grantee 
            from dba_role_privs 
           where grantee = d.username and granted_role = 'CONNECT') and
      username = 
         (select grantee from dba_role_privs where grantee = 
                         d.username and granted_role = 'RESOURCE')
union
select username, default_tablespace, temporary_tablespace,
       '      1' connect_priv, '       1' resource_priv,
       '   ' dba_priv, created
       from dba_users d
       where NOT EXISTS (select grantee from dba_role_privs where grantee = 
                         d.username and granted_role = 'DBA') and
             username = (select grantee from dba_role_privs where grantee = 
                         d.username and granted_role = 'CONNECT') and
             username = (select grantee from dba_role_privs where grantee = 
                         d.username and granted_role = 'RESOURCE')
union
select username, default_tablespace, temporary_tablespace,
       '      1' connect_priv, '        ' resource_priv,
       '   ' dba_priv, created
       from dba_users d
       where NOT EXISTS (select grantee from dba_role_privs where grantee = 
                         d.username and granted_role = 'DBA') and
             username = (select grantee from dba_role_privs where grantee = 
                         d.username and granted_role = 'CONNECT') and
             NOT EXISTS  (select grantee from dba_role_privs where grantee = 
                         d.username and granted_role = 'RESOURCE')
order by 1;

spool off
rem exit
