CORRECTION To: Destructive SQL Trapping "Alter Table Drop Column"

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Tue, 12 Jun 2001 14:37:04 -0700
Message-ID: <F001.00326889.20010612143255@fatcity.com>

In the trigger I posted I closed the "get_column_name" cursor at the wrong spot. It worked beautifully for "drops", but
"adds" complained

Here is the corrected code

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)),'dropcolumn') > 0 and
  instr(lower(c.kglnaobj),'select') = 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;
         close get_column_name;
      end if;

end if;
end;
/

Warning I'm still testing this.

As far as wanting to know whether drop column was issued, I don't want to loop through all the columns in the table
when a constraint is created, or a column is added, or a column is widened etc. Yes, my thinking is that its better to check the SQL rather than calling the function needlessly. But I have not done any performance testing.

I cannot think of any DDL which is issued against the sys owned tables after database create time, nor a reason for there to be. At one time tables needed to support certain database options were owned by sys, but I don't think that's true anymore; I haven't installed every option. If these premises are true, it is better to stop any DDL against a sys table than to record it, and that's a different trigger. I will probably remove the exclusion on sys tables from the present trigger to see if any DDL against them is trapped before writing a trigger which prevents such DDL.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

Original Message-----
[mailto:Chaim.Katz_at_Completions.Bombardier.com] Sent: Tuesday, June 12, 2001 1:31 PM
To: Multiple recipients of list ORACLE-L Column"

Thanks for posting this, I have a few questions. Why you need to look at the SQL statement at all. You already know that the statement is ALTER TABLE, so why not just check for dropped columns.

     if ora_dict_obj_type = 'TABLE' THEN
          if ora_dict_obj_type = 'ALTER' THEN
               for x in get_column_name loop
                   if ora_is_drop_column (x.column_name) then
                         object_security.record_destructive_ddl (x.column_name);
                   end if;
               end loop;
          else
          ...

Is it a performance thing? Is it faster to retrieve the SQL all the time and only check for dropped columns when you have to? And Is that also the reason for excluding the SYS schema?

chaim

"MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU> on 06/11/2001 07:18:21 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Chaim Katz/Completions/Bombardier)

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;
/

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Chaim.Katz_at_Completions.Bombardier.com

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).
--

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
--------------------------------------------------------------------