Re: quick qestion about logon/logoff triggers - does system have to own them?

From: GS <GS_at_GS.com>
Date: Wed, 26 Sep 2007 16:54:50 GMT
Message-ID: <u9wKi.87569$Pd4.48903@edtnps82>

Brian Peasland wrote:
> GS wrote:
>

>> In order to keep the SOX beancounters-from-hell(Auditors) at bay I 
>> have to provide a report for my boss that shows who logged on to which 
>> database, from which machine, to which database, with which app, etc.
>>
>> I plan on putting in a simple table that will get populated by two 
>> triggers, one after login and one before logoff.
>>
>> Do the logon/logoff triggers have to be owned by system or sys? I was 
>> thinking I'd create an "audit" user with its own tablespace (or use 
>> the tools tablspace) and keep the table out of the system/sys schemas.
>>
>> Out of curiousity, how much of a PITA has it been for anyone else out 
>> there implementing SOX, and to what lengths did you go to become 
>> compliant?
>>
>> thanks

>
>
> Instead of writing your own triggers, why not implement auditing? You
> can audit logons, but not logoffs. But keep in mind that any system
> trigger to capture logoff information might not work if the user just
> closes the app or their session gets killed by some other means. Unless
> the session is logged off gracefully, you may miss this information.
>
> These system triggers should be owned by SYS. However, the trigger's
> code can insert into any table even those not owned by SYS.
>
> HTH,
> Brian
>
>

thanks all and I apologise for forgetting the version, which is 9.2.0.x

Auditing is something I haven't done a lot of, so I'll check out the built in auditing in 9i. FWIW the way I had planned on doing this is:

/*=========================================================================*/

drop table session_info;

CREATE TABLE session_info
(user_name VARCHAR2(30),
  log_date DATE,
  session_id VARCHAR2(30),
  ip_addr VARCHAR2(30),
  hostname VARCHAR2(30),
  auth_type VARCHAR2(30),
  event VARCHAR2(8),
os_user varchar2(30))
tablespace tools;

CREATE OR REPLACE TRIGGER TRG_LOGOFF_INFO BEFORE LOGOFF
ON DATABASE
DECLARE

    session_id VARCHAR2(30);
    ip_addr    VARCHAR2(30);
    hostname   VARCHAR2(30);
    os_user    VARCHAR2(30);
    auth_type  VARCHAR2(30);

event varchar2(10);

   BEGIN
    SELECT sys_context ('USERENV', 'SESSIONID')     INTO session_id
    FROM dual;
    SELECT sys_context ('USERENV', 'IP_ADDRESS')     INTO ip_addr
    FROM dual;
    SELECT sys_context ('USERENV', 'HOST')     INTO hostname
    FROM dual;
    SELECT sys_context ('USERENV', 'OS_USER')     INTO os_user
    from dual;
    SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')     INTO auth_type
    FROM dual;
    select 'LOG OFF' into event from dual;     INSERT INTO session_info VALUES
    (user, sysdate, session_id, ip_addr, hostname, auth_type, event, os_user);

   END;
/

CREATE OR REPLACE TRIGGER TRG_LOGON_INFO AFTER LOGON
ON DATABASE
DECLARE

    session_id VARCHAR2(30);
    ip_addr    VARCHAR2(30);
    hostname   VARCHAR2(30);
    os_user    VARCHAR2(30);
    auth_type  VARCHAR2(30);

    event varchar2(10);
   BEGIN
    SELECT sys_context ('USERENV', 'SESSIONID')     INTO session_id
    FROM dual;
    SELECT sys_context ('USERENV', 'IP_ADDRESS')     INTO ip_addr
    FROM dual;
    SELECT sys_context ('USERENV', 'HOST')     INTO hostname
    FROM dual;
    SELECT sys_context ('USERENV', 'OS_USER')     INTO os_user
    from dual;
    SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')     INTO auth_type
    FROM dual;
    select 'LOG ON' into event from dual;     INSERT INTO session_info VALUES
    (user, sysdate, session_id, ip_addr, hostname, auth_type, event, os_user);

   END;
/

/*=========================================================================*/

Which pretty well captures what I need to know, with the exception of which application they are connecting with, ie. sql*plus, toad, 3rd part app, etc.. I haven't figured that one out yet.. Received on Wed Sep 26 2007 - 11:54:50 CDT