Logon triggers

I've wanted to do this for a long time, but now a migration project to both a new data centre, and a new version of Oracle means I have opportunity to do it and hopefully, do it right.

The current database design is not perfect.  There is one application user which is used by apps, users, support and development teams.  At the moment, there is no way to separate traffic, prevent connections from people to the wrong place or from unexpected places, or to do resource limiting.  I can't do any sensible auditing.

This changes as of the migration to 12c.  I have defined 7 different services, which will all have different sets of expected users.  After logon triggers will be set up so that all connections to the controlled services have the connection source IP and database username checked.  If either isn't appropriate for the service, the connection attempt is logged and the connection terminated.

CREATE OR REPLACE TRIGGER SYSTEM.check_service_appropriate AFTER LOGON ON DATABASE
  DECLARE
    v_servname  VARCHAR (100);
    v_ipaddress VARCHAR(15);
    v_hostname  VARCHAR(4000);
    v_osuser    VARCHAR(25);
    v_sessionid NUMBER;
    v_errortext VARCHAR(4000);
    NOACCESS    EXCEPTION;
  BEGIN
    SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') INTO v_servname FROM dual;
    SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO v_ipaddress FROM dual;
    SELECT SYS_CONTEXT('USERENV', 'HOST') INTO v_hostname FROM dual;
    SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO v_osuser FROM dual;
    SELECT SYS_CONTEXT('USERENV', 'UNIFIED_AUDIT_SESSIONID') INTO v_sessionid FROM dual;
    
    CASE v_servname
    WHEN 'reporting.xxxx.national' THEN
      IF ((USER NOT IN ('READONLY','XXX_REP','YYY_RO')) OR (v_hostname NOT LIKE 'ip-10-%')) THEN
        RAISE NOACCESS;
      END IF;
    WHEN 'datamanagement.xxxx.national' THEN
      IF ((USER NOT IN ('XXX_REP')) OR (v_hostname NOT LIKE 'ip-10%')) THEN
        RAISE NOACCESS;
      END IF;
    WHEN 'admin.xxxx.national' THEN
      IF ((USER IN ('READONLY','XXX_REP','YYY_RO','C##F5MONITOR')) OR (v_ipaddress NOT LIKE '10.%')) THEN
        RAISE NOACCESS;
      END IF;
    END CASE;

  EXCEPTION
    WHEN NOACCESS THEN
      INSERT
      INTO SYSTEM.audit_rejected_logins
        (
          time#
        , service
        , ipaddr
        , hostname
        , osuser
        , sessionid
        )
        VALUES
        (
          sysdate
        , v_servname
        , v_ipaddress
        , v_hostname
        , v_osuser
        , to_char(v_sessionid)
        );
    COMMIT;
    v_errortext := 'Attempt to log in using inappropriate DB service, from unexpected IP address or using wrong username (' || USER || ')';
    v_errortext := v_errortext || ' to service (' || v_servname || ')';
    v_errortext := v_errortext || ' from host (' || v_hostname || '). ';
    v_errortext := v_errortext || ' OS user (' || v_osuser || ')';
    RAISE_APPLICATION_ERROR(-20000, v_errortext);
  END;

Using these services, I will also be able to do sensible auditing, using unified auditing putting a condition on so that all DML when using the "admin" service is audited, whereas only specific DML using the application services is recorded.

Services are simple to set up, but their potential is surprisingly flexible.