Recording Destructive SQL Trapping "Alter Table Drop Column" So

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Mon, 11 Jun 2001 15:10:50 -0700
Message-ID: <F001.00324B2A.20010611151822@fatcity.com>

Yes, but how does one do that? I was really hoping someone would post an answer since there were other people wanting to be able to the same or something very similar. Failing to get a response. I wrote my own; actually, I took some code provided by Steve Adams for a different purpose and put "instr" and "sessionid" restrictions on it. Be warned the code provided below has not been thoroughly tested.

select
  'x'
from
  sys.x$_kglpn p,
  sys.x$_kglcursor c,
  v$_session s
where
  p.kglpnhdl = c.kglhdadr and
  p.kglpnses = s.saddr and

  instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') > 0 and
  instr(lower(c.kglnaobj),'select') = 0 and
  s.audsid = sys_context('USERENV', 'SESSIONID');

All this does is to make sure "drop column" was part of the statement and "select" is not. The check for the "alter table" is done in the event trigger. The full text of the trigger is

---

CREATE or replace TRIGGER record_destructive_ddl_trigger before drop or alter or truncate ON DATABASE
Declare

   pcolumn_name dba_tab_columns.column_name%type;    cursor get_column_name is

     select column_name from dba_tab_columns where
     owner = ora_dict_obj_owner and table_name = ora_dict_obj_name;

   sql_dummy varchar2(1);
   cursor is_drop_column is
select
  'x'
from

  sys.x$_kglpn  p,             
  sys.x$_kglcursor  c,         

  v$session s
where
  p.kglpnhdl = c.kglhdadr and
  p.kglpnses = s.saddr and
   instr(lower(replace(c.kglnaobj,' ', null)),'select') = 0 and   instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') > 0 and   s.audsid = sys_context('USERENV', 'SESSIONID');

Begin

   if (((ora_sysevent = 'DROP') and (ora_dict_obj_type = 'TABLE'))    or ora_sysevent = 'TRUNCATE') and ora_dict_obj_owner != 'SYS' THEN

        object_security.record_destructive_ddl(null);    elsif ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE'    and ora_dict_obj_owner != 'SYS' THEN

      open is_drop_column;
      fetch is_drop_column into sql_dummy;
      close is_drop_column;
      if sql_dummy = 'x' then
         open get_column_name;
         loop 
            fetch get_column_name into pcolumn_name;
            exit when get_column_name%notfound;
               if ora_is_drop_column(pcolumn_name) then
                   object_security.record_destructive_ddl(pcolumn_name);
               end if;
         end loop;
      end if;
         close get_column_name;

end if;
end;
/

---

object_security is a package I wrote which records the information about the destructive DDL into a table. It uses
the autonomous transaction pragma and many of the event attribute functions described in Chapter 13 of the "Application Developers Guide - Fundamentals" manual.

Create or replace package object_security is

   procedure RECORD_destructive_ddl(cname varchar2); end object_security;
/

create or replace package body object_security is

   procedure RECORD_destructive_ddl(cname varchar2) is

       PRAGMA AUTONOMOUS_TRANSACTION;
   Begin

          INSERT INTO oracle.DESTRUCTIVE_DDL_JOURNAL VALUES
         (ora_login_user, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type,
         cname,ora_sysevent,ora_client_ip_address, sysdate);
         commit;

   end RECORD_destructive_ddl;
end object_security;
/

---

The idea again is to timestamp the destructive DDL. Again, the code has not been thoroughly tested. Also, it needs improvements such as excluding routinely truncated tables.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
Sent: Monday, June 11, 2001 11:26 AM
To: Multiple recipients of list ORACLE-L

You are getting current SQL but you need to go thru all SQL for this session to find out if there was alter table drop (column).

Alex Hillman

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Jun 11 2001 - 17:10:50 CDT