Monitor Code Changes

If your Oracle database contains sensitive information you might want to know who creates, drops or modifies an object in it. You could set up a process where only one or two persons are allowed to do these kind of changes but at some point this could get rather annoying. But there's a little code which could help you!

Recipe #1 - Oracle Trigger to Monitor Code Changes

First, we need a table where we write all the changes:

CREATE TABLE ORACLE_DDL_LOG (
  SYS_EVENT    VARCHAR2(255),
  OBJECT_OWNER VARCHAR2(255),
  OBJECT_NAME  VARCHAR2(255),
  SQL_TEXT     CLOB,
  USER_NAME    VARCHAR2(255),
  ACTION_DATE  DATE
);

Once you've created the table, you just have to create a trigger:

CREATE OR REPLACE TRIGGER oracle_ddl_trigger
  BEFORE CREATE OR ALTER OR DROP ON SCHEMA
DECLARE
  sql_text  ora_name_list_t;
  i         PLS_INTEGER;
  sql_text_ CLOB;
BEGIN
  i := sql_txt(sql_text);
  FOR l IN 1..i LOOP
    sql_text_ := sql_text_ || sql_text(l);
  END LOOP;
  INSERT INTO oracle_ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name,
           sql_text_, SYS_CONTEXT('USERENV','OS_USER'), SYSDATE
    FROM DUAL;
END oracle_ddl_trigger;

Now that you've created the trigger, you'll get an entry in ORACLE_DDL_LOG when you create a new object. Try to run this test code to see if an entry appears:

CREATE OR REPLACE VIEW test_view AS SELECT 1 a FROM DUAL;

If everything works, you created a nice and simple monitoring system!