一个ORACLE的DDL触发器例子

一个ORACLE的DDL触发器例子

有时我们需要控制用户对表执行DDL操作，包括truncate等操作。为了达到灵活控制的目的，我们使用了DDL trigger。由于我们的系统每5分钟会检查alert log错误信息并短信报警，这样我采取了写alert log的方式来记录不明DDL操作。

下面这个trigger限制非服务器本机进行的DDL操作，由于应用的要求，我们允许非服务器本地登陆用户对snapshot log和以log结尾的表进行操作。

为了实现目标我们需要给用户授予相关权限

conn / as sysdba
grant select on v_$session to alibaba;
grant execute on dbms_system to alibaba;

CREATE or replace TRIGGER db_ddl_trigger
before ddl on alibaba.schema
declare
n number;
str_stmt varchar2(4000);
sql_text ora_name_list_t;
l_trace number;
str_session v$session%rowtype;
BEGIN
select count(*) into l_trace from dual
where ora_dict_obj_name not like 'MLOG%'
and ora_dict_obj_name not like '%LOG'
and utl_inaddr.GET_HOST_ADDRESS is not null
and sys_context('userenv','ip_address') is not null
and sys_context('userenv','ip_address') <> utl_inaddr.GET_HOST_ADDRESS;

if l_trace > 0 then

n := ora_sql_txt(sql_text);

for i in 1..n loop
str_stmt := substr(str_stmt || sql_text(i),1,3000);
end loop;

select * into str_session
from v$session where audsid = userenv('sessionid');
sys.dbms_system.ksdwrt(2,to_char(sysdate,'yyyymmdd hh24:mi:ss')||' ORA-20001 user: '||user||' program: '||
str_session.program||' IP: '|| sys_context('userenv','ip_address')||' object: '||ora_dict_obj_name||'DDL: '||str_stmt );

raise_application_error(-20001,'You can not execute ddl on this object except on the local machine');
end if;
END;

我在非本机sqlplus中登陆尝试truncate table ，返回自己定义错误信息，并在alert log中写入一行记录

20041117 18:45:50 ORA-20001 user: ALIBABA program: sqlplus.exe IP: 10.0.78.80 object: TDDL: truncate table t

总结：

数据库范围内的DDL触发器作为对象是存储在创建它们的数据库中的，服务器范围内的DDL触发器作为对象是存储在master数据库中的；DDL触发器又是与激发它们的语句运行在相同的事务中的，所以DDL事件执行后，就可以从触发器中回滚此事务，所以就会触发了

http://danielzzu.blog.163.com/blog/static/1185153042011101373913384/