| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: LOGON/LOGOFF Trigger Issue
Dear Mladen,
Thanks for your response.
SYS and REPADMIN users are keep on LOGGING-IN and LOGGING-OUT periodically
in the background.
That is why SYS user got deleted(when automatic LOG-OUT happens).
Anyway I'll try auditing feature as you suggested.
Thanks again,
Sami
-----Original Message-----
Mladen Gogala
Sent: Sunday, January 11, 2004 2:04 PM
To: Multiple recipients of list ORACLE-L
What happens is that you have a database wide logon trigger,
not just a trigger on scott.schema which needs an execution context.
A pseudo session with a SID=0 is established and the trigger is executed.
Personally, I would use auditing ("audit session" or "audit session by
scott"),
not a database trigger.
On 2004.01.11 12:54, Sami wrote:
> Hi
>
> I am trying to maintain currently connected users list using LOGON/LOGOFF
> triggres.
> But I am experiencing some unexpected behavior for SYS user.
>
>
> CREATE OR REPLACE TRIGGER SCOTT.LOGON_TRIG
> AFTER LOGON ON DATABASE
> BEGIN
> insert into logon_info
>
values(SYS_CONTEXT('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','INSTANCE'),
> SYS_CONTEXT('USERENV','HOST'),USER,SYS_CONTEXT('USERENV',
> 'session_user'),sysdate);
> END;
> /
>
> CREATE OR REPLACE TRIGGER SCOTT.LOGOFF_TRIG
> BEFORE LOGOFF ON DATABASE
> BEGIN
> delete logon_info where
SESSIONID_NUMBER=SYS_CONTEXT('USERENV','SESSIONID');
> END;
> /
>
> ================================================================
> Initiated TWO session using SYS and SCOTT in a separate window
> ================================================================
>
> SQL> desc scott.logon_info
> Name Null? Type
> ----------------------------------------- -------- ----------------------
-
> SESSIONID_NUMBER VARCHAR2(50)
> INSTANCE_NUMBER VARCHAR2(36)
> HOST_NAME VARCHAR2(36)
> USER_NAME VARCHAR2(36)
> SESSION_USER VARCHAR2(36)
> LOGONDATE DATE
>
>
> SQL>set head off
> SQL> set time on
> 19:01:01 SQL> select * from loon_info;
>
> 0
> 1
> DELTA\SSEERANG
> SYS
> SYS
> 10-JAN-04
>
> 1411
> 1
> DELTA\SSEERANG
> SCOTT
> SCOTT
> 10-JAN-04
>
>
>
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: mgogala_at_adelphia.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sami INET: jaysingh1_at_optonline.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).Received on Sun Jan 11 2004 - 15:14:24 CST
 |
 |