IDC资讯中心
如何使用触发器实现数据库级守护,防止ddl操作
–对于重要对象,实施ddl拒绝,防止create,drop,truncate,alter等重要操作
last updated: sunday, 2004-10-31 12:06 eygle
不管是有意还是无意的,你可能会遇到数据库中重要的数据表等对象被drop掉的情况,这可能会给我们带来巨大的损失.
通过触发器，我们可以实现对于表等对象的数据库级守护，禁止用户drop操作.
以下是一个简单的范例，供参考:
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开始,允许实施ddl事件trigger，可是实现对于ddl的监视及控制,以下是一个进一步的例子:
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语句都被禁止了,如果你不是禁止,可以选择把执行这些操作的用户及时间记录到另外的临时表中.以备查询.
本文作者:
eygle,oracle技术关注者,来自中国最大的oracle技术论坛itpub.
www.eygle.com是作者的个人站点.你可通过guoqiang.gai@gmail.com来联系作者.欢迎技术探讨交流以及链接交换.
原文出处:
http://www.eygle.com/faq/use.trigger.to.implement.ddl.deny.htm
如欲转载,请注明作者与出处.并请保留本文的连接.
回首页
