潛水珽的異想世界

過去要追蹤稽核使用者表格的異動,都會使用trigger來記錄,

但有時候trigger的作動會讓系統在尖峰時刻更加忙碌,

oracle11g之後提供了flashback archive的功能,可以將表格異動存儲在特定表空間裡,

且可自訂rota的頻率。本文記錄一下整個操作的過程,期望未來可以慢慢地捨棄掉"使用trigger來記錄資料異動的過程",

當然啦,比較好還是能夠採購更專業的資料庫稽核軟體是最好的。

--新增flashback archive 空間(稽核內容存放的位置)與保留的時間
CREATE FLASHBACK ARCHIVE longterm
  TABLESPACE users
  RETENTION 1 YEAR;
  
  
--刪除flashback archive 空間
DROP FLASHBACK ARCHIVE longterm;

--將table 加入出flashback archive 機制(打開稽核)
ALTER TABLE DBBED_B1003
FLASHBACK ARCHIVE LONGTERM;

--將table 移出flashback archive 機制(關閉稽核)
ALTER TABLE DBBED_B1003
NO FLASHBACK ARCHIVE;

--查詢特定資料異動的版本資訊
select EMPLOYEE_ID, FIRST_NAME, JOB_ID, VACATION_BALANCE,
       VERSIONS_STARTTIME TS,
       nvl(VERSIONS_OPERATION,'I') OP
from DBBED_B1003
versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
where EMPLOYEE_ID = 100
order by EMPLOYEE_ID, ts;

-- Oracle Database 12 c上。Flashback Data Archive 已擴展為能夠自動捕獲上下文級別的信息，而無需更改基表。

--啟用最高層級的審計級別
exec dbms_flashback_archive.set_context_level('ALL');

select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
       VERSIONS_STARTTIME TS,
       dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
       dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
from EMPLOYEES
versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
where EMPLOYEE_ID = 100
order by EMPLOYEE_ID, ts;

結論
使用觸發器進行審計很常見，並且仍然經常被認為是捕獲 Oracle 數據庫表上的行更改的唯一可能的解決方案。
但是，在 Oracle 數據庫 11.2.0.4 及更高版本中，現在可以使用閃回數據歸檔來滿足審計要求，
這是一種簡單、更高效、更安全的解決方案。

ref:

https://blogs.oracle.com/connect/post/a-fresh-look-at-auditing-row-changes
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5010.htm#SQLRF20008
http://www.dba-oracle.com/t_flashback_data_archive_fda.htm