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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anybody ever tried to use ON_LOGON triggers?

RE: Anybody ever tried to use ON_LOGON triggers?

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 22 Sep 2000 08:19:06 -0500
Message-Id: <10627.117652@fatcity.com>


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_at_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_at_fatcity.com [ <mailto:root_at_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_at_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: <mailto:v.schoen_at_inplan.de>=20
> > <http://www.inplan.de>=20
> >=20
> >=20
> >=20
> > -----Urspr=FCngliche Nachricht-----=20
> > Von: Vergara, Michael (TEM) [ <mailto:mvergara_at_guidant.com>]=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: =
<http://www.lazydba.com>
>=20

> > (updated daily)=20
> > to unsubscribe, send a blank email to
> oracledba-unsubscribe_at_quickdoc.co.uk=20
> > to subscribe send a blank email to =
oracledba-subscribe_at_quickdoc.co.uk=20
> >=20
> > --------=20
> > If you're bored, then visit the list's website: =
<http://www.lazydba.com>
>=20

> > (updated daily)=20
> > to unsubscribe, send a blank email to
> oracledba-unsubscribe_at_quickdoc.co.uk=20
> > to subscribe send a blank email to =
oracledba-subscribe_at_quickdoc.co.uk=20
> >=20
> --=20
> Please see the official ORACLE-L FAQ: <http://www.orafaq.com>=20
> --=20
> Author: Rahul=20
> INET: rahul_at_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_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20
> the message BODY, include a line containing: UNSUB ORACLE-L=20
Received on Fri Sep 22 2000 - 08:19:06 CDT

Original text of this message

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