各种各样功能的触发器

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

create or replace trigger grant_trigger
BEFORE grant ON database
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 := 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; 
/

CREATE OR REPLACE TRIGGER schema_trigger
   BEFORE CREATE OR ALTER OR DROP ON database
begin
     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_id
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
nocache;


create or replace trigger ddl_audit
   before ddl on bdca.schema
begin
if 
  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;  
  /

最后

以上就是魔幻红酒为你收集整理的各种各样功能的触发器的全部内容，希望文章能够帮你解决各种各样功能的触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。