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

On Sep 26, 10:59 am, GS <G..._at_GS.com> 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

What version and edition of the Oracle database server software are you running?

You might want to investigate the auditing functionality supplied by Oracle.
Why put code in userspace that can run as part of the Oracle kernel?

1. set the audit_trail parameter in the init.ora or spfile.
2. bounce the database instance
3. in sqlplus as user sys: SQL> audit session;
4. select <something> from dba_audit_session where <filtering condition>
5. determine how the audit trail will be managed, in terms of retention and archival. Keeping it indefinitely is an option but it will increase the size of the system tablespace.

If you're using shared accounts or connection pooling, things get more complicated.
If you're using dedicated server connections with each user having their own oracle user account, you're done.

This is a very common subject.
Start with the relevant docs for the version that you're running, such as:

http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm

-bdbafh Received on Wed Sep 26 2007 - 10:42:59 CDT