FreeLists / oracle-l / RE: What stupid mistake am I making with this onlogin trigger?

["Ken Naim" <kennaim@xxxxxxxxx>]

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

 

Ken Naim

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Monday, February 25, 2008 3:34 PM
To: oracle-l@xxxxxxxxxxxxx
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