使用触发器实现数据库级守护,防止DDL操作
来源:互联网 发布:淘宝网自动发货软件 编辑:程序博客网 时间:2024/05/16 04:02
不管是有意还是无意的,你可能会遇到数据库中重要的数据表等对象被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语句都被禁止了,如果你不是禁止,可以选择把执行这些操作的用户及时间记录到另外的临时表中.以备查询.
- 如何使用触发器实现数据库级守护,防止DDL操作
- 使用触发器实现数据库级守护,防止DDL操作
- 使用触发器实现数据库级守护,防止DDL操作
- 如何使用触发器实现级守护,防止DDL操作
- 使用触发器实现数据库级守护
- 记录数据库ddl操作触发器
- Oracle使用触发器禁止远程DDL操作
- SQL Server 2005中使用DDL触发器监控数据库变化
- 利用Oracle DDL触发器实现DDL监控
- DDL语句操作数据库
- **MYSQL** 数据库操作DDL
- DDL-操作数据库
- 因OGG ddl触发器 导致所有user不能ddl操作
- OGG ddl触发器 导致所有user不能ddl操作
- oracle数据库登录、DDL触发器的应用
- 使用触发器记录数据库删除操作
- 使用DDL,DML语言对数据库进行基本操作。
- DDL触发器
- 没有文件扩展“.js”的脚本引擎--解决办法(downmoon)
- 用冰刃结束pubwin
- 程序员思维定势
- 三种禁用FileSystemObject组件的方法
- python模块之StringIO
- 使用触发器实现数据库级守护,防止DDL操作
- -generating artifacts的一种解决办法
- 用filter解决汉字编码及禁止页面缓存 和 用listener实现在线人数统计
- 在ASP.NET中创建安全的web站点
- 关于"深入浅出MFC"的几个问题
- ASP.NET处理浏览器中数据分页的方法
- Java ToyProgam LunacyDoctor implements IO and Sequence Stack
- 中外主要节日表
- 笑死俺了!!!!! <基于sinox操作系统公厕收费系统>可行性报告