RE: What stupid mistake am I making with this onlogin trigger?

Is the user account a dba account? If so, dba accounts bypass logon triggers.  

Ken Naim  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay.Miller_at_tdameritrade.com Sent: Monday, February 25, 2008 3:34 PM
To: oracle-l_at_freelists.org
Subject: What stupid mistake am I making with this onlogin trigger?  

After going back an forth on this for a while today I figured a few more sets of eyes couldn't hurt.

I need to track connections from a specific module (not SQL*Plus but that was easiest for testing purposes). This trigger compiles but does not create any rows. The insert statement works if run from a sql*plus prompt. I'm sure I'm missing something really obvious here...

Does the onlogin trigger possibly execute as the user logging in as opposed to as sys?

Trigger created:
SQL> create or replace trigger sys.trace_php_onlogin after logon on database
  2 begin
  3
  4 insert into wsidba.php_logins
  5 select sid,username,module
  6 from sys.v_$session
  7 where sid = (select unique(sid) from v$mystat)   8 and upper(module) like '%SQL%';
  9 commit;
 10
 11 end;
 12 /

Trigger created.

Doesn't enter any data to table:
usnjc01urp001:/services/home/oracle<lass> $ sqlplus wsidba

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 25 15:23:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production

SQL> select * from wsidba.php_logins;

no rows selected

Works from SQL*Plus:
$ sqlplus wsidba

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 25 15:25:58 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production

SQL> insert into wsidba.php_logins
  select sid,username,module
  from sys.v_$session
  where sid = (select unique(sid) from v$mystat)   and upper(module) like '%SQL%'; 2 3 4 5

1 row created.

TIA,
Jay Miller

--
http://www.freelists.org/webpage/oracle-l