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