Limiting Oracle connection based on the user's IP and other information

This post is also available in: Português

When a user's password expires in one of the databases that I manage, some users complain that it is an application user and the password should never expires. Thus, if the schema really should be used only by the application, I've created a process that will control this access based on the user session information, which can be one or more of the following:

• User hostname
• OS username
• OS Terminal
• User Application Module
• User IP Address

In this article I will show the operation of this script. Let's start:

You must create the following objects:

1. Application user Profile
2. Control Table
3. Access Trigger

First, I create a profile that the password does not expire and whose users belonging to it will be controlled by the login trigger:

CREATE PROFILE "DEFAULT_APP_USER"
    LIMIT 
         COMPOSITE_LIMIT DEFAULT 
         SESSIONS_PER_USER DEFAULT 
         CPU_PER_SESSION DEFAULT 
         CPU_PER_CALL DEFAULT 
         LOGICAL_READS_PER_SESSION DEFAULT 
         LOGICAL_READS_PER_CALL DEFAULT 
         IDLE_TIME DEFAULT 
         CONNECT_TIME DEFAULT 
         PRIVATE_SGA DEFAULT 
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME UNLIMITED
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION DEFAULT
         PASSWORD_LOCK_TIME 30/1440
         PASSWORD_GRACE_TIME UNLIMITED;

PS: Note that in this PROFILE, most of the parameters are inherited from the DEFAULT one except that the password never expires and the login error attempts, which are 10 tries to every 30 minutes.

Next, we must create the table (and index) where you will control the access:

CREATE TABLE TB_FILTER_LOGON
	(
	SESSION_USER VARCHAR2(50) NOT NULL,
	HOST VARCHAR2(50),
	OS_USER VARCHAR2(50),
	TERMINAL VARCHAR2(50),
	MODULE VARCHAR2(50),
	IP_ADDRESS VARCHAR2(50)
	)
TABLESPACE USERS;

CREATE INDEX I1_FILTER_LOGON ON TB_FILTER_LOGON(SESSION_USER) TABLESPACE USERS;

In this table, the only mandatory field is the "SESSION_USER", which is the user who is logging in the database. Other fields can be filled or not according to the restrictions you want to do.

As you can notice, the "SESSION_USER" column is not a PK, which allows a combination of different filters to be applied.

Finally, create a trigger that will be used to control the access:

CREATE OR REPLACE TRIGGER TRG_FILTER_LOGON
  AFTER LOGON ON DATABASE
DECLARE
  V_GLOBAL_NAME VARCHAR2(50);
  V_PROFILE     VARCHAR2(50);
  V_CHECK       NUMBER;
  ---
  V_HOST         VARCHAR2(50);
  V_OS_USER      VARCHAR2(50);
  V_TERMINAL     VARCHAR2(50);
  V_MODULE       VARCHAR2(50);
  V_IP_ADDRESS   VARCHAR2(50);
  V_SESSION_USER VARCHAR2(50);
BEGIN
  -- Created by DBA RJ - www.dbarj.com.br - 2015/01
  -- Get Connection Info
  V_HOST         := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'HOST'))), 1, 50);
  V_OS_USER      := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'OS_USER'))), 1, 50);
  V_TERMINAL     := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'TERMINAL'))), 1, 50);
  V_MODULE       := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'MODULE'))), 1, 50);
  V_IP_ADDRESS   := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'IP_ADDRESS'))), 1, 50);
  V_SESSION_USER := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))), 1, 50);

  -- Get Database Name
  SELECT GLOBAL_NAME
  INTO   V_GLOBAL_NAME
  FROM   GLOBAL_NAME;

  -- Get Profile Name
  SELECT PROFILE
  INTO   V_PROFILE
  FROM   DBA_USERS
  WHERE  USERNAME = V_SESSION_USER;

  IF V_PROFILE = 'DEFAULT_APP_USER'
  THEN
    -- Check
    CASE V_GLOBAL_NAME
      WHEN 'DBFAIRUS' THEN
      
        SELECT TOTAL1 + TOTAL2
        INTO   V_CHECK
        FROM   (SELECT COUNT(*) TOTAL1
                FROM   TB_FILTER_LOGON
                WHERE  TRIM(UPPER(SESSION_USER)) = V_SESSION_USER
                AND    NVL(TRIM(UPPER(HOST)), NVL(V_HOST, 1)) = NVL(V_HOST, 1)
                AND    NVL(TRIM(UPPER(OS_USER)), NVL(V_OS_USER, 1)) = NVL(V_OS_USER, 1)
                AND    NVL(TRIM(UPPER(TERMINAL)), NVL(V_TERMINAL, 1)) = NVL(V_TERMINAL, 1)
                AND    NVL(TRIM(UPPER(MODULE)), NVL(V_MODULE, 1)) = NVL(V_MODULE, 1)
                AND    NVL(TRIM(UPPER(IP_ADDRESS)), NVL(V_IP_ADDRESS, 1)) = NVL(V_IP_ADDRESS, 1)),
               (SELECT DECODE(COUNT(*), 0, 1, 0) TOTAL2 -- If the entry does not exist, will return 1 to unlock the user
                FROM   TB_FILTER_LOGON
                WHERE  TRIM(UPPER(SESSION_USER)) = V_SESSION_USER);
      
        IF (V_CHECK <> 0)
        THEN
          NULL;
          --OK
        ELSE
          --NULL;
          RAISE_APPLICATION_ERROR(-20000, 'YOU ARE NOT AUTHORIZED TO LOGIN WITH THIS USERNAME. PLEASE CONTACT YOUR DBA.');
        END IF;
      ELSE
        NULL;
    END CASE; END IF;
EXCEPTION
  -- Avoid problems when fails to get GLOBAL_NAME or PROFILE (Data Guard)
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

Sample scenarios:

1) The SCOTT user can connect only:

• From IP 10.192.12.43 with OS user "scripts" OR
• From IP 10.192.12.40 with OS user "root"

SQL> select t.* from tb_filter_logon t

    SESSION_USER    HOST       OS_USER        TERMINAL    MODULE    IP_ADDRESS
1   SCOTT                      scripts                              10.192.12.43
2   SCOTT                      root                                 10.192.12.40

2) The EXPUSER user can connect only:

• From the server whose hostname is "syslogfubddsne001" and using "SQL Developer" OR
• From IP 10.192.12.40 using JDBC and with OS user "javacode"

SQL> select t.* from tb_filter_logon t

    SESSION_USER    HOST               OS_USER   TERMINAL  MODULE            IP_ADDRESS  
1   EXPUSER         syslogfubddsne001                      SQL Developer              
2   EXPUSER                            javacode            JDBC Thin Client  10.192.12.40

PS: If there is no row for some (SESSION_USER) in the table, the user will not be blocked.

Finally, remember: if the user has the "Administer Database Trigger" GRANT, directly or indirectly (as having the DBA role), it will never be blocked by login triggers!

+15