Re: Problem with logon trigger

No I am not suggesting that Audit is not useful. Database Event Triggers are much more
flexible and extensible.
Even otherwise, it is hardly any effort to create a table and write a single trigger to capture logon information alone.
And the trigger can be added, enabled, disabled online without having to restart the instance to change the audit_trail= init.ora parameter. I have a database where logon information was required "we want to capture all logons from tomorrow" but I could not schedule a database shutdown and restart to set audit_trail or to relocate the SYS.AUD$ table.

Hemant K Chitale

"Rick Wessman" <Rick.Wessman_at_oracle.com> wrote in message news:socvge3potd.fsf_at_rwessman-sun.us.oracle.com...
> Hemant:
>
> Just because the audit feature is "old," it doesn't mean that it isn't
> useful. In this case, it does what the original poster wanted.
>
> IMO, moving aud$ out of SYS and enabling auditing once is a lot easier
than
> writing and maintaining a logon trigger that replicates already existing
> functionality.
>
> If the request was for a trigger that would do complex operations, then I
> would agree with you.
>
> Rick
>
> "Hemant K Chitale" <hkchital_at_singnet.com.sg> writes:
>
> > Georg,
> >
> > You do need to grant SELECT ON V_$SESSION
> > to PUBLIC or use V$SESSION which
> > is already a PUBLIC SYNONYM for SYS.V_$SESSION.
> > Normally, you'd create a Logon trigger in
> > the SYSTEM or any other non-SYS DBA
> > account. In that case grant SELECT on
> > SYS.V_$SESSION to such account.
> >
> > Sybrand,
> >
> > As with many of your postings you do seem to be
> > behind the times. The Logon trigger (actually
> > database event trigger) is new (8i) ;
> > the audit feature is old.
> > There are good reasons for using database triggers.
> > e.g. using database triggers for logon auditing
> > you can setup your own audit table, do not need
> > to worry about relocating SYS.AUD$ out of the
> > SYSTEM Tablespace, you can write your own
> > PLSQL code to authenticate/disallow users etc.
> > Look up Note 74173.1 on Database Event Triggers in 8i.
> >
> > Hemant K Chitale
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:kc964u0olc3k2jke9p5vqje5vltstknlfb_at_4ax.com...
> > > On 14 Jan 2002 01:22:56 -0800, georg.scholz_at_ubisol.at (Georg Scholz)
> > > wrote:
> > >
> > > >Hello,
> > > >
> > > >we are using Oracle 8.1.7 on a W2K server.
> > > >
> > > >I have a problem with a logon trigger.
> > > >The idea is to store the OS-Username in a separate table when a user
> > > >logs on:
> > > >
> > > >CREATE OR REPLACE TRIGGER DB_LOGON
> > > >after logon on database
> > > >begin
> > > > INSERT INTO T_SESSIONS (SESSIONID, TERMINAL, OSUSER, LOGONTIME,
> > > >LASTALIVE)
> > > > SELECT userenv('sessionid'), TERMINAL, OSUSER, sysdate as s1,
sysdate
> > > >as s2
> > > > FROM SYS.V_$SESSION WHERE AUDSID = userenv('sessionid');
> > > >end;
> > > >
> > > >Problem: when creating this trigger, I get: "SYS.V_$SESSION must be
> > > >declared".
> > > >
> > > >However, in a normal SQL PLUS Window, the SQL-statement contained in
> > > >the trigger works fully OK!! I tried to create a synonym to
> > > >SYS.V_$SESSION, but I get the same error.
> > > >
> > > >Do you have any hints??
> > > >
> > > >Thanks in advance
> > > >Georg Scholz
> > >
> > >
> > > You seem to try to reinvent the wheel. Oracle has already the audit
> > > function for this. Just set your audit_trail parameter to db,
> > > issue audit connect and you're there.
> > >
> > > Hth
> > >
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > To reply remove -verwijderdit from my e-mail address
> >
> >
>
> --
> Rick Wessman
> Security Assurance Group
> Oracle Corporation
> Rick.Wessman_at_oracle.com
>
> The opinions expressed above are mine and do not necessarily reflect
> those of Oracle Corporation.
Received on Wed Jan 16 2002 - 09:03:13 CST