Re: Problem with logon trigger

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.