Home » SQL & PL/SQL » SQL & PL/SQL » Changing a logon trigger does not work (Oracle 9.2.0.7)
Changing a logon trigger does not work [message #312213] Tue, 08 April 2008 03:51 Go to next message
Uwe
Messages: 260
Registered: February 2003
Location: Z├╝rich, Switzerland
Senior Member
Hi all,

I guess thats an simple question but I am not that familiar with PL/SQL so I will ask for some help.

We have build up a logon trigger which sets some SESSION Parameters during LOGON. The Parameters are stored in a DB-Table.

by now I want to implement new parameters like FORCE PARALLEL DML

I inserted them into the table and try to rebuild the trigger.

Here's the Original one:

CREATE OR REPLACE TRIGGER SYS.LOGON_CHECK
AFTER LOGON
ON DATABASE
DECLARE

v_client_ip   VARCHAR2(50);
v_server_ip   VARCHAR2(50);
v_client_host VARCHAR2(50);
v_connect     BOOLEAN := FALSE;

CURSOR log_cur (p_user VARCHAR2)
IS
SELECT regel
FROM   system.rtc$logon_check
WHERE  p_user LIKE UPPER(like_user_name)
AND    INSTR(UPPER(NVL(not_like_user_name,' ')), p_user) = 0
AND    aktiv = 'J';

BEGIN

  FOR log_rec IN log_cur (UPPER(user))
    LOOP
      IF UPPER(SUBSTR(log_rec.regel,0,4)) LIKE 'SET%' 
        THEN EXECUTE IMMEDIATE ('ALTER SESSION '||log_rec.regel);
      ELSIF UPPER(log_rec.regel) LIKE 'CLIENTIP=SERVERIP%'
        THEN -- Herausfinden der Client und Server IP-Adresse
             SELECT NVL(sys_context('USERENV', 'IP_ADDRESS'),
                    utl_inaddr.get_host_address(SUBSTR(SYS_CONTEXT('USERENV', 'HOST'),INSTR(SYS_CONTEXT('USERENV', 'HOST'),'\')+1))),
                    utl_inaddr.get_host_address
             INTO   v_client_ip,
                    v_server_ip
             FROM   dual;

             IF v_client_ip != v_server_ip AND v_client_ip != '127.0.0.1' 
               THEN
                SELECT sys_context('USERENV', 'HOST')
                 INTO   v_client_host
                 FROM   dual; 

                 FOR  server_rec IN (SELECT *
                                     FROM   system.rtc$logon_check
                                     WHERE  UPPER(user) LIKE UPPER(like_user_name)
                                     AND    aktiv = 'J')
                   LOOP
                     IF UPPER(v_client_host) LIKE UPPER('%'||LTRIM(server_rec.regel,'SERVER=')||'%')
                       THEN 
                         -- Alles ok, Session darf connecten
                         v_connect := TRUE;
                     END IF;
                   END LOOP;

                 IF NOT v_connect
                   THEN 
                     RAISE_APPLICATION_ERROR (-20999,'REMOTE '||user||' CONNECTS FROM '||v_client_host||' ARE NOT ALLOWED (LOGON_CHECK-Trigger)');
                   END IF;

             END IF;

      END IF;
    END LOOP;

END;
/


I just tried to change the IF Sequence with

IF UPPER(SUBSTR(log_rec.regel,0,4)) LIKE 'SET%' 
        THEN EXECUTE IMMEDIATE ('ALTER SESSION '||log_rec.regel);
ELSIF UPPER(log_rec.regel) LIKE 'CLIENTIP=SERVERIP%'
....
END IF

to
IF UPPER(SUBSTR(log_rec.regel,0,4)) LIKE 'SET%' OR UPPER(SUBSTR(log_rec.regel,0,4)) LIKE 'FOR%'
        THEN EXECUTE IMMEDIATE ('ALTER SESSION '||log_rec.regel);
ELSIF UPPER(log_rec.regel) LIKE 'CLIENTIP=SERVERIP%'
...
END IF


or
IF UPPER(SUBSTR(log_rec.regel,0,4)) LIKE 'SET%' 
        THEN EXECUTE IMMEDIATE ('ALTER SESSION '||log_rec.regel);
ELSIF UPPER(SUBSTR(log_rec.regel,0,5)) LIKE 'FORC%'
        THEN EXECUTE IMMEDIATE ('ALTER SESSION '||log_rec.regel);
ELSIF UPPER(log_rec.regel) LIKE 'CLIENTIP=SERVERIP%'
...
END IF


But both ways blocked the connects. The users were not able to connect anymore.
How can I implement two different ELSIF parts into one IF ?

The Table looks like

LIKE_USER_NAME.......NOT_LIKE_USER_NAME.....AKTIV.....REGEL
DWH.................................................................J.........SET OPTIMIZER_INDEX_COST_ADJ=10
DWH.................................................................J.........FORCE PARALLEL DML

Any help is welcome

regards
Uwe



[Updated on: Tue, 08 April 2008 03:55]

Report message to a moderator

Re: Changing a logon trigger does not work [message #312490 is a reply to message #312213] Wed, 09 April 2008 00:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you do a substr(x, 4) like ('XXX%') ???
This is the same as substr(x, 3) = ('XXX')

Why were users 'unable to connect'? Was there an error? Hanging sessions?
Previous Topic: how to run reports from sql (merged)
Next Topic: Compute Sum Compute count
Goto Forum:
  


Current Time: Thu Dec 08 10:29:11 CST 2016

Total time taken to generate the page: 0.05187 seconds