Oracle 11G 监控单张表的增删改操作

rwewqe

前言：
    线上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>