监控oracle9i数据库ddl操作，及捕获dml操作

    最近公司加强内控管理，领导希望能对缴费卡数据库的一张密码表做监控，防止非法修改。这里我没有选择审计，因为审计会降低系统的整体性能。最后还是决定使用触发器来做，虽然触发器也会影响执行对象的性能，不过应该会比审计更方便些。

   监控ddl方法如下：

CREATE TABLE DDL_OBJECTS
(
  USER_ID     VARCHAR2(30 BYTE),
  LOGIN_USER  VARCHAR2(30 BYTE),
  OBJ_TYPE    VARCHAR2(20 BYTE),
  OBJ_NAME    VARCHAR2(30 BYTE),
  OBJ_OWNER   VARCHAR2(30 BYTE),
  OBJ_IP      VARCHAR2(30 BYTE),
  OBJ_DATE    DATE,
  SQL_TEXT    VARCHAR2(4000 BYTE)
)
TABLESPACE TOOLS;

 

CREATE OR REPLACE TRIGGER tri_ddl_object
  before ddl  on database
declare
  sql_text ora_name_list_t;
  state_sql ddl_objects.sql_text%TYPE;
  v_ip varchar2(30);
begin
  FOR i IN 1..ora_sql_txt(sql_text) LOOP
 state_sql := state_sql || sql_text(i);
 END LOOP;
  select sys_context('userenv','ip_address') into v_ip from dual;
  insert into ddl_objects(user_id,login_user,obj_type,
                        obj_name,obj_owner,obj_ip,obj_date,sql_text)
  values (user,sys.login_user,sys.dictionary_obj_type,
          sys.dictionary_obj_name,sys.dictionary_obj_owner,v_ip,sysdate,substr(state_sql,1,4000));
 exception
   when others then
   begin
   insert into ddl_objects(user_id,login_user,obj_type,
                        obj_name,obj_owner,obj_ip,obj_date)
  values (user,sys.login_user,sys.dictionary_obj_type,
          sys.dictionary_obj_name,sys.dictionary_obj_owner,sys_context('userenv','ip_address'),sysdate);
          exception when others then
     null;
  end;
end tri_ddl_object;
/

 

捕获对表rcpms.rcpms_loginuser的dml操作方法：

CREATE TABLE MODIFY_TAB
(
  USER_ID     VARCHAR2(30 BYTE),
  LOGIN_USER  VARCHAR2(30 BYTE),
  OBJ_TYPE    VARCHAR2(20 BYTE),
  OBJ_NAME    VARCHAR2(30 BYTE),
  OBJ_OWNER   VARCHAR2(30 BYTE),
  OBJ_IP      VARCHAR2(30 BYTE),
  OBJ_DATE    DATE,
  SQL_TEXT    VARCHAR2(4000 BYTE)
)
TABLESPACE TOOLS;

 

CREATE TABLE MODIFY_PASSWD
(
  USER_ID     VARCHAR2(30 BYTE),
  LOGIN_USER  VARCHAR2(30 BYTE),
  OBJ_TYPE    VARCHAR2(20 BYTE),
  OBJ_NAME    VARCHAR2(30 BYTE),
  OBJ_OWNER   VARCHAR2(30 BYTE),
  OBJ_IP      VARCHAR2(30 BYTE),
  OBJ_DATE    DATE,
  SQL_TEXT    VARCHAR2(4000 BYTE)
)
TABLESPACE TOOLS;

 

CREATE OR REPLACE TRIGGER tri_mod_loginuser
BEFORE DELETE OR INSERT OR UPDATE
ON rcpms.rcpms_loginuser
DECLARE
sql_text ora_name_list_t;
state_sql modify_passwd.sql_text%TYPE;
v_ip varchar2(30);
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql || sql_text(i);
END LOOP;
  select sys_context('userenv','ip_address') into v_ip from dual;
  insert into modify_passwd(user_id,login_user,obj_type,
                        obj_name,obj_owner,obj_ip,obj_date,sql_text)
  values (user,sys.login_user,'TABLE',
          'RCPMS_LOGINUSER','RCPMS',v_ip,sysdate,substr(state_sql,1,4000));
/*
INSERT INTO rcpms.modify_passwd(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
   begin
   insert into modify_passwd(user_id,login_user,obj_type,
                        obj_name,obj_owner,obj_ip,obj_date)
  values (user,sys.login_user,'TABLE',
          'RCPMS_LOGINUSER','RCPMS',sys_context('userenv','ip_address'),sysdate);
          exception when others then
     null;
  end;
END tri_mod_loginuser;

触发器写的比较简单，捕捉到的sql语句可能很长，所以额外增加了不记录sql的exception，为了对系统影响最小，没有做更多的exception。

另外触发器不支持commit，所以对于失败的操作可能无法记录。

其实个人很不喜欢这样牺牲性能的安全手段（虽然这里的影响并不很大），这完全是因为系统无法封闭而造成的无奈之举。更好的从操作系统和应用本身来控制安全问题才是上策。