Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: quick qestion about logon/logoff triggers - does system have to own them?

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

From: joel garry <>
Date: Wed, 26 Sep 2007 11:01:14 -0700
Message-ID: <>

On Sep 26, 9:54 am, GS <> 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;

I gotta wonder if the auditors will pick up on that one... :-) ...

> 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

That may be an intractable problem. If users can use any OS, users can make it look like it's any other tool. The most you can do is not allow remote dba access or be real picky about grants&roles. AFAIK. There is some security by obscurity, but that doesn't stop the ones you would need to catch. For example, I just went into the bin directory of XE, copied sqlplus.exe to hackerdoodle.exe, executed it, and select program from v$session. My program was listed as hackerdoodle.exe. I could have called it toad... unix users might have to know how to recompile or search usenet for argv[0]...

On the other hand, auditors, they may just need a trail, and may not care about little details if not told.


-- is bogus.
"Puppy dogs are like cute cuddly little piranhas with razor sharp
teeth." - Ralph Garman
Received on Wed Sep 26 2007 - 13:01:14 CDT

Original text of this message