21. November 2011

DROP TABLE .. Sind Sie sicher?

"Are you sure?" for DROP TABLE
UPDATE: Auf einem internen "Produktionsserver" (sofern man davon sprechen kann) habe ich das Package natürlich gleich installiert. Und heute morgen dann gemerkt, dass ein TRUNCATE / DROP auch mal der Normalfall sein kann. Als ich mich im APEX-Workspace einloggen wollte, kam die Fehlermeldung, dass ein DROP / TRUNCATE auf die Tabelle WWV_FLOW_USER_ACCESS_LOG2$ nicht möglich ist. APEX macht das von sich aus: Da hilft es nix: Der Trigger muss erweitert werden! Ich habe den Code (unten) angepasst und eine Ausnahme für APEX eingebaut; es kann durchaus sein, dass noch ein paar hinzukommen. Wenn euch da etwas vor mir auffällt, einfach in den Kommentar schreiben.
Jüngst bin ich nochmals über das Thema Database Event Triggers mit PL/SQL gestolpert - man kann ja (schon seit einiger Zeit) auch Trigger auf Datenbank-Events anlegen - ein klassisches Beispiel wäre das "Verbieten" einer DROP-Operation auf Produktionssystemen - einfach zur Sicherheit. Der Trigger dazu ist einfach und schnell gebaut ...
create or replace trigger tr_drop_is_prohibited
before drop on database
begin
  raise_application_error(-20000, 'DROP IS NOT ALLOWED ON THIS SYSTEM');
end;
/
Einfach, aber effektvoll. Man kann nun auf dieser Datenbank nichts mehr droppen (außer dem Trigger selbst, natürlich). Aber für den praktischen Einsatz etwas zu "grobstollig". Erstens ist ein TRUNCATE Table mittlerweile schlimmer als ein DROP TABLE, welches man mit dem Recycle Bin (FLASHBACK TABLE ... TO BEFORE DROP) rückgängig machen kann, zum anderen muss es ja manchmal doch sein, dass man etwas droppen will ... und dann muss man erst den Trigger deaktivieren und danach wieder aktivieren.
Nach diesem Gedanken kam ich auf die Idee, eine Art "Sind Sie Sicher?"-Mechanismus für DROP- und TRUNCATE-Operationen zu bauen. In der Datenbank funktioniert er nur andersherum als bei Microsoft Windows: Man muss die Drop-Operation zuerst deklarieren und dann ausführen. Auf einem Produktionssystem ist sowas vielleicht doch ganz hilfreich und erspart unter Umständen das eine oder andere versehentlich gedroppte Object. Hier ist nun der Code (muss als SYS eingespielt werden) - wir beginnen mit einem Package, mit dem die DROP-Operation vorher deklariert wird:
create or replace package PREDROP authid current_user is
  g_forsession boolean := false;
  g_nextobject boolean := false;
  g_objectname varchar2(4000) := '';
  g_objecttype varchar2(4000) := '';
  g_objectuser varchar2(4000) := '';

  procedure obj(
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  );
  procedure obj;
  procedure sess(
    p_enable in boolean default true
  );
  procedure clear; 
  procedure info;
end PREDROP;
/
sho err

create or replace package body PREDROP is
  procedure sess (
    p_enable in boolean default true
  ) is 
  begin
     g_forsession := p_enable;
  end sess;

  procedure obj is
  begin
    g_forsession := false;
    g_nextobject := true;

    g_objectname := null;
    g_objectuser := null;
    g_objecttype := null;
  end obj;

  procedure obj (
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  ) is
  begin
    g_forsession := false;
    g_nextobject := true;
    g_objectname := p_objectname;
    g_objecttype := p_objecttype;
    g_objectuser := p_objectuser;
  end obj;

  procedure clear is
  begin
    g_forsession := false;
    g_nextobject := false;
    g_objectname := null;
    g_objecttype := null;
    g_objectuser := null;
  end clear;

  procedure info is
  begin
    dbms_output.put_line('********************************************');
    dbms_output.put_line('* Enable Session Flag: ' ||(case when g_forsession then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Enable Object Flag:  ' ||(case when g_nextobject then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Object name:         ' ||g_objectname);
    dbms_output.put_line('* Object type:         ' ||g_objecttype);
    dbms_output.put_line('* Object owner:        ' ||g_objectuser);
    dbms_output.put_line('********************************************');
    dbms_output.put_line('');
  end info;
end PREDROP;
/
sho err

grant execute on PREDROP to public
/

create public synonym PREDROP for sys.PREDROP
/
Execute-Privileg und Public Synonym nicht vergessen und dann geht es auch schon weiter. Als nächstes kommt der Database Event Trigger, der ins Package hineinguckt, ob das Objekt, was da gerade bearbeitet werden soll, vorher deklariert wurde - wenn ja, geht die Operation durch, wenn nicht, wird ein Fehler ausgelöst ...
create or replace trigger tr_drop_protection 
before drop or truncate on database
declare
  l_raiseerror     boolean := true;

  l_namematch      boolean := false;
  l_typematch      boolean := false;
  l_usermatch      boolean := false;
  l_match          boolean := false;
  l_always_allowed boolean := false;

  /* UPDATE: 
   * Some code need to be allowed in general!
   */ 
  function always_allowed(p_owner in varchar2, p_object in varchar2) return boolean is
    l_always boolean := false;
  begin
    if p_owner like 'APEX_%' and p_object like 'WWV_FLOW_%LOG%' then
      l_always := true;
      goto ende;
    end if;
    <<ende>>
    return l_always;
  end always_allowed;

  function match(p_string1 in varchar2, p_string2 in varchar2) return boolean is
    l_match boolean := false;
  begin
    if p_string1 = p_string2 or p_string2 is null then 
      l_match := true;
    else 
      l_match := false;
    end if;
    return l_match;
  end match;
begin
    l_namematch := match(ora_dict_obj_name, PREDROP.g_objectname);
    l_typematch := match(ora_dict_obj_type, PREDROP.g_objecttype);
    l_usermatch := match(ora_dict_obj_owner, PREDROP.g_objectuser);
    l_match := l_namematch and l_usermatch and l_typematch;
    l_always_allowed := always_allowed(ora_dict_obj_owner, ora_dict_obj_name);

    if ((PREDROP.g_nextobject or PREDROP.g_forsession) and l_match) or l_always_allowed  then
      l_raiseerror := false;
    else
      l_raiseerror := true;
    end if;

    if not PREDROP.g_forsession and not l_raiseerror then 
      PREDROP.clear;
    end if;

    if l_raiseerror then
      raise_application_error(-20000, 'Enable DROP/TRUNCATE of '||ora_dict_obj_type|| ' "'||ora_dict_obj_name||'" with the '||upper('PREDROP')||' package.');
    end if;
end;
/
Und das war's auch schon. Von jetzt an funktioniert DROP und TRUNCATE etwas anders ...
SQL> drop table emp3;
drop table emp3
*
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-20000: Enable DROP/TRUNCATE of TABLE "EMP3" with the PREDROP package.
ORA-06512: in Zeile 36

SQL> exec predrop.obj('EMP3');

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> drop table emp3;

Tabelle wurde gelöscht.

SQL>
Für Truncate gilt das gleiche. Lässt man den Objektnamen auf SQL NULL, gilt die Freigabe für die nächste Tabelle, egal, wie sie heißt. Setzt man auch den Object Type auf SQL NULL, gilt die Freigabe für alle Objekte im Schema. Und der Parameter P_OBJECTUSER gilt analog. Ruft man dann noch die Prozedur PREDROP.SESS auf, gilt die Freigabe nicht nur für das nächste Objekt, sondern für die ganze Session. Auch auf Produktionsungebungen läuft ja mal ein Skript mit mehreren gewollten DROP-Operationen - solche Vorgänge sollten nicht behindert werden; das Package soll helfen und nicht im Weg sehen. Man könnte das jetzt natürlich auch noch mit regulären Ausdrücken erweitern und so die völlige Flexibilität bekommen - aber das machen wir ein anderes Mal ...
Was denkt Ihr ...?
UPDATE: Just yesterday I installed this package on my own "production" instance - and as I wanted to login into APEX today I encountered my own message Cannot DROP/TRUNCATE table "WWV_FLOW_USER_ACCESS_LOG2$" - so APEX does some regular DROP / TRUNCATE operations which must not be trapped by the Trigger - So I extended the trigger code a bit (further extensions might be necessary in the future) - just have a look into the new trigger code. If you also encounter DROP or TRUNCATE operations which need to ge generally allowed in the trigger, feel free to post me a message or a comment.
Recently I again encountered the database event triggers in PL/SQL - using these you can place a trigger not only on table or view DML, but also on DDL commands or database events like STARTUP, SHUTDOWN oder LOGON. One thirst thought would be prohibiting the DROP operation on a production instance - the trigger code for this is rather simple ...
create or replace trigger tr_drop_is_prohibited
before drop on database
begin
  raise_application_error(-20000, 'DROP IS NOT ALLOWED ON THIS SYSTEM');
end;
/
Simple and powerful. On this instance no object can be dropped any more (except the trigger itself, of course). But this can also lead to a lot of problems, of course: Even on production systems, sometimes there must be dropped something. Then the trigger needs to be disabled before and enabled after the operation. And then there might be objects which are frequently dropped and re-created and other objects which not. And what about the TRUNCATE operation. This one cannot be undone with FLASHBACK TABLE TO BEFORE DROP - so accidentially use of TRUNCATE is much more dangerous than the DROP operation ...
So my next thought was to try a bit more sophisticated approach - I'd like to see the "Are you sure?" feature which we all know from Windows also in the database. And although we cannot catch the DROP operation to ask the "are you sure" question, we can implement a similar approach: The idea is to declare the object to be dropped beforehand. So we have a package which holds the declared object and a trigger which looks into the package and raises an error message if the object has not been declared. First we create the package.
create or replace package PREDROP authid current_user is
  g_forsession boolean := false;
  g_nextobject boolean := false;
  g_objectname varchar2(4000) := '';
  g_objecttype varchar2(4000) := '';
  g_objectuser varchar2(4000) := '';

  procedure obj(
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  );
  procedure obj;
  procedure sess(
    p_enable in boolean default true
  );
  procedure clear; 
  procedure info;
end PREDROP;
/
sho err

create or replace package body PREDROP is
  procedure sess (
    p_enable in boolean default true
  ) is 
  begin
     g_forsession := p_enable;
  end sess;

  procedure obj is
  begin
    g_forsession := false;
    g_nextobject := true;

    g_objectname := null;
    g_objectuser := null;
    g_objecttype := null;
  end obj;

  procedure obj (
    p_objectname in varchar2,
    p_objectuser in varchar2 default user,
    p_objecttype in varchar2 default 'TABLE'
  ) is
  begin
    g_forsession := false;
    g_nextobject := true;
    g_objectname := p_objectname;
    g_objecttype := p_objecttype;
    g_objectuser := p_objectuser;
  end obj;

  procedure clear is
  begin
    g_forsession := false;
    g_nextobject := false;
    g_objectname := null;
    g_objecttype := null;
    g_objectuser := null;
  end clear;

  procedure info is
  begin
    dbms_output.put_line('********************************************');
    dbms_output.put_line('* Enable Session Flag: ' ||(case when g_forsession then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Enable Object Flag:  ' ||(case when g_nextobject then 'TRUE' else 'FALSE' end));
    dbms_output.put_line('* Object name:         ' ||g_objectname);
    dbms_output.put_line('* Object type:         ' ||g_objecttype);
    dbms_output.put_line('* Object owner:        ' ||g_objectuser);
    dbms_output.put_line('********************************************');
    dbms_output.put_line('');
  end info;
end PREDROP;
/
sho err

grant execute on PREDROP to public
/

create public synonym PREDROP for sys.PREDROP
/
Don't forget to grant EXECUTE Privileges and to create a public synonym in order to make the package accessible for everyone. Creating the Trigger is the next step. As said before, it simply checks whether the object to be dropped ( ora_dict_obj_name, ora_dict_obj_type, ora_dict_obj_owner) has been declared with the PRECODE package. If not, the trigger raises an error, otherwise it does nothing and the operation will succeed.
create or replace trigger tr_drop_protection 
before drop or truncate on database
declare
  l_raiseerror     boolean := true;

  l_namematch      boolean := false;
  l_typematch      boolean := false;
  l_usermatch      boolean := false;
  l_match          boolean := false;
  l_always_allowed boolean := false;

  /* UPDATE: 
   * Some code need to be allowed in general!
   */
  function always_allowed(p_owner in varchar2, p_object in varchar2) return boolean is
    l_always boolean := false;
  begin
    if p_owner like 'APEX_%' and p_object like 'WWV_FLOW_%LOG%' then
      l_always := true;
      goto ende;
    end if;
    <<ende>>
    return l_always;
  end always_allowed;

  function match(p_string1 in varchar2, p_string2 in varchar2) return boolean is
    l_match boolean := false;
  begin
    if p_string1 = p_string2 or p_string2 is null then 
      l_match := true;
    else 
      l_match := false;
    end if;
    return l_match;
  end match;
begin
    l_namematch := match(ora_dict_obj_name, PREDROP.g_objectname);
    l_typematch := match(ora_dict_obj_type, PREDROP.g_objecttype);
    l_usermatch := match(ora_dict_obj_owner, PREDROP.g_objectuser);
    l_match := l_namematch and l_usermatch and l_typematch;
    l_always_allowed := always_allowed(ora_dict_obj_owner, ora_dict_obj_name);

    if ((PREDROP.g_nextobject or PREDROP.g_forsession) and l_match) or l_always_allowed  then
      l_raiseerror := false;
    else
      l_raiseerror := true;
    end if;

    if not PREDROP.g_forsession and not l_raiseerror then 
      PREDROP.clear;
    end if;

    if l_raiseerror then
      raise_application_error(-20000, 'Enable DROP/TRUNCATE of '||ora_dict_obj_type|| ' "'||ora_dict_obj_name||'" with the '||upper('PREDROP')||' package.');
    end if;
end;
/
That's it. From now on, DROP and TRUNCATE operations need to be declared beforehand ...
SQL> drop table emp3;
drop table emp3
*
ERROR in line 1:
ORA-00604: Error at recursive SQL level 1
ORA-20000: Enable DROP/TRUNCATE of TABLE "EMP3" with the PREDROP package.
ORA-06512: line 36

SQL> exec predrop.obj('EMP3');

PL/SQL-Procedure successfully completed.

SQL> drop table emp3;

Table dropped.

SQL>
This implementation of the PREDROP package already provides some flexibility. Setting the object name, type or owner to SQL NULL matches everything. Normally a declaration is valid until it has been matched by a DROP operation - after calling PREDROP.SESS it will be valid until the end of the session - this is useful when runnung SQL scripts. Using regular expressions or LIKE syntax would make the package even more flexible ... but this is a story for another blog posting ...
Tell me what you think!

1 Kommentar:

Tobias Arnhold hat gesagt…

Hi Carsten,

das ist echt ne Klasse Idee. Danke dafür.

Beliebte Postings