Sample Header Ad - 728x90

Logon Trigger IP Address Issue

0 votes
0 answers
570 views
I have this logon trigger to restrict IP addresses. It is the same trigger I have seen everywhere. However, it is not working as expected, and I was hoping someone could give some direction. I am able to connect to this account / server from SQL*Plus and from a PHP script on a server where the IP is not in the list of IP's.
CREATE OR REPLACE TRIGGER check_ip_addresses_trg
AFTER LOGON
ON DATABASE

BEGIN
  IF USER IN ('JSMITH') THEN
     IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT IN
       ('192.168.170.34',
        '192.168.170.39',
        '192.168.170.57',
        '192.168.170.62') THEN
       RAISE_APPLICATION_ERROR(-20001, 'Can not log in from this IP address (' || SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ')');
     END IF;
 END IF;
But, if I try from a different server: sqlplus jsmith/password@dbserver.dbase.com, I am able to log into SQL*Plus successfully. From this one PHP script I wrote, I also try to connect to this account, and I am able to connect successfully. We are running SE 11g R2. I had expected an error message. Any thoughts?
Asked by Landon Statis (151 rep)
May 28, 2020, 03:36 PM
Last activity: May 28, 2020, 03:43 PM