ORACLE TRIGGER ON DDL

[Q]怎么捕获用户登录信息，如SID，IP地址等  

  [A]可以利用登录触发器，如  

  CREATE   OR   REPLACE   TRIGGER   tr_login_record  

  AFTER   logon   ON   DATABASE  

  DECLARE  

  miUserSid   NUMBER;  

  mtSession   v$session%ROWTYPE;  

  CURSOR   cSession(iiUserSid   IN   NUMBER)   IS  

  SELECT   *   FROM   v$session  

  WHERE   sid=iiUserSid;  

  BEGIN  

  SELECT   sid   INTO   miUserSid   FROM   v$mystat   WHERE   rownum<=1;  

  OPEN   cSession(miUserSid);  

  FETCH   cSession   INTO   mtSession;  

  --if   user   exists   then   insert   data  

  IF   cSession%FOUND   THEN  

  INSERT   INTO   log$information(login_user,login_time,ip_adress,ausid,terminal,    

  osuser,machine,program,sid,serial#)  

  VALUES(ora_login_user,SYSDATE,SYS_CONTEXT   ('USERENV','IP_ADDRESS'),    

  userenv('SESSIONID'),  

  mtSession.Terminal,mtSession.Osuser,  

  mtSession.Machine,mtSession.Program,  

  mtSession.Sid,mtSession.Serial#);  

  ELSE  

  --if   user   don't   exists   then   return   error  

  sp_write_log('Session   Information   Error:'||SQLERRM);  

  CLOSE   cSession;  

  raise_application_error(-20099,'Login   Exception',FALSE);  

  END   IF;  

  CLOSE   cSession;  

  EXCEPTION  

  WHEN   OTHERS   THEN  

  sp_write_log('Login   Trigger   Error:'||SQLERRM);  

  END   tr_login_record;  

  在以上触发器中需要注意以下几点  

  1、该用户有v_$session与v_$mystat的对象查询权限，可以在sys下对该拥护显式授权。  

  2、sp_write_log原本是一个写日志的过程，可以置换为自己的需要，如null跳过。  

  3、必须在创建该触发器之前创建一个log$information的表记录登录信息。  

  [Q]怎么捕获整个数据库的DDL语句或者是说对象结构变化与修改  

  [A]可以采用DDL触发器，如  

  CREATE   OR   REPLACE   TRIGGER   tr_trace_ddl  

  AFTER   DDL   ON   DATABASE    

  DECLARE  

  sql_text   ora_name_list_t;  

  state_sql   ddl$trace.ddl_sql%TYPE;  

  BEGIN  

  FOR   i   IN   1..ora_sql_txt(sql_text)   LOOP  

  state_sql   :=   state_sql||sql_text(i);  

  END   LOOP;  

  INSERT   INTO   ddl$trace(login_user,ddl_time,ip_address,audsid,  

  schema_user,schema_object,ddl_sql)  

  VALUES(ora_login_user,SYSDATE,userenv('SESSIONID'),  

  sys_context('USERENV','IP_ADDRESS'),  

  ora_dict_obj_owner,ora_dict_obj_name,state_sql);  

  EXCEPTION    

  WHEN   OTHERS   THEN    

  sp_write_log('Capture   DDL   Excption:'||SQLERRM);  

  END   tr_trace_ddl;  

  在创建以上触发器时要注意几点  

  1、必须创建一个ddl$trace的表，用来记录ddl的记录  

  2、sp_write_log原本是一个写日志的过程，可以置换为自己的需要，如null跳过。  

  [Q]怎么捕获表上的DML语句（不包括select)语句）  

  [A]可以采用dml触发器，如  

  CREATE   OR   REPLACE   TRIGGER   tr_capt_sql  

  BEFORE   DELETE   OR   INSERT   OR   UPDATE    

  ON   manager.test  

  DECLARE  

  sql_text   ora_name_list_t;  

  state_sql   capt$sql.sql_text%TYPE;  

  BEGIN  

  FOR   i   IN   1..ora_sql_txt(sql_text)   LOOP  

  state_sql   :=   state_sql   ||   sql_text(i);  

  END   LOOP;  

  INSERT   INTO   capt$sql(login_user,capt_time,ip_address,audsid,owner,table_name,sql_text)  

  VALUES(ora_login_user,sysdate,sys_context('USERENV','IP_ADDRESS'),  

  userenv('SESSIONID'),'MANAGER','TEST',state_sql);  

  EXCEPTION    

  WHEN   OTHERS   THEN  

  sp_write_log('Capture   DML   Exception:'||SQLERRM);  

  END   tr_capt_sql;  

  在创建以上触发器时要注意几点  

  1、必须创建一个capt$sql的表，用来记录ddl的记录  

  2、sp_write_log原本是一个写日志的过程，可以置换为自己的需要，如null跳过。