Re: Problem with logon trigger

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
Received on Tue Jan 15 2002 - 07:29:16 CST