ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 捕获、禁止及审计发出的dml语句的方法

捕获、禁止及审计发出的dml语句的方法

原创 Linux操作系统 作者:junsansi 时间:2007-04-11 00:00:00 0 删除 编辑

捕获发出的dml语句的方法:
SQL> create table mon_sql(
username varchar2(30),
client_ip varchar2(100),
sql_text varchar2(4000),
table_name varchar2(30),
owner varchar2(30)
);

create or replace trigger tri_capt_dml_sql
BEFORE DELETE OR INSERT OR UPDATE ON EMP_TEMP
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;

insert into mon_sql(USERNAME, CLIENT_IP, SQL_TEXT, TABLE_NAME, OWNER)
values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY');
end;
/

禁止ddl操作
create or replace trigger ddl_deny
before create or alter or drop or truncate on database
declare
v_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 || ' ' || v_errmsg);
elsif ora_sysevent = 'ALTER' then
raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || v_errmsg);
elsif ora_sysevent = 'DROP' then
raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || v_errmsg);
elsif ora_sysevent = 'TRUNCATE' then
raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || v_errmsg);
end if;

exception
when no_data_found then
null;
end;
/

审计ddl操作
create table event_table (text varchar(200));

CREATE OR REPLACE TRIGGER bef_alter
BEFORE create or alter or drop or truncate ON DATABASE
DECLARE
sql_text ora_name_list_t;
stmt VARCHAR2(2000);
n number;
begin
INSERT INTO event_table VALUES ('Changed object is ' || ora_dict_obj_name);
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
INSERT INTO event_table VALUES ('text of triggering statement: ' || stmt);
end;
/


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7607759/viewspace-22211/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-21

  • 博文量
    640
  • 访问量
    4125998