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

捕获发出的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7607759/viewspace-22211/