各种各样功能的触发器

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; 
/

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

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;  
  /