-- The actions which can performed are controlled by the ACTION column of the
-- SESSION_CONTROL table and can be summarised as follows:-
--    AUDIT - Set-up session specific auditing
--    NOAUD - Ignore AUDIT rules
--    ALLOW - Filter on session attributes which indicate that a session should be
--            allowed to continue. Although the default action of this trigger is to
--            allow connections this action is required in order to short-circuit (or
--            skip over) any BLOCK actions.
--    BLOCK - Filter on session attributes which indicate that a session should NOT
--            be allowed to continue.
--    LIMIT - Enable Resource Management limits, via FIL_THROTTLED consumer group
--    NOLIM - Ignore NOLIM rules

CREATE OR REPLACE TRIGGER session_control
AFTER LOGON ON DATABASE
DECLARE

   v_count                   integer;
   v_oldgroup                varchar2(30);

   block_session EXCEPTION;
   PRAGMA EXCEPTION_INIT(block_session, -20101);

BEGIN

   -- All of the following checks follow the same pattern where the SYS_CONTEXT function
   -- is used to retrieve attributes for the incoming database connection which is then
   -- checked against a column in the SESSION_CONTROL using the REGEXP_LIKE function.
   --
   -- As an example, the name of the database which is being connected to is retrieved
   -- using the sys_context('USERENV','DB_NAME') function and this is compared to the
   -- DB_NAME_REGEXP column in the SESSION_CONTROL table.


   -- Check for any applicable NOAUD actions
   SELECT count(1)
   INTO v_count
   FROM <SYSAUX>.SESSION_CONTROL
   WHERE action = 'NOAUD'
     AND regexp_like ( NVL(sys_context('USERENV','HOST'),         'UNKNOWN'),    host_regexp,         'i')
     AND regexp_like ( NVL(sys_context('USERENV','OS_USER'),      'UNKNOWN'),    os_user_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','DB_NAME'),      'UNKNOWN'),    db_name_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','SESSION_USER'), 'UNKNOWN'),    session_user_regexp, 'i')
     AND regexp_like ( NVL(sys_context('USERENV','MODULE'),       'UNKNOWN'),    module_regexp,       'i')
     AND regexp_like ( NVL(sys_context('USERENV','SERVICE_NAME'), 'UNKNOWN'),    service_name_regexp, 'i')
   ;

   -- If one or more NOAUD actions exists, skip check for AUDIT records
   IF v_count > 0 then
      <SYSAUX>.SESSION_CONTROL_aud_i_proc('NOAUD');
   ELSE
     -- Check for any applicable AUDIT actions
     SELECT count(1)
     INTO v_count
     FROM <SYSAUX>.SESSION_CONTROL
     WHERE action = 'AUDIT'
       AND regexp_like ( NVL(sys_context('USERENV','HOST'),         'UNKNOWN'),    host_regexp,         'i')
       AND regexp_like ( NVL(sys_context('USERENV','OS_USER'),      'UNKNOWN'),    os_user_regexp,      'i')
       AND regexp_like ( NVL(sys_context('USERENV','DB_NAME'),      'UNKNOWN'),    db_name_regexp,      'i')
       AND regexp_like ( NVL(sys_context('USERENV','SESSION_USER'), 'UNKNOWN'),    session_user_regexp, 'i')
       AND regexp_like ( NVL(sys_context('USERENV','MODULE'),       'UNKNOWN'),    module_regexp,       'i')
       AND regexp_like ( NVL(sys_context('USERENV','SERVICE_NAME'), 'UNKNOWN'),    service_name_regexp, 'i')
     ;

     -- If one or more AUDIT actions exists, audit INSERT, UPDATE, DELETE and EXECUTE SQL within the session
     IF v_count > 0 then
        -- write activity to SESSION_CONTROL_AUDIT table
        <SYSAUX>.SESSION_CONTROL_aud_i_proc('AUDIT');

        -- enable session auditing options
        EXECUTE IMMEDIATE 'audit insert table,update table,delete table,execute procedure in session current by access';
     END IF;
   END IF;

   -- Check for any applicable NOLIM actions
   SELECT count(1)
   INTO v_count
   FROM <SYSAUX>.SESSION_CONTROL
   WHERE action = 'NOLIM'
     AND regexp_like ( NVL(sys_context('USERENV','HOST'),         'UNKNOWN'),    host_regexp,         'i')
     AND regexp_like ( NVL(sys_context('USERENV','OS_USER'),      'UNKNOWN'),    os_user_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','DB_NAME'),      'UNKNOWN'),    db_name_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','SESSION_USER'), 'UNKNOWN'),    session_user_regexp, 'i')
     AND regexp_like ( NVL(sys_context('USERENV','MODULE'),       'UNKNOWN'),    module_regexp,       'i')
     AND regexp_like ( NVL(sys_context('USERENV','SERVICE_NAME'), 'UNKNOWN'),    service_name_regexp, 'i')
   ;

   -- If one or more NOLIM actions exists, skip check for LIMIT records
   IF v_count > 0 then
      <SYSAUX>.SESSION_CONTROL_aud_i_proc('NOLIM');
   ELSE
     -- Check for any applicable LIMIT actions, doesn't apply to users with BreakGlass
     SELECT count(1)
     INTO v_count
     FROM <SYSAUX>.SESSION_CONTROL
     WHERE action = 'LIMIT'
       AND regexp_like ( NVL(sys_context('USERENV','HOST'),         'UNKNOWN'),    host_regexp,         'i')
       AND regexp_like ( NVL(sys_context('USERENV','OS_USER'),      'UNKNOWN'),    os_user_regexp,      'i')
       AND regexp_like ( NVL(sys_context('USERENV','DB_NAME'),      'UNKNOWN'),    db_name_regexp,      'i')
       AND regexp_like ( NVL(sys_context('USERENV','SESSION_USER'), 'UNKNOWN'),    session_user_regexp, 'i')
       AND regexp_like ( NVL(sys_context('USERENV','MODULE'),       'UNKNOWN'),    module_regexp,       'i')
       AND regexp_like ( NVL(sys_context('USERENV','SERVICE_NAME'), 'UNKNOWN'),    service_name_regexp, 'i')
       AND not exists (select 1 from dba_role_privs where grantee = sys_context('USERENV','SESSION_USER') and granted_role like '%BREAKGLASS')
     ;

     -- If one or more LIMIT actions exist, switch to THROTTLED resource consumer group
     IF v_count > 0 then
        -- write activity to SESSION_CONTROL_AUDIT table
        <SYSAUX>.SESSION_CONTROL_aud_i_proc('LIMIT');

        -- enable resource consumer group
        DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('THROTTLED', v_oldgroup, TRUE);
     END IF;
   END IF;

   -- Check for any applicable ALLOW actions - these MUST be checked before the BLOCK actions
   SELECT count(1)
   INTO v_count
   FROM <SYSAUX>.SESSION_CONTROL
   WHERE action = 'ALLOW'
     AND regexp_like ( NVL(sys_context('USERENV','HOST'),         'UNKNOWN'),    host_regexp,         'i')
     AND regexp_like ( NVL(sys_context('USERENV','OS_USER'),      'UNKNOWN'),    os_user_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','DB_NAME'),      'UNKNOWN'),    db_name_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','SESSION_USER'), 'UNKNOWN'),    session_user_regexp, 'i')
     AND regexp_like ( NVL(sys_context('USERENV','MODULE'),       'UNKNOWN'),    module_regexp,       'i')
     AND regexp_like ( NVL(sys_context('USERENV','SERVICE_NAME'), 'UNKNOWN'),    service_name_regexp, 'i')
   ;

   -- If one or more ALLOW actions exists, exit trigger without checking BLOCK actions
   IF v_count > 0 then
      -- no need to write to SESSION_CONTROL_AUDIT - as this 'normal' activity will be in AUD$
      RETURN;
   END IF;


   -- Finally, check for any applicable BLOCK actions
   SELECT count(1)
   INTO v_count
   FROM <SYSAUX>.SESSION_CONTROL
   WHERE action = 'BLOCK'
     AND regexp_like ( NVL(sys_context('USERENV','HOST'),         'UNKNOWN'),    host_regexp,         'i')
     AND regexp_like ( NVL(sys_context('USERENV','OS_USER'),      'UNKNOWN'),    os_user_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','DB_NAME'),      'UNKNOWN'),    db_name_regexp,      'i')
     AND regexp_like ( NVL(sys_context('USERENV','SESSION_USER'), 'UNKNOWN'),    session_user_regexp, 'i')
     AND regexp_like ( NVL(sys_context('USERENV','MODULE'),       'UNKNOWN'),    module_regexp,       'i')
     AND regexp_like ( NVL(sys_context('USERENV','SERVICE_NAME'), 'UNKNOWN'),    service_name_regexp, 'i')
   ;

   -- If one or more BLOCK actions exists, prevent session logon
   IF v_count > 0 then
      -- write activity to SESSION_CONTROL_AUDIT table
      <SYSAUX>.SESSION_CONTROL_aud_i_proc('BLOCK');

      -- raise exception
      RAISE block_session;
   END IF;


   -- If you've got here - there are no further checks to perform so simply allow the database connection
   -- attempt to continue.

EXCEPTION

   WHEN block_session THEN
      -- check if session has 'ADMINISTER DATABASE TRIGGER' privilege
      -- this could be either direct or via a role
      -- the following search isn't comprehensive - but want to avoid lots
      -- of expensive hierarchal lookups which are performed every time
      -- 'block_session' is executed
      SELECT count(1)
      INTO v_count
      FROM (SELECT '1'
            FROM dba_sys_privs
            WHERE privilege = 'ADMINISTER DATABASE TRIGGER'
              AND grantee = sys_context('USERENV','SESSION_USER')
              AND grantee <> 'SYS'
            UNION ALL
            SELECT grantee
            FROM dba_role_privs
            WHERE granted_role in (SELECT grantee
                                   FROM dba_sys_privs
                                   WHERE privilege = 'ADMINISTER DATABASE TRIGGER')
              AND grantee = sys_context('USERENV','SESSION_USER')
              AND grantee <> 'SYS')
      ;

      IF v_count > 0 then
         -- If so, session is either SYS or a DBA - so perform 'nasty' crash of session to block access
         execute immediate 'alter session set events ''immediate crash''';
      ELSE
         -- raise a normal application exception
         RAISE_APPLICATION_ERROR(-20101, 'Database access by this Schema/OS User/Client Machine/Program are not permitted!');
      END IF;
   WHEN OTHERS THEN
      -- If there is an error within the trigger itself, allow connections by default
      NULL;

END;
/

show errors;
