各种各样功能的触发器

来源：互联网发布：吉他软件中文版下载编辑：程序博客网时间：2024/04/29 09:36

1、可以实现监控任何地点sql语句，使之失效

create or replace trigger grant_triggerBEFORE grant ON databasedeclaren number; stmt varchar2(4000); sql_text ora_name_list_t; begin n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP stmt := sql_text(i); END LOOP; if stmt like ('grant select any table%')  then    Raise_application_error(-20001, 'You have no privilege to do these operations!');  end if;end; /

2、使否个用户只能操作自己schema的内容

CREATE OR REPLACE TRIGGER schema_trigger   BEFORE CREATE OR ALTER OR DROP ON databasebegin     if ora_login_user not in ('DBA%') and ora_login_user <> ora_dict_obj_owner  then    Raise_application_error(-20001, 'You have no privilege to do these operations!');  end if;end;/

3、审计DDL语句，记录执行ddl语句的用户的详细情况

create table bdca_ddl_audit( audit_id number(12) primary key, db_name varchar2(30), login_user varchar2(30), ddl_time date, ip_address varchar2(20), audsid varchar2(20), schema_user varchar2(30), schema_object varchar2(40), login_tool varchar2(40), os_user varchar2(40), ddl_sql varchar2(4000));create sequence seq_audit_idminvalue 1maxvalue 999999999999start with 1increment by 1nocache;create or replace trigger ddl_audit   before ddl on bdca.schemabeginif   ora_sysevent not in ('TRUNCATE')  then  declare     n number;    str_stmt varchar2(4000);    sql_text ora_name_list_t;    l_trace number;    v_action varchar2(50);    v_db_name varchar2(50);    v_ip_addr varchar2(50);    v_os varchar2(50);    v_session_id varchar2(50);    v_loginuser varchar2(50);     v_obj_name varchar2(50);    v_owner varchar2(50); begin     n:=ora_sql_txt(sql_text);     for i in 1 .. n loop       str_stmt:=substr(str_stmt||sql_text(i),1,3000);     end loop;    v_db_name :=sys_context('USERENV', 'db_name');    v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');    v_os:=sys_context('userenv', 'os_user');    v_session_id:=userenv('SESSIONID');    v_loginuser:= ora_login_user;    v_owner:=ora_dict_obj_owner;    v_obj_name:=ora_dict_obj_name;    INSERT INTO bdca_ddl_audit      ( audit_id,       db_name,       login_user,       ddl_time,       ip_address,       audsid,       schema_user,       schema_object,       os_user,       ddl_sql)    VALUES      ( seq_audit_id.nextval,       v_db_name,       v_loginuser,       SYSDATE,       v_ip_addr,      v_session_id,       v_owner,       v_obj_name,       v_os,       str_stmt);  exception    when no_data_found then      null;  end;  Raise_application_error(-20001, 'You have no privilege to do these operations! Please call on DBA !');end if;end;    /

0 0