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

On Sep 26, 11:54 am, GS <G..._at_GS.com> wrote:
> 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..- Hide quoted text -
>
> - Show quoted text -

The PROGRAM column in V$SESSION contains that information.

David Fitzjarrell Received on Wed Sep 26 2007 - 12:52:17 CDT