作者：佚名 文章來(lái)源：不詳 點(diǎn)擊數： 1011 更新時(shí)間：2005-6-19
如何使用觸發(fā)器實(shí)現數據庫級守護,防止DDL操作
--對于重要對象,實(shí)施DDL拒絕,防止create,drop,truncate,alter等重要操作
Last Updated: Sunday, 2004-10-31 12:06 Eygle
不管是有意還是無(wú)意的,你可能會(huì )遇到數據庫中重要的數據表等對象被drop掉的情況,這可能會(huì )給我們帶來(lái)巨大的損失.
通過(guò)觸發(fā)器，我們可以實(shí)現對于表等對象的數據庫級守護，禁止用戶(hù)drop操作.
以下是一個(gè)簡(jiǎn)單的范例，供參考:
REM this script can be used to monitor a objectREM deny any drop operation on it.CREATE OR REPLACE TRIGGER trg_dropdeny BEFORE DROP ON DATABASEBEGIN IF LOWER (ora_dict_obj_name ()) = ‘test‘ THEN raise_application_error (num => -20000, msg => ‘你瘋了,想刪除表 ‘ || ora_dict_obj_name () || ‘ ?!!!!!‘ || ‘你完了，警察已在途中.....‘ ); END IF;END;/
測試效果:
SQL> connect scott/tigerConnected.SQL> create table test as select * from dba_users;Table created.SQL> connect / as sysdbaConnected.SQL> create or replace trigger trg_dropdeny 2 before drop on database 3 begin 4 if lower(ora_dict_obj_name()) = ‘test‘ 5 then 6 raise_application_error( 7 num => -20000, 8 msg => ‘你瘋了,想刪除表 ‘ || ora_dict_obj_name() || ‘ ?!!!!!‘ ||‘你完了，警察已在途中.....‘); 9 end if; 10 end; 11 /Trigger created.SQL> connect scott/tigerConnected.SQL> drop table test;drop table test*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-20000: 你瘋了,想刪除表 TEST ?!!!!!你完了，警察已在途中.....ORA-06512: at line 4
Oracle從Oracle8i開(kāi)始,允許實(shí)施DDL事件trigger，可是實(shí)現對于DDL的監視及控制,以下是一個(gè)進(jìn)一步的例子:
create or replace trigger ddl_denybefore create or alter or drop or truncate on databasedeclare l_errmsg varchar2(100):= ‘You have no permission to this operation‘;begin if ora_sysevent = ‘CREATE‘ then raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg); elsif ora_sysevent = ‘ALTER‘ then raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg); elsif ora_sysevent = ‘DROP‘ then raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg); elsif ora_sysevent = ‘TRUNCATE‘ then raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg); end if;exception when no_data_found then null;end;/
我們看一下效果:
[oracle@jumper tools]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 31 11:38:25 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> set echo on
SQL> @ddlt
SQL> create or replace trigger ddl_deny
2 before create or alter or drop or truncate on database
3 declare
4 l_errmsg varchar2(100):= ‘You have no permission to this operation‘;
5 begin
6 if ora_sysevent = ‘CREATE‘ then
7 raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg);
8 elsif ora_sysevent = ‘ALTER‘ then
9 raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg);
10 elsif ora_sysevent = ‘DROP‘ then
11 raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg);
12 elsif ora_sysevent = ‘TRUNCATE‘ then
13 raise_application_error(-20001, ora_dict_obj_owner || ‘.‘ || ora_dict_obj_name || ‘ ‘ || l_errmsg);
14 end if;
15
16 exception
17 when no_data_found then
18 null;
19 end;
20 /
Trigger created.
SQL>
SQL>
SQL> connect scott/tiger
Connected.
SQL> create table t as select * from test;
create table t as select * from test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.T You have no permission to this operation
ORA-06512: at line 5
SQL> alter table test add (id number);
alter table test add (id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 7
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 9
SQL> truncate table test;
truncate table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SCOTT.TEST You have no permission to this operation
ORA-06512: at line 11
我們可以看到,ddl語(yǔ)句都被禁止了,如果你不是禁止,可以選擇把執行這些操作的用戶(hù)及時(shí)間記錄到另外的臨時(shí)表中.以備查詢(xún).
本文作者:
eygle,Oracle技術(shù)關(guān)注者,來(lái)自中國最大的Oracle技術(shù)論壇itpub.
www.eygle.com是作者的個(gè)人站點(diǎn).你可通過(guò)Guoqiang.Gai@gmail.com來(lái)聯(lián)系作者.歡迎技術(shù)探討交流以及鏈接交換.
原文出處:
http://www.eygle.com/faq/Use.Trigger.To.implement.ddl.deny.htm
