« 如何获得跟踪文件名称 | Blog首页 | 我的MT,新的开始 »
使用触发器实现数据库级守护,防止DDL操作
作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】链接：http://www.eygle.com/archives/2004/12/use_trigger_to_implement_ddl_deny.html
不管是有意还是无意的,你可能会遇到数据库中重要的数据表等对象被drop掉的情况,这可能会给我们带来巨大的损失.
通过触发器，我们可以实现对于表等对象的数据库级守护，禁止用户drop操作.
以下是一个简单的范例，供参考:
REM this script can be used to monitor a object
REM deny any drop operation on it.
CREATE OR REPLACE TRIGGER trg_dropdeny
BEFORE DROP ON DATABASE
BEGIN
IF LOWER (ora_dict_obj_name ()) = 'test'
THEN
raise_application_error (num => -20000,
msg => '你疯了,想删除表 '
|| ora_dict_obj_name ()
|| ' ?!!!!!'
|| '你完了，警察已在途中.....'
);
END IF;
END;
/
|
测试效果:
|
SQL> connect scott/tiger
Connected.
SQL> create table test as select * from dba_users;
Table created.
SQL> connect / as sysdba
Connected.
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/tiger
Connected.
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: 你疯了,想删除表 TEST ?!!!!!你完了，警察已在途中.....
ORA-06512: at line 4
|
Oracle从Oracle8i开始,允许实施DDL事件trigger，可是实现对于DDL的监视及控制,以下是一个进一步的例子:
create or replace trigger ddl_deny
before create or alter or drop or truncate on database
declare
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: SQL> set echo on Trigger created. SQL> SQL> alter table test add (id number); SQL> drop table test; SQL> truncate table test;
|
我们可以看到,ddl语句都被禁止了,如果你不是禁止,可以选择把执行这些操作的用户及时间记录到另外的临时表中.以备查询.
By eygle on 2004-12-16 22:49 | Comments (2) | Posted to SQL.PLSQL | Edit |Pageviews:
留言 (2)
你疯了,想删除表 ' || ora_dict_obj_name() || ' ?!!!!!' ||'你完了，警察已在途中.....
我删完就跑
Posted by: fjmingyang at April 4, 2007 4:00 PM
删也删不掉，跑也跑不了，你惨了！
Posted by: eygle at April 5, 2007 10:32 AM
