Manual Audit using logon Trigger

This is useful PL/SQL block to enable manual auditing and can customized on so many level.

Condition check can executed on:

• USER matching
• Table Matching
• Host Matching etc.

CREATE OR REPLACE TRIGGER audit_ddl_changes
   AFTER create OR drop OR alter
      ON DILIP.SCHEMA -- Change SCOTT to your schema name!!!
    -- ON DATABASE
BEGIN
  INSERT INTO dll_audit_log VALUES
        (SYSDATE,
         SYS_CONTEXT('USERENV', 'SESSION_USER'),
         SYS_CONTEXT('USERENV', 'OS_USER'),
         SYS_CONTEXT('USERENV', 'HOST'),
         SYS_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME
        );
END;


Create Or Replace Trigger CheckDILIP_Logon
After logon on database
Begin
            if ( user='DILIP') then

            --   execute immediate 'alter session set sql_trace=true';

  ---execute immediate 'alter session set cursor_sharing = FORCE';
            End if;
end;

exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/spreport


CREATE or replace  TRIGGER audit_DILIP
After logon on database
Begin
        if ( user='DILIP') then
INSERT INTO sms_log VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_NAME
);
        end if;
END;

CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog 
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  rownum = 1;  -- Parallel processes will have the same AUDSID's

  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed on PROD DB!');
  END IF;
END;

/



Explore more functionaly of SYS_CONTEXT

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117