月光软件站 - 编程文档 - 数据库 - 如何使用触发器实现数据库级守护,防止DDL操作

数据库

本类阅读TOP10

·SQL语句导入导出大全
·SQL Server日期计算
·SQL语句导入导出大全
·SQL to Excel 的应用
·Oracle中password file的作用及说明
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·sqlserver2000数据库置疑的解决方法
·一个比较实用的大数据量分页存储过程
·如何在正运行 SQL Server 7.0 的服务器之间传输登录和密码
·SQL中两台服务器间使用连接服务器

→ 分类导航

VC语言	Delphi
VB语言	ASP
Perl	Java
Script	数据库
其他语言	游戏开发
文件格式	网站制作
软件工程	.NET开发

如何使用触发器实现数据库级守护,防止DDL操作

作者：未知来源：月光软件站 加入时间：2005-2-28　月光软件站

如何使用触发器实现数据库级守护,防止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 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:
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是作者的个人站点.你可通过[email protected]来联系作者.欢迎技术探讨交流以及链接交换.

原文出处:

http://www.eygle.com/faq/Use.Trigger.To.implement.ddl.deny.htm

如欲转载,请注明作者与出处.并请保留本文的连接.

回首页

相关文章：

相关软件：