Oracle 11g 監控單張表的增刪改查操作

Oracle 11g 監控單張表的增刪改查操作

前言：
線上Oracle資料庫有張表的資料有些亂，根據應用db的log和應用的log也沒有檢查出來誰修改了，所以決定把這張單表做個詳細的insert、update、delete監控。

一：使用資料庫內建的審計功能
1，查看審計功能是否啟動
SQL> show parameter audit                                                                                                                                                                     

NAME    TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest    string /oracle/app/oracle/admin/power
des/adump
audit_sys_operations    boolean FALSE
audit_syslog_level    string
audit_trail    string NONE
SQL>
沒有開啟審計功能，需要自己去開啟一下。

2，開啟審計功能
需要用sysdba，注意audit_trail要為DB_EXTENDED才記錄執行的具體語句...
alter system set audit_sys_operations=TRUE scope=spfile;
SQL> alter system set audit_sys_operations=TRUE scope=spfile;                                                                                                                                 
                                                                                                                                                                                             
System altered.

SQL>

再次查看審計功能是否啟動
SQL> show parameter audit;                                                                                                                                                                   

NAME    TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest    string /oracle/app/oracle/admin/power
des/adump
audit_sys_operations    boolean FALSE
audit_syslog_level    string
audit_trail    string NONE
SQL>         

需要重啟執行個體才能看到狀態。

3，關閉審計功能
SQL> alter system set audit_trail = none scope=spfile;

4，針對某張表的審計功能
AUDIT UPDATE,DELETE,INSERT ON T_TEST by access;

5，對該張表進行各種DML操作測試

6，查詢審計的資訊
select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP DESC;

二，採用觸發器
        看到線上資料庫load比以前增加蠻多的，為了單張表的監控開啟審計比較消耗資源，有些不划算，所以可以採用另外一種辦法來做，就是在表上建立觸發器。

1，先建立建立測試表：
查看已經建立的表 aaa_test與trig_sql。
SQL> describe plas.aaa_test;                                                                                                                                                                 
 Name  Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID    NUMBER
 NAME    VARCHAR2(100)
 LOGIN_TIME    DATE

SQL>
SQL> describe plas.trig_sql;                                                                                                                                                                 
 Name  Null?    Type
 ----------------------------------------- -------- ----------------------------
 LT    DATE
 SID    NUMBER
 SERIAL#    NUMBER
 USERNAME    VARCHAR2(30)
 OSUSER    VARCHAR2(64)
 MACHINE    VARCHAR2(32)
 TERMINAL    VARCHAR2(16)
 PROGRAM    VARCHAR2(64)
 SQLTEXT    VARCHAR2(2000)
 STATUS    VARCHAR2(30)
 CLIENT_IP    VARCHAR2(60)

SQL>

2，並且在 trig_sql表上面添加索引：
 create index  idx_time on plas.trig_sql (LT);
 
3，建立觸發器
create or replace trigger pri_test
  after insert or update or delete on plas.aaa_test
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF inserting THEN
    INSERT INTO plas.trig_sql
        select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
              s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
              'INSERT',
              sys_context('userenv','ip_address')
          from v$sql q, v$session s
        where s.audsid=(select userenv('SESSIONID') from dual)
          and s.prev_sql_addr=q.address
          AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  ELSIF deleting  then
      INSERT INTO plas.trig_sql
          select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
                      s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
                      'DELETE',
                      sys_context('userenv','ip_address')
            from v$sql q, v$session s
            where s.audsid=(select userenv('SESSIONID') from dual)
            and s.prev_sql_addr=q.address
            AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  ELSIF updating then
    INSERT INTO plas.trig_sql
        select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
                    s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
                    'UPDATE',
                    sys_context('userenv','ip_address')
          from v$sql q, v$session s
          where s.audsid=(select userenv('SESSIONID') from dual)
          and s.prev_sql_addr=q.address
          AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  END IF;
END;

4，開始進行資料操作測試：
          insert into plas.aaa_test1 select 2,'tom',sysdate from dual;
          update plas.aaa_test1 a set a.name='tom_up' where a.id=2;
  update plas.aaa_test a set a.name='tom_up1' where a.id=1;
......
          commit;

5，去查看錶記錄，會發現如下
SQL> select * from plas.trig_sql;
LT                SID    SERIAL# USERNAME                      OSUSER                                                          MACHINE                          TERMINAL        PROGRAM                                                          SQLTEXT                                                                          STATUS                        CLIENT_IP
----------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- -------------------------------- ---------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------------------------------------
2014/10/29        1273      33297 POWERDESK                      Administrator                                                    WORKGROUP\WIN-18P5Q5AREH9        WIN-18P5Q5AREH9  plsqldev.exe                                                    begin :id := sys.dbms_transaction.local_transaction_id; end;                    INSERT                        192.168.170.180
2014/10/29        1273      33297 POWERDESK                      Administrator                                                    WORKGROUP\WIN-18P5Q5AREH9        WIN-18P5Q5AREH9  plsqldev.exe                                                    begin :id := sys.dbms_transaction.local_transaction_id; end;                    DELETE                        192.168.170.180
2014/10/29        1273      33297 POWERDESK                      Administrator                                                    WORKGROUP\WIN-18P5Q5AREH9        WIN-18P5Q5AREH9  plsqldev.exe                                                    begin :id := sys.dbms_transaction.local_transaction_id; end;                    INSERT                        192.168.170.180
2014/10/29        1352      40155 POWERDESK                      Administrator                                                    WORKGROUP\WIN-18P5Q5AREH9        WIN-18P5Q5AREH9  plsqldev.exe                                                    begin :id := sys.dbms_transaction.local_transaction_id; end;                    INSERT                        192.168.170.180
2014/10/29        1273      33297 POWERDESK                      Administrator                                                    WORKGROUP\WIN-18P5Q5AREH9        WIN-18P5Q5AREH9  plsqldev.exe                                                    begin :id := sys.dbms_transaction.local_transaction_id; end;                    UPDATE                        192.168.170.180
2014/10/29        1273      33297 POWERDESK                      Administrator                                                    WORKGROUP\WIN-18P5Q5AREH9        WIN-18P5Q5AREH9  plsqldev.exe                                                    begin :id := sys.dbms_transaction.local_transaction_id; end;                    UPDATE                        192.168.170.110
2014/10/29          25      39527 SYS                            oracle                                                          localhost.localdomain            pts/1            sqlplus@localhost.localdomain (TNS V1-V3)                        update plas.aaa_test a set a.name='tom_update' where id=2                        UPDATE                       
2014/10/29          25      39527 SYS                            oracle                                                          localhost.localdomain            pts/1            sqlplus@localhost.localdomain (TNS V1-V3)                        update plas.aaa_test a set a.name='tom_update3' where id=3                      UPDATE                       
8 rows selected

SQL>

PS：看到SQLTEXT有些都為  begin :id := sys.dbms_transaction.local_transaction_id; end; 的，是因為我執行的insert、delete、update語句在plsqldev.exe用戶端執行的，所以沒有記錄下執行的sql語句。而有些通過sqlplus@localhost.localdomain (TNS V1-V3)用戶端串連執行的，會記錄下執行過的update語句。

6，統計下當前都有哪些使用者以及ip執行了dml操作。
SQL> select username,client_ip from plas.trig_sql group by username,client_ip;
USERNAME                      CLIENT_IP
------------------------------ ------------------------------------------------------------
PLAS                          192.168.170.180
DESKER                        192.168.170.110

SQL>

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2