Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with logon trigger

Re: Problem with logon trigger

From: Georg Scholz <georg.scholz_at_vienna.at>
Date: Wed, 16 Jan 2002 16:31:11 GMT
Message-ID: <jVh18.78772$gX1.638891@news.chello.at>


Hello to all,

grant select on v_$session to theusername; --> it worked!

BTW, I know we are "re-inventing the wheel", but a) we need a simple way to trace logons and logoffs; b) the client program will periodically put timestamps into the log table, so we can check if clients are "alive".
c) When logging on, a unique key is created in the log table, which is later on used for marking records in data tables (for "stamping" data rows). This key is unique FOREVER. However, when using the audit feature, only the SESSIONID is created; and we found out that when moving the database from one server to another, the server starts counting the Session ID again at 1.

Thanks to all

Georg

"Hemant K Chitale" <hkchital_at_singnet.com.sg> wrote in message news:a244od$550$1_at_dahlia.singnet.com.sg...
>
> 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 - 10:31:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US