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
Last activity: May 28, 2020, 03:43 PM