Message-Id: <10627.117652@fatcity.com> From: "Deshpande, Kirti" Date: Fri, 22 Sep 2000 08:19:06 -0500 Subject: RE: Anybody ever tried to use ON_LOGON triggers? Hi All, One minor detail about this 'AFTER LOGON' trigger..=20 If this trigger ever becomes invalid or does not fire (due to the table holding logon info runs out of space, for example), the only way to log = on to the database is to CONNECT INTERNAL and to disable the the trigger. = No one (including sys and system) can get in until this is done.. =20 - Kirti =20 > -----Original Message----- > From: Vincent Ruger [SMTP:Vincent.Ruger@nos.nl] > Sent: Friday, September 22, 2000 6:01 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Anybody ever tried to use ON_LOGON triggers? >=20 > Yes, and it works (aix 4.3.2 / Oracle 8.1.6.1.0)=20 >=20 > create or replace trigger user_logon after logon on database=20 > declare=20 > cursor c1 is=20 > select v1.username, v1.osuser, v1.logon_time, v1.terminal=20 > from v$session v1=20 > where v1.username=3Duser;=20 > my_user varchar2(30);=20 > my_os varchar2(39);=20 > my_time date;=20 > my_term varchar2(10);=20 > begin=20 >=20 > for x in c1 loop=20 > fetch c1 into my_user,my_os,my_time,my_term;=20 > insert into user_logon values(my_user,my_os,my_time,my_term);=20 > commit;=20 > end loop;=20 >=20 > end;=20 >=20 > Table and trigger created as system, grant v%_session from sys to = system=20 >=20 > good luck=20 >=20 > Vincent Ruger=20 > (Oracle DBA)=20 >=20 > -----Oorspronkelijk bericht-----=20 > Van: root@fatcity.com [ ]Namens Rahul=20 > Verzonden: vrijdag 22 september 2000 12:15=20 > Aan: Multiple recipients of list ORACLE-L=20 > Onderwerp: RE: Anybody ever tried to use ON_LOGON triggers?=20 >=20 >=20 > I tried this and DID NOT work(metalink is wrong about this)=20 > it returns SYS's sessionid always...=20 > so i use the following..=20 >=20 > WHERE username =3D user=20 > and logon_time =3D (select max(logon_time) from sys.v_$session = where=20 > username=3Duser)=20 >=20 >=20 >=20 > > ----------=20 > > From: Schoen Volker[SMTP:v.schoen@inplan.de]=20 > > Sent: Friday, September 22, 2000 3:13 PM=20 > > To: 'Vergara, Michael (TEM)'; 'Oracle DBA List (Quickdoc)'=20 > > Subject: AW: Anybody ever tried to use ON_LOGON triggers?=20 > >=20 > > Hi Mike,=20 > >=20 > > try to select the infos from v$session, I think it should work, = haven't=20 > > tested it with logon trigger, but with normal trigger.=20 > >=20 > > select SID,SERIAL# from v$session where audsid =3D = userenv('SESSIONID');=20 > >=20 > > Regards=20 > >=20 > > Volker Sch=F6n=20 > > E-Mail: =20 > > =20 > >=20 > >=20 > >=20 > > -----Urspr=FCngliche Nachricht-----=20 > > Von: Vergara, Michael (TEM) [ ]=20 > > Gesendet: 21. September 2000 19:51=20 > > An: 'Oracle DBA List (Quickdoc)'=20 > > Betreff: Anybody ever tried to use ON_LOGON triggers?=20 > >=20 > >=20 > > Hi DBAs!=20 > >=20 > > I experimenting with ON_LOGON triggers, learning about them as a = step=20 > > in setting up a 'Virtual Private Database' in Oracle 8i. However,=20 > > I cannot get something to work.=20 > >=20 > > I'm trying to get the user's session id from within the trigger. = I've=20 > > tried to use SYS_CONTEXT('USERENV','SESIONID') and=20 > > USERENV('SESSIONID') and both return '0' from within the trigger. = Now=20 > > 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?=20 > > And if that doesn't work, what else doesn't work?=20 > >=20 > > Anybody?=20 > >=20 > > TIA,=20 > > Mike=20 > >=20 > > ---=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=20 > > Michael P. Vergara | I do it for the stories I can tell=20 > > Guidant Corporation | - John = Sebastian=20 > > Oracle DBA |=20 > >=20 > >=20 > >=20 > > --------=20 > > If you're bored, then visit the list's website: = >=20 > > (updated daily)=20 > > to unsubscribe, send a blank email to > oracledba-unsubscribe@quickdoc.co.uk=20 > > to subscribe send a blank email to = oracledba-subscribe@quickdoc.co.uk=20 > >=20 > > --------=20 > > If you're bored, then visit the list's website: = >=20 > > (updated daily)=20 > > to unsubscribe, send a blank email to > oracledba-unsubscribe@quickdoc.co.uk=20 > > to subscribe send a blank email to = oracledba-subscribe@quickdoc.co.uk=20 > >=20 > --=20 > Please see the official ORACLE-L FAQ: =20 > --=20 > Author: Rahul=20 > INET: rahul@ratelindo.co.id=20 >=20 > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051=20 > San Diego, California -- Public Internet access / Mailing = Lists=20 > --------------------------------------------------------------------=20 > To REMOVE yourself from this mailing list, send an E-Mail message=20 > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in=20 > the message BODY, include a line containing: UNSUB ORACLE-L=20