/* ************************************************ */
/*                                                  */
/* fnctn:   System Privlages Granted to Query Roles */
/*                                                  */
/* name:    @c:\oracle\sql\gr_roles.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

set pages 100
set lines 85
set termout off
set verify off
set feedback off

col grantee heading 'Role'
col owner heading 'Object|Owner'
col table_name heading 'Object Name'
col privilege heading  'Privilege'
col grantable heading 'Grantable?' format a10
col today noprint new_value cur_date

ttitle center 'Table/Views Granted to Query Roles' - 
skip 1 center '(ordered by Query Role Name)' -
skip 1 center 'Report Date: ' cur_date -
skip 2

spool gr_role1.rslt

select to_char(sysdate,'MM-DD-YYYY') today,
 	substr(a.grantee,1,15) grantee,
	substr(a.owner,1,7) owner,
	a.table_name table_name,
	substr(a.privilege,1,10) privilege,
	a.grantable grantable
from dba_tab_privs a
where a.grantee like 'QUERY%'
order by a.grantee, a.owner,a.table_name;

spool off 

set lines 100
ttitle center 'Query Roles Granted to Employees or Other Query Roles' - skip 1 center '(ordered by Employee Name)' - 
skip 1 center 'Report Date: ' cur_date - 
skip 2

spool gr_role2.rslt

col name heading 'Employee Name' format a30
col grantee heading 'Grantee'
col granted_role heading 'Role' 
col admin_option heading 'Admin|Option?' format a7
column default_role heading 'Default|Role?' format a7

break on name on grantee

select to_char(sysdate,'MM-DD-YYYY') today,
	upper(substr(b.lname,1,1))||lower(substr(b.lname,2,24))||','||
        upper(substr(b.fname,1,1))||lower(substr(b.fname,2,9)) name,
        substr(a.grantee,1,15) grantee,
   	a.granted_role granted_role,
	a.admin_option admin_option,
	a.default_role default_role
    from dba_role_privs a,
	a_fin_es.esd_employees b
	where (a.grantee like 'QUERY%' or a.granted_role like 'QUERY%')
 	and substr(a.grantee,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
	order by name,grantee,granted_role;
spool off
set pages 0

set linesize 160
set termout off
set verify off
set feedback off
set head off
ttitle off

spool gr_role3.rslt

select 'Table/Views Granted to Query Roles (ordered by Query Role Name),' from dual;
select 'Report Date: '||to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')||',' from dual;
select ',' from dual;

select substr(a.grantee,1,15)||','||
	substr(a.owner,1,7)||','||
        a.table_name||','||
	substr(a.privilege,1,10)||','||
	a.grantable||','
from dba_tab_privs a
order by 1,2,3
spool off


spool gr_role4.rslt

select 'Query Roles Granted to Employees or Other Query Role (ordered by Employee Name ),' from dual;

select 'Report Date: '||to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')||',' 
from dual;

select ',' from dual;


select 'Employee Name, Grantee, Role, Admin, Default,' from dual;
select ',,,Option?,Role?,' from dual;

select '"'||initcap(substr(b.lname,1,25))||', '||
         initcap(substr(b.fname,1,10))||'",'||
	substr(a.grantee,1,15)||','||
	a.granted_role||','||
	a.admin_option||','||
	a.default_role||','
from dba_role_privs a,
	a_fin_es.esd_employees b
where substr(a.grantee,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
order by b.lname,b.fname,a.grantee,a.granted_role;

spool off
set pages 0
set linesize 160

spool gr_role5.rslt

select 'System Privileges Granted to Roles or Users(ordered by Roles/Users),' from dual;
select 'Report Date: '||to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')||',' from dual;

select ',' from dual;

select 'Grantee, Privilege, Admin?,' from dual;
select ',,Option,' from dual;

select substr(a.grantee,1,15)||','||
	substr(a.privilege,1,30)||','||
	a.admin_option||','
from dba_sys_privs a
order by a.grantee, a.privilege;

spool off
exit

