RE: logon trigger with machine condition

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Tue, 1 Feb 2005 14:39:06 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A972C8@exchsen0a1ma>

Jason,

Try this:

  CURSOR c1 IS
    SELECT UPPER(program) program, username,

           osuser, terminal,
           sys_context('USERENV','IP_ADDRESS') ip_addr
      FROM V$SESSION
      WHERE AUDSID = USERENV('SESSIONID');

c1_rec c1%ROWTYPE;

BEGIN    OPEN c1;
   FETCH c1 INTO c1_rec;
   CLOSE c1;

c1_rec.ip_addr will have the ip address of the user logging on. This should work for you, right?

Tom

-----Original Message-----

From: jsb_at_digistar.com [mailto:jsb_at_digistar.com] Sent: Tuesday, February 01, 2005 2:35 PM To: oracle-l_at_freelists.org
Subject: logon trigger with machine condition

I'm looking for a way to trace a session on logon with the condition that it be from a particular host. Wolfgang brought up a good point about logon to a particular schema but in this case I need a database-wide trigger.

I'm looking to do something like this below, but it does not work:

CREATE OR REPLACE TRIGGER trace_a_session

   AFTER LOGON ON DATABASE
BEGIN
   IF USER LIKE 'USER'
   THEN

      IF HOST LIKE 'SOMEHOST.DOMAIN.COM'
      THEN
         EXECUTE IMMEDIATE 'alter session set events ''10046 trace name
context forever, level 12''';
      END IF;

   END IF;
END;
/

There apparently is no PL/SQL HOST function which is what I need, or a way to do it via a nested select and variables. I intend to purchase a book or two after work tonight but I need some help now if anyone has dealt with this situation before.

regards,
Jason
--

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

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 01 2005 - 14:41:47 CST