/* ************************************************ */
/*                                                  */
/* fnctn:   Produce the report of those users found */
/*          accessing the database via a query tool */
/*          and not being limited by a database usr.*/
/*                                                  */
/* name:    @c:\oracle\sql\qry_scrp.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

whenever sqlerror exit 

set pagesize 2000
set verify off
set termout off

column today noprint new_value cur_date

spool  qry_scrp.rslt

ttitle left 'The following non-MIS users were found to be logged on to a' -
      skip 1 left 'query tool (Data Browser, QMX, SQL*Plus) within the ' -
      skip 1 left 'previous 24 hour period and do not have a profile limiting' -
      skip 1 left 'their access.  At the time of this report, the following' -
      skip 1 left 'users were added to a limiting profile.' -
      skip 1 -
      skip 1 left 'Report Date: ' cur_date

ttitle off

select to_char(sysdate,'MM-DD-YYYY HH24:MI') today from dual;

column username format A8 heading 'User Id'
column osuser format A9 heading 'OS User'
column userfullname format A23 heading 'User Full Name'
column tool format A9 heading 'Tool'
column runtimes format A11 heading 'Start Time|End Time'
column logicalreads format 9,999,999,999 heading 'Logical Reads'

ttitle on

select a.username username,
       a.osuser osuser,
       upper(substr(b.fname,1,1))||lower(substr(b.fname,2,7))||' '||
           upper(substr(b.lname,1,1))||lower(substr(b.lname,2,15)) userfullname,
       'Browser' tool,
       min(to_char(a.query_date,'MM/DD HH24:MI'))||
       max(to_char(a.query_date,'MM/DD HH24:MI')) runtimes,
       max(a.block_gets + a.consistent_gets) logicalreads
  from sys.session_history a, a_fin_es.esd_employees b, dba_users c
 where substr(a.username,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
   and a.username = c.username
   and b.mail_stop_id not like 'ISD%'
   and c.profile = 'DEFAULT'
   and exists
        (select 'x'
           from sys.session_history d
          where d.query_date > sysdate - 1 
            and upper(d.program) like '%BROW%'
            and d.username like 'U%'
            and d.username = a.username
            and d.osuser = a.osuser
            and d.process = a.process
            and d.sid = a.sid)
 group by a.username, a.osuser, b.lname, b.fname
union
select a.username username,
       a.osuser osuser,
       upper(substr(b.fname,1,1))||lower(substr(b.fname,2,7))||' '||
           upper(substr(b.lname,1,1))||lower(substr(b.lname,2,15)) userfullname,
       'QMX' tool,
       min(to_char(a.query_date,'MM/DD HH24:MI'))||
       max(to_char(a.query_date,'MM/DD HH24:MI')) runtimes,
       max(a.block_gets + a.consistent_gets) logicalreads
  from sys.session_history a, a_fin_es.esd_employees b, dba_users c
 where substr(a.username,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
   and a.username = c.username
   and b.mail_stop_id not like 'ISD%'
   and c.profile = 'DEFAULT'
   and exists
        (select 'x'
           from sys.session_history d
          where d.query_date > sysdate - 1 
            and upper(d.program) like '%QMX%'
            and d.username like 'U%'
            and d.username = a.username
            and d.osuser = a.osuser
            and d.process = a.process
            and d.sid = a.sid)
 group by a.username, a.osuser, b.lname, b.fname
union
select a.username username,
       a.osuser osuser,
       upper(substr(b.fname,1,1))||lower(substr(b.fname,2,7))||' '||
           upper(substr(b.lname,1,1))||lower(substr(b.lname,2,15)) userfullname,
       'SQL*Plus' tool,
       min(to_char(a.query_date,'MM/DD HH24:MI'))||
       max(to_char(a.query_date,'MM/DD HH24:MI')) runtimes,
       max(a.block_gets + a.consistent_gets) logicalreads
  from sys.session_history a, a_fin_es.esd_employees b, dba_users c
 where substr(a.username,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
   and a.username = c.username
   and b.mail_stop_id not like 'ISD%'
   and c.profile = 'DEFAULT'
   and exists
        (select 'x'
           from sys.session_history d
          where d.query_date > sysdate - 1 
            and upper(d.program) like '%SQLPLUS%'
            and d.username like 'U%'
            and d.username = a.username
            and d.osuser = a.osuser
            and d.process = a.process
            and d.sid = a.sid)
 group by a.username, a.osuser, b.lname, b.fname
 order by 1
/

spool off

REM Produce the "alter user" statements to modify those users identified     
REM to a database profile limiting the amount of resource they may 
REM utilize.

ttitle off
set heading off
set feedback off

spool qryusrsrpt.alt

select distinct
       'ALTER USER '||a.username||' PROFILE QUERY_LEVEL_20;'
  from sys.session_history a, a_fin_es.esd_employees b, dba_users c
 where substr(a.username,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
   and a.username = c.username
   and b.mail_stop_id not like 'ISD%'
   and c.profile = 'DEFAULT'
   and exists
        (select 'x'
           from sys.session_history d
          where d.query_date > sysdate - 1 
            and (   upper(d.program) like '%BROW%'
                 or upper(d.program) like '%SQLPLUS%'
                 or upper(d.program) like '%QMX%')
            and d.username like 'U%'
            and d.username = a.username
            and d.osuser = a.osuser
            and d.process = a.process
            and d.sid = a.sid)
/

spool off

exit
