关闭

一个ORACLE的DDL触发器例子

340人阅读 评论(0) 收藏 举报

一个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: T DDL: truncate table t

总结:

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

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:485508次
    • 积分:10221
    • 等级:
    • 排名:第1773名
    • 原创:254篇
    • 转载:574篇
    • 译文:0篇
    • 评论:9条