Alert on Schema change 2

[CassidyHunt]

Is there anyway to setup something that sends some type of notification when something changes like triggers disabled, functions/procedures become invalid, etc...

Basically anything that happens that causes something in the database to change state. It would be nice to know who did it, what computer they did it on in the notification. Reason why is I had a user yesterday decide to run a program from a third party vendor that claimed to clean up old data from the database. In doing so it disabled some 300 triggers and brought us to our knees for a bit of time. Program did do what it said it would it just didn't re-enable what it disabled.

Thanks in advance for the help.

Cassidy

 

[SantaMufasa]

Cassidy,

Before I attempt to resolve the wrong question, which is your actual question:[ul][li]How to send a notification via Oracle?, or[/li][li]How to create an Oracle trigger that will audit all DDL against any object in a schema?[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CassidyHunt]

I guess my question is to do both. Need a trigger to audit ddl changes and that triggers needs to notify me.

Thanks

Cassidy

 

[SantaMufasa]

Okay, then following is a script that does the following:[ul][li]Drops the audit trigger (to allow script re-run)[/li][li]Drops the audit table (to allow script re-run)[/li][li]Creates a new copy of the DDL_AUDIT_LOG table[/li][li]Creates a new copy of the DDL_TRIGGER trigger[/li][li]Does some SQL*Plus column formatting[/li][li]For illustration, SELECTs current contents of the DDL_AUDIT_LOG table prior to population[/li][li]Connects as SYSDBA to flush the SHARED_POOL[/li][li]Connects as some other user (I chose TEST, pw TEST.)[/li][li]Drops the NEW_TABLE (to allow script re-run)[/li][li]Creates the NEW_TABLE[/li][li]For illustration, SELECTs current contents of the DDL_AUDIT_LOG table to show the effect of the CREATE TABLE DDL[/li][li]Alters NEW_TABLE[/li][li]For illustration, SELECTs current contents of the DDL_AUDIT_LOG table to show the effect of the ALTER TABLE DDL[/li][li]Drop NEW_TABLE[/li][li]For illustration, SELECTs current contents of the DDL_AUDIT_LOG table to show the effect of the DROP TABLE DDL[/li][/ul]The trigger also audits TRIGGER modifications, as well.

Here is the script (without showing intervening results) so that you can simply copy and paste the code to your own script. (Be sure to change the "conn test/test" to your own username/password.):

drop trigger ddl_trigger;

drop table ddl_audit_log;

CREATE TABLE ddl_audit_log (
operation                      VARCHAR2(30),
obj_owner                      VARCHAR2(30),
object_name                    VARCHAR2(30),
sql_text                       VARCHAR2(64),
who                            VARCHAR2(1000),
mod_date                       DATE); 

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA
DECLARE
 oper ddl_audit_log.operation%TYPE;
 sql_text ora_name_list_t;
 i        PLS_INTEGER;
 who_details ddl_audit_log.who%type;
 function details (user_in varchar2) return varchar2 is
         a varchar2(10);
         cnt number;
         conjunction varchar2(2);
         hold_who ddl_audit_log.who%type;
     begin
         hold_who := 'Oracle user: '||user;
         cnt      := 1;
         for x in (select distinct username,osuser,machine
                     from gv$session
                    where username = user_in) loop
             if cnt > 1 then
                 conjunction := 'or';
             else
                 cnt := cnt + 1;
                 conjunction := '  ';
             end if;
             hold_who := hold_who||chr(10)||' '||conjunction||' OSLogin: '||x.osuser||
                         '. Machine: '||x.machine;
         end loop;
         return hold_who;
     end;
BEGIN
  SELECT ora_sysevent
  INTO oper
  FROM dual;
  i := sql_txt(sql_text);
  who_details := details(user);
  IF oper IN ('CREATE', 'DROP') THEN
    INSERT INTO ddl_audit_log
    SELECT ora_sysevent, ora_dict_obj_owner, 
    ora_dict_obj_name, sql_text(1), who_details, SYSDATE
    FROM dual;
  ELSIF oper = 'ALTER' THEN
    INSERT INTO ddl_audit_log
    SELECT distinct ora_sysevent, ora_dict_obj_owner, 
    ora_dict_obj_name, sql_text(1), who_details, SYSDATE
    FROM sys.gv_$sqltext
    WHERE UPPER(sql_text) LIKE 'ALTER%'
      and upper(sql_text) like '%'||ora_dict_obj_name||'%';
  END IF;
END ddl_trigger;
/

col operation format a10
col obj_owner format a10
col sql_text  format a30
col who format a55
col mod_date format a
col x heading "mod_date" format a20
SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  FROM ddl_audit_log;

conn / as sysdba

alter system flush shared_pool;
alter system flush shared_pool;

conn test/test

drop table new_table;

CREATE TABLE new_table (
charcol VARCHAR(20));

SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  FROM ddl_audit_log;

ALTER TABLE new_table
ADD (numbcol NUMBER(10));

SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  FROM ddl_audit_log;

DROP TABLE new_table;

SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  FROM ddl_audit_log;

Here, also, are results of running the script (as proof of concept):

SQL> drop trigger ddl_trigger;

Trigger dropped.

SQL> 
SQL> drop table ddl_audit_log;

Table dropped.

SQL> 
SQL> CREATE TABLE ddl_audit_log (
  2  operation                      VARCHAR2(30),
  3  obj_owner                      VARCHAR2(30),
  4  object_name                    VARCHAR2(30),
  5  sql_text                       VARCHAR2(64),
  6  who                            VARCHAR2(1000),
  7  mod_date                       DATE); 

Table created.

SQL> 
SQL> CREATE OR REPLACE TRIGGER ddl_trigger
  2  BEFORE CREATE OR ALTER OR DROP
  3  ON SCHEMA
  4  DECLARE
  5   oper ddl_audit_log.operation%TYPE;
  6   sql_text ora_name_list_t;
  7   i        PLS_INTEGER;
  8   who_details ddl_audit_log.who%type;
  9   function details (user_in varchar2) return varchar2 is
 10           a varchar2(10);
 11           cnt number;
 12           conjunction varchar2(2);
 13           hold_who ddl_audit_log.who%type;
 14       begin
 15           hold_who := 'Oracle user: '||user;
 16           cnt      := 1;
 17           for x in (select distinct username,osuser,machine
 18                       from gv$session
 19                      where username = user_in) loop
 20               if cnt > 1 then
 21                   conjunction := 'or';
 22               else
 23                   cnt := cnt + 1;
 24                   conjunction := '  ';
 25               end if;
 26               hold_who := hold_who||chr(10)||' '||conjunction||' OSLogin: '||x.osuser||
 27                           '. Machine: '||x.machine;
 28           end loop;
 29           return hold_who;
 30       end;
 31  BEGIN
 32    SELECT ora_sysevent
 33    INTO oper
 34    FROM dual;
 35    i := sql_txt(sql_text);
 36    who_details := details(user);
 37    IF oper IN ('CREATE', 'DROP') THEN
 38      INSERT INTO ddl_audit_log
 39      SELECT ora_sysevent, ora_dict_obj_owner, 
 40      ora_dict_obj_name, sql_text(1), who_details, SYSDATE
 41      FROM dual;
 42    ELSIF oper = 'ALTER' THEN
 43      INSERT INTO ddl_audit_log
 44      SELECT distinct ora_sysevent, ora_dict_obj_owner, 
 45      ora_dict_obj_name, sql_text(1), who_details, SYSDATE
 46      FROM sys.gv_$sqltext
 47      WHERE UPPER(sql_text) LIKE 'ALTER%'
 48        and upper(sql_text) like '%'||ora_dict_obj_name||'%';
 49    END IF;
 50  END ddl_trigger;
 51  /

Trigger created.

SQL> 
SQL> col operation format a10
SQL> col obj_owner format a10
SQL> col sql_text  format a30
SQL> col who format a55
SQL> col mod_date format a20
SQL> col x heading "mod_date" format a20
SQL> SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  2    FROM ddl_audit_log;

no rows selected

SQL> 
SQL> conn / as sysdba
Connected.
SQL> 
SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> 
SQL> conn test/test
Connected.
SQL> 
SQL> drop table new_table;
drop table new_table
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> CREATE TABLE new_table (
  2  charcol VARCHAR(20));

Table created.

SQL> 
SQL> SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  2    FROM ddl_audit_log;

OPERATION  OBJ_OWNER  SQL_TEXT                       WHO                                                     mod_date
---------- ---------- ------------------------------ ------------------------------------------------------- --------------------
CREATE     TEST       CREATE TABLE new_table (       Oracle user: TEST                                       2009-02-20 11:38:44
                      charcol VARCHAR(20))               OSLogin: WM-HUNTDL\dhunt. Machine: WM-HUNTDL    


1 row selected.

SQL> 
SQL> ALTER TABLE new_table
  2  ADD (numbcol NUMBER(10));

Table altered.

SQL> 
SQL> SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  2    FROM ddl_audit_log;

OPERATION  OBJ_OWNER  SQL_TEXT                       WHO                                                     mod_date
---------- ---------- ------------------------------ ------------------------------------------------------- --------------------
CREATE     TEST       CREATE TABLE new_table (       Oracle user: TEST                                       2009-02-20 11:38:44
                      charcol VARCHAR(20))               OSLogin: WM-HUNTDL\dhunt. Machine: WM-HUNTDL    

ALTER      TEST       ALTER TABLE new_table          Oracle user: TEST                                       2009-02-20 11:38:44
                      ADD (numbcol NUMBER(10))           OSLogin: WM-HUNTDL\dhunt. Machine: WM-HUNTDL    


2 rows selected.

SQL> 
SQL> DROP TABLE new_table;

Table dropped.

SQL> 
SQL> SELECT operation,obj_owner,sql_text,who,to_char(mod_date,'yyyy-mm-dd hh24:mi:ss')x
  2    FROM ddl_audit_log;

OPERATION  OBJ_OWNER  SQL_TEXT                       WHO                                                     mod_date
---------- ---------- ------------------------------ ------------------------------------------------------- --------------------
CREATE     TEST       CREATE TABLE new_table (       Oracle user: TEST                                       2009-02-20 11:38:44
                      charcol VARCHAR(20))               OSLogin: WM-HUNTDL\dhunt. Machine: WM-HUNTDL    

ALTER      TEST       ALTER TABLE new_table          Oracle user: TEST                                       2009-02-20 11:38:44
                      ADD (numbcol NUMBER(10))           OSLogin: WM-HUNTDL\dhunt. Machine: WM-HUNTDL    

DROP       TEST       DROP TABLE new_table           Oracle user: TEST                                       2009-02-20 11:38:44
                                                         OSLogin: WM-HUNTDL\dhunt. Machine: WM-HUNTDL    


3 rows selected.

When I get a bit more time, I'll also post how to trigger the e-mail/other notification, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CassidyHunt]

As always you have the most elegant and exceptional solutions I could ask for.

Thanks

Cassidy

 

[SantaMufasa]

Thanks, Cassidy. You are very generous, yourself.

It would have looked even better had my "Line-Wrap-Prevention Workaround" been allowed to stay in this thread. Hopefully, our Tek-Tips technical guru will have a better workaround available soon.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Cassidy,

I can't help but add my own less useful, but far more effective method, with which even the illustrious Santa will likely agree.

My method is to only allow users to log on to the system with the privileges they need to do their job, and no more. Users never need to alter tables, triggers or anything else in a schema, they just add, delete and/or modify data. If your design allows otherwise then you are in serious trouble.

The lunacy of allowing a third-party tool unrestricted access to a schema beggars belief. Instead of trapping events with (admittedly well-crafted) scripts from Santa, run the third party tool on a copy, see what the outcome is, and only when you are completely satisfied, allow it to run on production.

That way, you just write a one-shot wonder script to tidy up the carnage left by the third-party tool. Don't check for bulls in the china shop, have a bull barrier at the shop door!

What truly scared the pants off me was the fact that a user unilaterally decided to do this, and they had access to such a powerful account that they were able to wreak havoc without reference to anyone. You ought to consider employing a database administrator, usually abbreviated to DBA. Part of their job is to stop such idiocies from ever occurring in the first place.

Although this particular horse bolted so long ago that it's disappeared over the horizon, if you continue to allow unrestriced access, don't be surprised if equine quadrupeds emerge at inconvenient moments, unannounced, and at great velocity from your particular "stable".

Rant off?

Regards

T

 

[SantaMufasa]

Cassidy,

I totally agree with Tharg (as is the typical case). His suggestion is so important, that I'm pasting a

on his post focus people's attention on his advice moreso than my post.

Rather than dutifully resolving your request (as I usually do), without question, I should have offered Tharg's excellent warning before posting any code.

Knowing you as well as I do (away from Tek-Tips), I presumed (perhaps inappropriately) that you wouldn't have given a non-DBA the privileges that the "goofer-upper" had to wreak the havoc that s/he did.

So, Tharg's advice is key to preventing disasters.

You may have heard before, my First Rule of Good Data:

Let bad data live as short a life as possible (i.e., don't even allow bad data to be COMMITted.)

To prevent system chaos, I use the "DELTA" rule:

Don't
Even
Let
Them
Administer

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CassidyHunt]

I am 100% on board with that. Unfortunately the ERP system that we use requires us to run their code periodically and give them full access to the system. That is how this happens. So my only recourse is something like this to know what I have to redo after they are done wrecking things.

Cassidy

 

[Thargy]

Cassidy,

I find that hard to believe. Yes, you may currently be required to allow this to run for business reasons, but it can still be done in a controlled manner.

For starters, you can insist with absolute legitimacy that the vendor must state precisely what changes their system will make to yours, and require that they deomonstrate this by a post-activity report.

Also, after the third party tool has finished, the database must be left in a fully operational state, and that there must be no decompiled and/or invalid items anywhere. Also, no data may be lost as part of their activities. Anything less is incompetence and/or negligence on the part of the vendor.

You are also entitled to insist that it be run on a copy of a production environment and its impact assessed, before it's allowed anywhere near production.

If this thing goes spectacularly wrong, what are their rollback procedures? Note that the onus is on the third party vendor to get this right, not you. They must have a guaranteed rollback method, otherwise you are playing Russion Roulette with a production system.

You should also then run a schema comparison tool against old and new (TOAD and SQL developer both have such facilities) and compare the outcome with the changes the vendors claim to have made. That way you can ensure that they have made the requied changes, and only the required changes, and that these changes are correct.

Allowing unrestricted access to a db by anyone is just plain daft. Also, after the vendor has wrought his havoc, you should change the password of the account used to make the changes to something that only you know. That way, the can't sneak anything in without your knowledge. Only put the password back to something they know, when they've satisfied you that what they're doing is right.

In order to "sell" this to your boss, I suggest you adopt the following approach:-

Boss, after the last upgrade by vendor x, I checked on our system, and found the following things were wrong, xyz was decompiled, abc was invalid etc etc.

Because of the damage done to our system, I had to spend several hours, checking everything out and fixing issues caused by running this tool. This is costing us money, and increases risk of failure in production - see my reference to the abc in my analysis.

I realise that we have to allow these guys access to maintain the system, but surely we ought to be get a fully working system afterwards, not a broken one. Also, there is no change control here, so we have a system which can't be controlled, and therefore can't be managed. I suggest we use the following to make sure all subsequnt changes are fully controlled...

See how that flys with risk-averse managers.

Regards

T