How to Kill Session in Logon Trigger

  • by

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.

Leave a Reply

Your email address will not be published. Required fields are marked *