Oraclue

Oracle internals, debugging and undocumented features

DDL Audit trigger that sends e-mail

Found this code on google and modified it to send e-mail when DDL change on schema of interest.You can alter it to fit your needs..

First create two tables that will store DDL information:events and sqltext:
DROP TABLE ddl_events;
CREATE TABLE ddl_events
( eventId          NUMBER(10,0),
  eventDate        DATE,
  oraLoginUser     VARCHAR2(30),
  oraDictObjName   VARCHAR2(30),
  oraDictObjOwner  VARCHAR2(30),
  oraDictObjType   VARCHAR2(30),
  oraSysEvent      VARCHAR2(30),
  machine          VARCHAR2(64),
  program          VARCHAR2(64),
  osuser           VARCHAR2(30),
  ip_address       VARCHAR2(20));
 
DROP TABLE ddl_events_sql;
CREATE TABLE ddl_events_sql
( eventId          NUMBER(10,0),
  sqlLine          NUMBER(10,0),
  sqlText          VARCHAR2(4000) );
 
Sequence to support events id’s:
DROP SEQUENCE dsq_ddlEvents;
CREATE SEQUENCE dsq_ddlEvents START WITH 1000;
and here is trigger code:

CREATE OR REPLACE TRIGGER dtr_ddlEvents
AFTER DDL ON DATABASE
DECLARE
 
  l_sqlText    ORA_NAME_LIST_T;
 
BEGIN
  IF ORA_DICT_OBJ_OWNER in (‘SCHEMA_NAME’)
  THEN
BEGIN
  utl_mail.send (
  sender => ‘senders_email’,
  recipients => ‘recipient_email’,
  subject => ‘DDL change  has been made in ‘||ORA_DATABASE_NAME||’ database.’,
  message => ‘User ‘||ORA_LOGIN_USER||’ had run ‘||ORA_SYSEVENT|| ‘ on ‘||ORA_DICT_OBJ_TYPE||’ ‘||ORA_DICT_OBJ_OWNER||’.’||ORA_DICT_OBJ_NAME||’ in ‘||ORA_DATABASE_NAME||’ database.’
  );
  END;
  INSERT INTO ddl_events
  ( SELECT dsq_ddlEvents.NEXTVAL,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’)
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT(‘USERENV’,’SESSIONID’ ) = audsid(+) );
 
   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( dsq_ddlEvents.CURRVAL, l, l_sqlText(l) );
  END LOOP;
 END IF;
END;
/

Keep in mind purging recyclebin with objects owned by schema will also send e-mail so you might need to exclude BIN objects.

Advertisements

4 responses to “DDL Audit trigger that sends e-mail

  1. Jon March 17, 2009 at 12:55 am

    Personally, i’d offline the sending of the email and have a job which ran periodically and did the email sending as to not hold up any DDL (or indeed have the DDL fail?) because of the mail.

  2. oraclue March 17, 2009 at 3:31 am

    There are many different ways to do it.I needed something quickly ( I have found this code on google and modified it ) that will alert me immediatelly.I have some other tools in place also but they are always behind at least few min…

  3. Jon March 17, 2009 at 3:34 am

    i wasn’t having a pop! good code though, i may well take a copy and bodge it to meet my own needs

  4. БaкинcкийPoмaн April 24, 2009 at 6:11 pm

    В очередной раз спасибо, хорошие что есть такие люди как вы, которые дают дейстивтельно дельные советы:)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: