RE: Anybody ever tried to use ON

From: Vincent Ruger <Vincent.Ruger_at_nos.nl>
Date: Fri, 22 Sep 2000 12:00:03 +0200
Message-Id: <10627.117637@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0247B.E0CA064C
Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Yes, and it works (aix 4.3.2 / Oracle 8.1.6.1.0)

create or replace trigger user_logon after logon on database declare
cursor c1 is
select v1.username, v1.osuser, v1.logon_time, v1.terminal from v$session v1
where v1.username=3Duser;

my_user  varchar2(30);
my_os    varchar2(39);
my_time  date;
my_term  varchar2(10);

begin

for x in c1 loop
fetch c1 into my_user,my_os,my_time,my_term; insert into user_logon values(my_user,my_os,my_time,my_term); commit;
end loop;

end;

Table and trigger created as system, grant v%_session from sys to = system

good luck

Vincent Ruger
(Oracle DBA)

-----Oorspronkelijk bericht-----
Van: root_at_fatcity.com [mailto:root_at_fatcity.com]Namens Rahul Verzonden: vrijdag 22 september 2000 12:15 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Anybody ever tried to use ON_LOGON triggers?

I tried this and DID NOT work(metalink is wrong about this)=20 it returns SYS's sessionid always...
so i use the following..

WHERE username =3D user
and logon_time =3D (select max(logon_time) from sys.v_$session where username=3Duser)

> ----------
> From: Schoen Volker[SMTP:v.schoen_at_inplan.de]
> Sent: Friday, September 22, 2000 3:13 PM
> To: 'Vergara, Michael (TEM)'; 'Oracle DBA List (Quickdoc)'
> Subject: AW: Anybody ever tried to use ON_LOGON triggers?

>=20

> Hi Mike,

>=20
> try to select the infos from v$session, I think it should work, =
haven't
> tested it with logon trigger, but with normal trigger.
>=20
> select SID,SERIAL# from v$session where audsid =3D =
userenv('SESSIONID');

>=20

> Regards

>=20

> Volker Sch=F6n
> E-Mail: mailto:v.schoen_at_inplan.de
> http://www.inplan.de

>=20
>=20
>=20

> -----Urspr=FCngliche Nachricht-----
> Von: Vergara, Michael (TEM) [mailto:mvergara_at_guidant.com]
> Gesendet: 21. September 2000 19:51
> An: 'Oracle DBA List (Quickdoc)'
> Betreff: Anybody ever tried to use ON_LOGON triggers?

>=20
>=20

> Hi DBAs!

>=20

> I experimenting with ON_LOGON triggers, learning about them as a step
> in setting up a 'Virtual Private Database' in Oracle 8i. However,=20
> I cannot get something to work.

>=20
> I'm trying to get the user's session id from within the trigger. =
I've
> tried to use SYS_CONTEXT('USERENV','SESIONID') and=20
> USERENV('SESSIONID') and both return '0' from within the trigger. =
Now
> since this is an 'AFTER LOGON' trigger I think it should know the=20
> session id. It DOES know the IP_ADDRESS, so why not the SESSION_ID?
> And if that doesn't work, what else doesn't work?

>=20

> Anybody?

>=20

> TIA,
> Mike

>=20

> ---
> =

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D


> Michael P. Vergara      | I do it for the stories I can tell

> Guidant Corporation     |                           - John Sebastian

> Oracle DBA              |

>=20
>=20
>=20

>=20

> --------
> If you're bored, then visit the list's website: =
http://www.lazydba.com
> (updated daily)
> to unsubscribe, send a blank email to =
oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>=20
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author: Rahul
INET: rahul_at_ratelindo.co.id

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C0247B.E0CA064C
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12">

<TITLE>RE: Anybody ever tried to use ON_LOGON triggers?</TITLE>
</HEAD>
<BODY>

Yes, and it works (aix 4.3.2 / Oracle = 8.1.6.1.0)

create or replace trigger user_logon after logon on = database

<BR><FONT SIZE=3D2>declare</FONT>
<BR><FONT SIZE=3D2>cursor c1 is</FONT>
<BR><FONT SIZE=3D2>select v1.username, v1.osuser, v1.logon_time, =

v1.terminal

<BR><FONT SIZE=3D2>from v$session v1</FONT>
<BR><FONT SIZE=3D2>where v1.username=3Duser;</FONT>
<BR><FONT SIZE=3D2>my_user&nbsp; varchar2(30);</FONT>
<BR><FONT SIZE=3D2>my_os&nbsp;&nbsp;&nbsp; varchar2(39);</FONT>
<BR><FONT SIZE=3D2>my_time&nbsp; date;</FONT>
<BR><FONT SIZE=3D2>my_term&nbsp; varchar2(10);</FONT>
<BR><FONT SIZE=3D2>begin</FONT>
</P>

for x in c1 loop  fetch c1 into = my_user,my_os,my_time,my_term;
  insert into user_logon = values(my_user,my_os,my_time,my_term);

<BR><FONT SIZE=3D2>&nbsp;commit;</FONT>
<BR><FONT SIZE=3D2>end loop;</FONT>
</P>

end;

Table and trigger created as system, grant v%_session = from sys to system

good luck

<P><FONT SIZE=3D2>Vincent Ruger</FONT>
<BR><FONT SIZE=3D2>(Oracle DBA)</FONT>
</P>

-----Oorspronkelijk bericht----- Van: root_at_fatcity.com [<A = HREF=3D"mailto:root_at_fatcity.com">mailto:root_at_fatcity.com</A>]Namens = Rahul

<BR><FONT SIZE=3D2>Verzonden: vrijdag 22 september 2000 12:15</FONT>
<BR><FONT SIZE=3D2>Aan: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Onderwerp: RE: Anybody ever tried to use ON_LOGON =

triggers?

I tried this and DID NOT work(metalink is wrong about = this)

<BR><FONT SIZE=3D2>&nbsp;it returns SYS's sessionid always...</FONT>
<BR><FONT SIZE=3D2>so i use the following..</FONT>
</P>

WHERE username =3D user and     logon_time =3D (select = max(logon_time) from sys.v_$session where

<BR><FONT SIZE=3D2>username=3Duser)</FONT>
</P>
<BR>
<BR>

> ---------- > From: =
        Schoen = Volker[SMTP:v.schoen_at_inplan.de]
 > Sent: =
        Friday, September 22, 2000 = 3:13 PM
 > To:   'Vergara, Michael (TEM)'; 'Oracle = DBA List (Quickdoc)'
 > Subject:      AW: Anybody = ever tried to use ON_LOGON triggers?

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Hi Mike,</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; try to select the infos from v$session, I think =

it should work, haven't
 > tested it with logon trigger, but with normal = trigger.
 > 
 > select SID,SERIAL# from v$session where audsid = =3D userenv('SESSIONID');

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Regards</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Volker Sch=F6n</FONT>
<BR><FONT SIZE=3D2>&gt; E-Mail: <A =

HREF=3D"mailto:v.schoen_at_inplan.de">mailto:v.schoen_at_inplan.de</A> > <A HREF=3D"http://www.inplan.de" = TARGET=3D"_blank">http://www.inplan.de</A>

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; -----Urspr=FCngliche Nachricht-----</FONT>
<BR><FONT SIZE=3D2>&gt; Von: Vergara, Michael (TEM) [<A =

HREF=3D"mailto:mvergara_at_guidant.com">mailto:mvergara_at_guidant.com</A>]</F=

ONT>
<BR><FONT SIZE=3D2>&gt; Gesendet: 21. September 2000 19:51</FONT>
<BR><FONT SIZE=3D2>&gt; An: 'Oracle DBA List (Quickdoc)'</FONT>
<BR><FONT SIZE=3D2>&gt; Betreff: Anybody ever tried to use ON_LOGON =

triggers?

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Hi DBAs!</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; I experimenting with ON_LOGON triggers, =

learning about them as a step
 > in setting up a 'Virtual Private Database' in = Oracle 8i.  However,

<BR><FONT SIZE=3D2>&gt; I cannot get something to work.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; I'm trying to get the user's session id from =

within the trigger.  I've

<BR><FONT SIZE=3D2>&gt; tried to use SYS_CONTEXT('USERENV','SESIONID') =
and </FONT>
<BR><FONT SIZE=3D2>&gt; USERENV('SESSIONID') and both return '0' from =

within the trigger.  Now
 > since this is an 'AFTER LOGON' trigger I think = it should know the 
 > session id.  It DOES know the IP_ADDRESS, = so why not the SESSION_ID?
 > And if that doesn't work, what else doesn't = work?

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Anybody?</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; TIA,</FONT>
<BR><FONT SIZE=3D2>&gt; Mike</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; ---</FONT>
<BR><FONT SIZE=3D2>&gt; =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT= >
 > Michael P. =
Vergara      | I do it for the stories I can = tell

<BR><FONT SIZE=3D2>&gt; Guidant Corporation&nbsp;&nbsp;&nbsp;&nbsp; =
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=

;   - John Sebastian
 > Oracle =
DBA           &nb= sp;  |

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; --------</FONT>
<BR><FONT SIZE=3D2>&gt; If you're bored, then visit the list's website: =
<A HREF=3D"http://www.lazydba.com" =

TARGET=3D"_blank">http://www.lazydba.com</A> > (updated daily) > to unsubscribe, send a blank email to = oracledba-unsubscribe_at_quickdoc.co.uk > to subscribe send a blank email to = oracledba-subscribe_at_quickdoc.co.uk

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; --------</FONT>
<BR><FONT SIZE=3D2>&gt; If you're bored, then visit the list's website: =
<A HREF=3D"http://www.lazydba.com" =

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =

HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Rahul</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: rahul_at_ratelindo.co.id</FONT>
</P>

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051 San Diego, =
California        -- Public Internet = access / Mailing Lists
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

-----
 To REMOVE yourself from this mailing list, send an = E-Mail message
 to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in Received on Fri Sep 22 2000 - 05:00:03 CDT