Re: Tracking users access

From: Rob Calfee <trace_at_primenet.com>
Date: Wed, 22 Sep 1999 20:35:48 GMT
Message-ID: <37e93d34.17704453@news.primenet.com>

Here is some doc and a script for creating a trigger to track your user logons. It also tracks how much CPU time they used. Let me know if I goofed on the doc, folks. I wrote it pretty quick. Hope this helps. Let me know how it goes.

TRACKING of Query/CPU time per user.

1. Start database with init.ora parameter audit_trail=none
2. Create needed objects and privileges.

#create aud$ table in tablespace of choice under the system account and
#rename the old aud$ for backup
connect sys/incsys_at_mhs
create table system.aud$ tablespace usr as select * from aud$; rename aud$ to aud$temp;

#create index for that table and grant all to the sys account connect system/incdba_at_mhs
create index i_aud1 on aud$(sessionid, ses$tid) tablespace indx; grant all on aud$ to sys with grant option;

#create a new view for aud$
connect sys/incsys_at_mhs
create view aud$ as select * from system.aud$;

3. Connect internal in svrmgr and run CATAUDIT.sql (/ORACLE_HOME/RDBMS80/admin)

4. Create table accounting in sys

CREATE TABLE sys.accounting
 (

  username                   VARCHAR2(30),
  login_time                 DATE,
  logoff_time                DATE,
  schemaname                 VARCHAR2(30),
  osuser                     VARCHAR2(30),
  process                    VARCHAR2(30),
  machine                    VARCHAR2(64),
  terminal                   VARCHAR2(30),
  program                    VARCHAR2(64),
  type                       VARCHAR2(30),
  logical_read               NUMBER,
  physical_read              NUMBER,
  sid                        NUMBER,
  session_sid                NUMBER,
  cpu_time                   NUMBER

 )
 PCTFREE 10
 PCTUSED 40
 INITRANS 1
 MAXTRANS 255
 TABLESPACE usr
 STORAGE (

      INITIAL     1001472
      NEXT        501760
      PCTINCREASE 0
      MINEXTENTS  1
      MAXEXTENTS  249

   )
/

5. Create trigger on system.aud$ to feed system.accounting when a user logs off. It will feed total cpu time (value from v$sysstat) into accounting when user logs off. It will also timestamp loggon and loggoff time. Each unit in value and CPU time = 1/100th of a second.

CREATE OR REPLACE TRIGGER sys.acc_trigger AFTER UPDATE
ON system.aud$
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (new.action# = 101 or new.action# = 102) begin

    insert into accounting

        select


            :new.userid,


            :new.timestamp#,


            :new.logoff$time,


            ss.schemaname,
            ss.osuser,
            ss.process,
            ss.machine,
            ss.terminal,
            ss.program,
            ss.type,


            :new.logoff$lread,


            :new.logoff$pread,


            st.sid, ss.audsid, st.value
        from V$STATNAME S,
             V$SESSTAT ST,
            V$SESSION SS
        where S.STATISTIC# = ST.STATISTiC#
        AND ST.SID = SS.SID
        and :new.sessionid = SS.AUDSID
        and S.NAME='CPU used by this session';
        end;

/
6. From svrmgr or sqlplus execute the audit session command.

7. Shutdown the database and set the following parameters in the init file:

Audit_trail = DB
Timed_statistics = true

8. Startup the database.

9. Go to server manager and key 'audit session' and enter, then close server manager.

1. After users have logged on, done some business, and logged off, run the following example sql script to find out when a user logged on and off and how much cpu time they used while they were on.

select username,

        osuser,
        username,
        to_char(LOGIN_TIME, 'dd.mm.yy:hh:mi'),
        to_char(LOGOFF_TIME, 'dd.mm.yy.:hh:mi'),
        machine,
        cpu_time

from sys.accounting

1. Pay attention to all the aud$ tables and the accounting table. THEY WILL GROW AND FRAGMENT. Schedule a deletion of information after backing up the data.

On Wed, 22 Sep 1999 15:20:08 +0100, "Margarida Afonso" <mrafonso_at_sonae.pt> wrote:

>
>Hi
>
>I would like to be able to track every user logon to an oracle database,
>even if the only thing he does is a select on the database tables.
>Does anyone know how that can be done ?
>
>Thank you for your help
>
>Margarida Afonso
>
>

Rob Calfee
DBA
rcalfee_at_incsystem.com Received on Wed Sep 22 1999 - 15:35:48 CDT