Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article we will be explaining how to do IP Filtering for Application users.
If we want application users to come from a single IP, the following process is applied.
1. Login to DB with SYS user.
2. APP_USER_IP_FILTER_TRG Trigger opens.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE OR REPLACE TRIGGER SYS.APP_USER_IP_FILTER_TRG AFTER LOGON ON DATABASE BEGIN IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('MAXIMO') AND SYS_CONTEXT ('USERENV', 'IP_ADDRESS') NOT IN ('172.24.67.91') THEN raise_application_error ( -20003, 'Application users cannot connect except to application servers.'); END IF; END; / |
3. We add the user and IP information to be filtered.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE OR REPLACE TRIGGER SYS.APP_USER_IP_FILTER_TRG AFTER LOGON ON DATABASE BEGIN IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('MAXIMO') AND SYS_CONTEXT ('USERENV', 'IP_ADDRESS') NOT IN ('172.24.67.91') THEN raise_application_error ( -20003, 'Application users cannot connect except to application servers.'); ELSIF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('KYENIAY') AND SYS_CONTEXT ('USERENV', 'IP_ADDRESS') NOT IN ('172.24.82.141') THEN raise_application_error ( -20003, 'Application users cannot connect except to application servers.'); END IF; END; / |
