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

From: GS <GS_at_GS.com>
Date: Wed, 26 Sep 2007 18:51:33 GMT
Message-ID: <VSxKi.106860$bO6.63466@edtnps89>

joel garry wrote:
> On Sep 26, 9: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;

>
>
> 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.
>
> jg
> --
> @home.com is bogus.
> "Puppy dogs are like cute cuddly little piranhas with razor sharp
> teeth." - Ralph Garman
>

I guess to get around that one could put a trigger in allowing users to connect only via certain apps, but then again they could rename whatever app they wanted to connect with with the name of what they were allowed to connect with. In any case, for now I'm not so worried about what they connect with as much as I am just logging it, but my next step after this is to do just that, allow sqlplus, toad etc.. connections to only a few select users.

I turned on auditing on a test database, and had a look at the dba_audit_trail view, it will probably actually do the trick but it does not capture the application, but I have to check and see if I even have to capture that info. By the looks of it even with the auditing enabled in the database you would still have to enter "SQL>session audit;" at database startup to start populating the table. Received on Wed Sep 26 2007 - 13:51:33 CDT