應用系統的數據日志管理

吳曉燕

（中國國際石油化工聯合有限責任公司 北京市 100728）

1 引言

國內某油氣貿易公司是世界上大型國際石油貿易公司之一，主要經營原油貿易、成品油貿易、天然氣貿易及倉儲物流等業務。為了規范業務管理、加強風險管控能力、提高工作效率，公司結合實際業務情況，開發了一套貿易系統，應用于貿易、執行、運輸、保險、結算等各個業務環節，是公司各部門和境內外子公司的重要工作平臺。

貿易系統包括實貨管理、金融衍生品管理、運輸管理、市場風險管理、信用風險管理、財務管理等諸多功能模塊，應用系統是用PowerBuilder開發，數據庫是ORACLE 10G，系統已經使用了十幾年，后臺數據庫累計創建了近千張表，還有大量的視圖、觸發器、作業等，系統架構特別復雜，系統前端應用程序代碼可以記錄數據修改日志，為了防止日志記錄遺漏，還可以開啟數據庫審計功能，并通過Oracle LogMiner定時解析歸檔日志，對數據修改日志進行有效管理。

2 應用程序代碼記錄數據變更日志

2.1 創建日志數據庫表

數據的操作分新增、修改、刪除三種情況，新增操作是一次性操作，即每條記錄只能新增一次，不用單獨建日志信息記錄表，每張表中除了業務字段外，都有數據創建時間、創建人等字段，可以清晰地看出這條記錄是誰建的、什么時間建的。修改操作可能對數據庫表每個字段進行多次修改，刪除操作是將業務數據從業務表移除，因此，修改和刪除操作需要分別創建日志信息表。

修改日志表的字段包括業務表名、字段名、業務數據主鍵值、修改人、修改時間、修改前的值、修改后的值，因為同一張表有多個字段多條數據、同一個字段有多人修改、同一個人在不同時點會對同一個字段修改多次，所以將業務表名、字段名、業務數據主鍵值、修改人、修改時間作為聯合主鍵，創建修改日志表的語句為：

刪除日志表的字段包括刪除數據的主鍵值、表名、字段名稱、刪除前的值、刪除人、刪除時間，因為刪除操作需要記錄所有數據庫表中每條數據每個字段刪除前的值，所以將數據的主鍵值、表名、字段名稱作為聯合主鍵，創建刪除日志表的語句為：

2.2 編寫修改日志的公共函數

記錄修改日志的公共函數設計了兩個，第一個是傳輸整表，函數循環判斷傳輸表每個字段的datamodified狀態，如果datamodified狀態為TRUE，就往修改日志表T_MODIFY插入一條數據，否則跳過繼續判斷下一個字段。第二個是只傳輸固定字段和修改前后的值，修改信息插入日志表。以第一個函數為例，主體部分程序代碼為：

2.3 編寫刪除日志的公共函數

記錄數據刪除日志的公共函數參數有表名、刪除數據的主鍵值，接收參數后，在數據庫檢索數據，生成臨時的數據存儲，通過循環將每個字段的值、刪除時間、刪除人等信息寫入刪除日志表，刪除函數主體部分的程序代碼為：

2.4 應用系統頁面調用公共函數

如果對應用系統界面操作需要記錄日志，在保存按鈕代碼中調用數據修改日志的公共函數，在刪除按鈕代碼中調用數據刪除日志的公共函數，公共函數調用方法相同，只是不同頁面調用時傳輸的表名、數據主鍵值等參數不一樣。需要注意的是日志修改和業務數據修改必須為同一個事務，日志函數執行成功后與業務數據修改一起提交，保證數據同步修改。

3 數據庫審計記錄數據日志

3.1 開啟數據庫審計

Oracle審計總體上可分為“標準審計”和“細粒度審計”。其中標準審計可分為用戶級審計和系統級審計。用戶級審計是任何Oracle用戶可設置的審計，主要是用戶針對自己創建的數據庫表或視圖進行審計，記錄所有用戶對這些表或視圖的一切成功和(或)不成功的訪問要求以及各種類型的SQL操作。系統級審計只能由DBA設置，用以監測成功或失敗的登錄要求、監測GRANT和REVOKE操作以及其他數據庫級權限下的操作。

標準審計可以分為以下三種標準審計類型。語句審計，對某種類型的SQL語句審計，不指定結構或對象。特權審計，對執行相應動作的系統特權的使用審計。對象審計，對一特殊模式對象上的指定語句的審計。這三種標準審計類型分別對如下3方面進行審計：審計語句的成功執行、不成功執行，或者其兩者；對每一用戶會話審計語句執行一次或者對語句每次執行審計一次；對全部用戶或指定用戶活動的審計。

開啟數據庫審計的步驟是先用管理員賬號進入PL/SQL,使用show parameter audit;查看是否開啟數據庫審計功能。如果未開啟，使用SQL語句alter system set audit_sys_operations=TRUE scope=spfile;開啟，最后重啟實例。

當數據庫的審計功能打開后，在語句執行階段產生審計記錄。審計記錄包含有審計的操作、用戶執行的操作、操作的日期和時間等信息。審計記錄可存在數據字典表（稱為審計記錄，在SYS模式的AUD$表中）或操作系統審計記錄中。Oracle提供了相應的視圖供查詢相關的審計信息，通過SELECT * FROMDBA_AUDIT_TRAIL語句查看。

數據庫審計可以在DBA_OBJ_AUDIT_OPTS表中設置審計對象，審計對象有表、視圖、語句、特權等，該公司對關鍵的表、視圖、語句等設置審計，其中只對執行成功的語句進行審計，且語句每次執行審計一次。設置審計的SQL語句為：insert into dba_obj_audit_opts (owner, object_name, object_type, alt, aud, com, del, gra, ind, ins, loc, ren, upd) values ( ’UNI’, ’LH_BGHT’, ’TABLE’, ’A/-’, ’A/-’, ’A/-’, ’A/-’, ’A/-’, ’A/-’, ’A/-’, ’ A/-’, ’A/-’,’A/-’);其中，’A/-’表示對該表相應的操作記錄審計，’-/-’表示對該表相應的操作不記錄審計。這條語句含義是對數據庫用戶UNI下的LH_BGHT表記錄新增、修改、刪除、索引變動等審計日志。

系統運行過程中難免會在數據庫創建新表或視圖等，可以在ALL_DEF_AUDIT_OPTS表對數據對象設置默認的審計選項，保證新對象創建之后，也能對其進行審計。

該公司不僅對一般用戶的操作進行審計，而且還對sys用戶的操作進行了審計，對于Windows Server操作系統來說，sys用戶操作的審計記錄存放在操作系統中，可以通過事件查看器查詢。

由于審計信息會占用生數據庫較大的空間，并增長迅速，可以定時將審計記錄拷貝到日志分析數據庫永久保留。

3.2 數據庫日志分析

有了數據庫審計信息，可以查詢到某條SQL語句是在什么計算機、什么時間、對什么對象進行了什么樣的操作。但是，并不能查詢到這條SQL語句改變多少行的數據、是哪些數據庫行、這些數據行的前值和后值是什么等信息。鑒于這樣的原因，可以利用Oracle LogMiner對生產數據庫的歸檔日志進行分析，并創建了日志分析數據庫保存日志分析結構和生產數據庫的審計信息。有了審計信息和日志分析記錄，在出現任何異常狀況時都可以快速的查詢出任何一個會話的相關信息、任何一條SQL語句的相關信息以及會話和SQL語句對數據庫產生的具體影響。

Oracle LogMiner 是Oracle公司提供的一個實際非常有用的分析工具，使用該工具可以輕松獲得Oracle 重做日志文件（歸檔日志文件）中的具體內容，LogMiner分析工具實際上是由一組PL/SQL包和一些動態視圖組成，它作為Oracle數據庫的一部分來發布，是oracle公司提供的一個完全免費的工具。

日志文件中存放著所有進行數據庫恢復的數據，記錄了針對數據庫結構的每一個變化，也就是對數據庫操作的所有DML語句。

LogMiner工具的主要用途有：

（1）跟蹤數據庫的變化：可以離線的跟蹤數據庫的變化，而不會影響在線系統的性能；

（2）回退數據庫的變化：回退特定的變化數據，減少pointin-time recovery的執行；

（3）優化和擴容計劃：可通過分析日志文件中的數據以分析數據增長模式。

創建日志分析數據庫時，首先要在服務器的操作系統中創建共享文件夾用來存儲生產環境生成的歸檔日志和數據字典文件。從數據庫備份部分展示的腳本中可以看出，生產數據庫每小時調用switch.sql來生成歸檔日志時會同時生成最新的生產數據庫數據字典文件，語句為execute dbms_logmnr_d.build(dictionary_filename=>'uni_52_dict.ora',dictionary_location =>'D:oracleproduct10.2.0logs')；生產數據庫每小時調用auto_copy_arc.bat來將每個小時生成的歸檔日志拷貝到共享存儲時會同時將最新生成的歸檔日志文件拷貝至日志分析數據庫服務器，語句為：

if not exist "%remotedisk%\%dict_file_name%" (xcopy "D:oracleproduct10.2.0logs\%dict_file_name%""%remotedisk%")。這樣生產數據庫生成的最新的數據字典文件和歸檔日志均被拷貝至日志分析數據庫所在的服務器，以供日志分析使用。日志文件中保存的均是數據庫對象的代碼，而不是對象的名字，為了保證日志分析出來的記錄的可讀性，所以需要生成最新的數據字典文件參與日志分析。

系統定時每小時進行一次日志分析，日志分析數據庫會記錄下已經被分析過的日志文件的名字，然后搜索相應目錄下的日志文件，如果存在未被分析過的日志文件，則利用最新的數據字典文件分析這些日志文件。具體如下：

日志分析出的結果存入LOGMNR_RECORDER表，包括所屬事務號、表名、ROW_ID、會話信息、操作類型、SQL_REDO、SQL_UNDO等信息，下面以一張表某個字段數據修改為例，說明如何使用日志分析結果追蹤修改源。

（1）查詢日志分析表LOGMNR_RECORDER

如果需要查詢TDR字段修改記錄，where條件是table_name= ‘LH_BGHT_DETAIL’ and row_id = ’AAAMrwAAKAABjYbAAL’ and lower ( sql_redo ) like ‘%tdr%’，從LOGMNR_RECORDER表中查詢數據修改日志，SQL_REDO是修改TDR字段的SQL語句，從語句可以看出該記錄的TDR字段從空修改成20210626，SQL_UNDO是回滾的SQL語句，AUDIT_SESSIONID是會話ID，通過會話ID可以進一步查找修改該字段的用戶信息。

（2）利用會話信息查找修改源

系統用另外一張表SESSION_RECORDER記錄每個賬號的登錄會話信息，用戶賬號登錄后，系統將會話ID、用戶賬號、操作系統名稱、計算機名、客戶端信息、登錄的應用程序執行文件、登錄時間、應用程序ID號存入SESSION_RECORDER表，客戶端信息包含具體IP地址，可以精確定位，以日志分析表的會話ID為條件查詢會話信息，從查詢結果可以看到修改TDR這個字段的IP為10.3.20.15，應用程序ID是1834。

4 結論

該公司貿易系統利用應用程序代碼記錄數據修改日志和刪除日志，同時，使用Oracle審計功能對數據庫對象、特權、語句進行審計并保存審計信息，利用Oracle LogMiner對數據庫日志文件進行分析并保存分析結果，保障了數據庫會話、操作、影響范圍的可追溯，進一步提升了數據庫信息安全級別。