Monitor Code Changes


Problem

Monitor Code Changes

If your Oracle database contains sensitive information you might want to know who creates, drops or modified 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!


Solution

Recipe #1 - Oracle Trigger to Monitor Code Changes

First, we need a table where we write all the changes, execute the following query to do that:

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, run the following code to create the 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 in ORACLE_DDL_LOG:

CREATE OR REPLACE VIEW test_view AS SELECT 1 a FROM DUAL

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

Comments




Please sign-in to post a comment