Here I introduce a trigger which can log logon information on specific schemas. It will kill their sessions if they are not from specific subnet.
Create a table for storing logon records
CREATE TABLE LOGON_EXCEPTION_RECORD
(
USERNAME VARCHAR2 (30 BYTE),
LOGON_TIME DATE,
IP_ADDRESS VARCHAR2 (15 BYTE)
);Create a trigger for inspecting logon behaviours
CREATE OR REPLACE TRIGGER LOGON_EXCPETION_CHECK
AFTER LOGON
ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF USER IN ('HR', 'SH', 'OE')
THEN
INSERT INTO LOGON_EXCEPTION_RECORD VALUES (USER, SYSDATE, TRIM(SYS_CONTEXT('USERENV', 'IP_ADDRESS')));
COMMIT;
IF TRIM(SYS_CONTEXT('USERENV', 'IP_ADDRESS')) NOT LIKE '10.10.10.%'
THEN
RAISE_APPLICATION_ERROR(-20101, USER || ', you can not login from ' || SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END IF;
END IF;
END;
/This trigger will be fired after any users' logon. Possibly, a customized application error will be raised so as to stop user's session if the user is not from allowable subnet, i.e. 10.10.10.0/24. We don't have to execute immediate 'ALTER SYSTEM KILL SESSION ...'. Instead, we do it in a better way by raising an unhandled exception to force sessions killed.
This is a very simple trigger that you can use it for logon checking and handling. You can add or drop more filters in if blocks to focus on your target user. Some filters like USER, DB_NAME and IP_ADDRESS come from user's context. For more SYS_CONTEXT context information, you can check Oracle Database / Release 12.2 / SQL Language Reference - SYS_CONTEXT.
Note: This trigger does not affact users who have DBA system privilege. That's a drawback.