MS SQL Server 2000 Audit

最近因為公司的資安查核，所以 研究了一下 MS SQL Server 2000 要如何建立稽核軌跡(Audit Trail)，大概找了幾類的方法，整理說明如下:

------------------------------------------------------------------
A. enable SQL Server login audit (through SQL Server Enterprise manager)
------------------------------------------------------------------

ref: http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

notice: SQL Server 要重啟才生效
login audit 資訊會寫在 windows 的 app event log 裡

1. 在這個畫面做設定，安全性 -> 選要做 Login 的稽核層級為何



2. 重啟SQL Server

3. 接著，後續的login記錄，就會記錄在 windows event log 內 (歸於 application這個類別)



------------------------------------------------------------------
B. enable SQL Server 「c2 audit mode」:
------------------------------------------------------------------

ref: http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

SQL Server 內建有一 C2 audit mode, 預設是沒打開的

打開方式如下:
1. 在 command mode 裡下下列的指令
C:\Documents and Settings\Administrator>osql -E
1> exec sp_configure
2> go

1> exec sp_configure 'c2 audit mode','1'
2> go
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請聯絡您的系統管理員。
設定選項 'c2 audit mode' 從 0 變更到 1。請執行 RECONFIGURE 陳述式來安裝。
1> reconfigure
2> go

2. 重啟 SQL Server
3. 接著，SQL Server即會以 c2 的標準做 log, log 檔會以 .trc (trace)存在
C:\Program Files\Microsoft SQL Server\MSSQL\Data 下
4. .trc 可用 sql profiler 來看
5. 目前正在錄的 .trc 無法直接開，可先將 sql server restart 後，該 .trc 即可開啟
.trc 檔會做 log rotate，被rotate 掉的 .trc 也可以直接開來看

效果如下，可以稽核 對DB的一些Command及登入資訊，不過使用這種方式要注意，它可能會拖慢DB的速度，所以若要打開此選項，一定要先測試過，看看目前的交易量及設備容不容許將這選項打開。



另外，何謂 C2 等級的 audit log?

根據美國國防部的一個標準:
DoD 5200.28-STD, "Department of Defense Trusted Computer System Evaluation Criteria"
http://www.boran.com/security/tcsec.html
有將電腦系統依安全的程度，分為下列等級，C2即是其中的一級:

* DISCRETIONARY PROTECTION
Class (C1): Discretionary Security Protection
Class (C2): Controlled Access Protection
* MANDATORY PROTECTION
Class (B1): Labeled Security Protection
Class (B2): Structured Protection
Class (B3): Security Domains
* VERIFIED PROTECTION
Class (A1): Verified Design
Beyond Class (A1)

C2何底要求為何，依該標準，簡述如下:
Systems in this class enforce a more finely grained discretionary access
control than (C1) systems, making users individually accountable for their
actions through login procedures, auditing of security-relevant events, and
resource isolation.
實際的要求，要來的細的多，要參考該標準文件才行。

------------------------------------------------------------------
C. 使用 Server Side Trace 的功能 來客制 trace 有興趣的 sql server operation
------------------------------------------------------------------

ref: http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx
(與 SQL Profiler 原理一樣)

其實，這就好像用 SQL Profiler 一樣，選自已有興趣的event，來錄製相關的

上述的 C2 log 也是用一樣的原理，差別在於 C2 audit log 是 SQL Server建好的套餐

如果覺的那樣的 log 太多了，則 你以用 server side trace 的方式，來挑想log的部分記錄即可

------------------------------------------------------------------
D. 使用 DBCC command --> 但是 結果很難看.. 不是人可以看的
------------------------------------------------------------------

指令: DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )

PARAMETERS:

Dbid or dbname - Enter either the dbid or the name of the database in question.

type - is the type of output:

0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

by default type = 0

可以下下列的指令試看看

DBCC log (master)
DBCC log (master, type=4)

效果如下:



------------------------------------------------------------------
E. 使用SQL Server 2000 內一個沒被寫在文件的function: fn_dblog
------------------------------------------------------------------

-- entire tlog
select * from ::fn_dblog(NULL, NULL)
-- selection of tlog
select * from ::fn_dblog('129:148:1', '129:150:6')
Note: 129:148:1 is LSN

Select * from ::fn_dblog('', '') where [Begin Time] >= '02/03/08'

------------------------------------------------------------------
F. 使用 3rd party tool, 來觀看 SQL Transaction Log - 以 Lumigent Log Explorer for SQLServer 為例
------------------------------------------------------------------

用上述的 DBCC, fn_dblog 內建指令，很明顯的 雖然可以 Query 出log，但是，那些log
根本就不是人可以看的懂的

但是，MS 官方也沒有這種可以讓人看Transaction Log的工具 (真怪)

所以，只得求助於 3rd party 軟體

將 Lumigent Log Explorer for SQLServer 的 cliet 端、Server端(需與SQL Server裝在一起)裝好後，即可針對 Transaction log做查看分析的動作、還可以把單獨的Transaction挑出來、Rollback 哦 :)

另外，這個軟體，只是幫你 parsing transaction log，所以非transaction類的部分(如, Query的操作) 沒有辦法呈現出來。
若要 Audit 到對 DB 的 insert/update/delete/query ...的話，則要打開 c2 audit mode 或是使用 server side trace or sql profile 來記錄相關的操作

Demo 畫面如下: