iT邦幫忙

0

MS-SQL 如何查詢用SQL 指令更改資料庫資料的記錄

各位好

小弟家的ERP系統資料庫用的是SQL Server 2012 R2,前幾天發生一件事,我們發現系統中有很多交易的金額被修改成相同的數字,由於我們用的系統是自動過帳的,也就是當交易儲存時,傳票就產生了,因此我們進一步發現這些交易所對應的傳票金額都是正確的,也就是說,交易儲存時的金額是正確的,後來應該有人用Update之類的指令變成了交易金額,才會有交易與傳票金額不一致的情形發生。

事發後,小弟曾試著開啟系統中的log_xx的檔案查詢,但發現開啟檔案的內容都是亂碼,也是試過在Management Studio中開啟Menagement→SQL Server Log的記錄查看,但也看不到相關的記錄,想請教網路上的SQL達人,像這樣的情形該如何查出相關的記錄?謝謝!!

先問那些MIS誰有去動過資料庫~搞不好會有人承認@@...
rogermou iT邦新手 5 級 ‧ 2018-07-30 09:38:13 檢舉
就是沒人承認,然後我們家主機又是廠商代管...
您好, 我並非DBA, 但知資料庫是能開啟異動記錄, 可參考這篇
https://jasper-it.blogspot.com/2018/01/sql-server-1-change-data-capture.html
3
rogeryao
iT邦研究生 1 級 ‧ 2018-07-30 09:07:57

抓出"被修改成相同的數字"那幾筆資料的建立日期時間跟修改日期時間,
大概就可以判斷出是透過系統改的或是 MIS 人員下 SQL 改的.
1.透過系統改的:或許有 log 或相關欄位可以查到
2.MIS 人員下 SQL 改的:應該查不到是誰下的 SQL 指令

rogermou iT邦新手 5 級 ‧ 2018-07-30 09:39:47 檢舉

你好,建立日期時間跟修改日期時間都相關沒變,所以我才會懷疑有人是用指令去批次修改的...

0
fuzzylee1688
iT邦新手 2 級 ‧ 2018-07-30 09:21:11

感覺內稽沒做好, 每個DB Owner都有嫌疑了, 快去做好內部稽核管理,不然以後也會一直發生, 對公司營運有害.

rogermou iT邦新手 5 級 ‧ 2018-07-30 09:40:17 檢舉

謝謝提醒,已經開始做了...

2
Luke
iT邦新手 3 級 ‧ 2018-07-30 09:35:41
SELECT * FROM sys.traces

從上面這個可以看出目前這台 SQL Server 在系統目前的預設 Trace 是寫入到 「C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_78.trc」內,預設最多保留五個,因此可以推論知道應該會有 log_74.trc ~ log_78.trc 這五個檔案。既然要取出這些紀錄檔的內容,在這裡我們可以使用「sys.fn_trace_gettable」來取得資料,利用類似以下的語法來處理,在這裡因為我們不確定是在甚麼時間點把資料庫給刪除的,因此我們先設定從 log_74.trc 這個檔案開始來找,搜尋還存在的這五個追蹤檔。

select * from 
   fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_74.trc',5)

是看您的喔~~不要照抄/images/emoticon/emoticon10.gif
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_74.trc

看更多先前的回應...收起先前的回應...
Luke iT邦新手 3 級 ‧ 2018-07-30 09:36:51 檢舉

但這樣出來的資訊有點多,也比較複雜一點,因此調整一下,只取出部分我們所要注意的欄位,像是資料庫名稱、物件名稱、應用程式、電腦名稱和時間,其他我就先暫時不去管他了。

WITH ObjectTypeMap(Value,ObjectType) AS
 (
     SELECT * FROM 
         ( Values ( 8259, 'Check Constraint' ),(8272,'Stored Procedure'),(8277, 'Table'),(8278,'View'),
                  (16964, 'Database'),(17235,'Schema' )
         ) as TypeMap(Value,ObjectType)
 )
 SELECT e.name,f.DatabaseName,m.ObjectType, f.ObjectName,f.ApplicationName, f.HostName,f.NTUserName,f.StartTime 
   FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_74.trc',5) f
   JOIN sys.trace_events e ON f.EventClass = e.trace_event_id AND f.EventClass in (46,47,164) and f.EventSubClass = 0
   JOIN ObjectTypeMap m ON f.ObjectType = m.Value
   WHERE DatabaseName = 'DEMO'  
   ORDER BY f.StartTime DESC,f.EventSequence DESC
Luke iT邦新手 3 級 ‧ 2018-07-30 09:38:04 檢舉

這樣我們就可以取得物件被異動和資料庫被刪除的相關資訊了。至於上面 SQL 中有一些物件編號對應的部分,如果需要詳細的相關資訊,可以參考 MSDN 上面「ObjectType Trace Event Column」上有更詳細的所有說明。

Luke iT邦新手 3 級 ‧ 2018-07-30 09:40:01 檢舉

覺得這樣太過於麻煩,要去記這些 DMV 實在有點煩人,不想去記這些指令的話,其實在 SQL Server 裡面,透過 SSMS 所提供的管理報表─「結構描述變更紀錄」,也可以有類似的功用。您可以按照你的狀況來決定要自己下指令還是用報表,反正不管黑貓白貓,會抓老鼠的就是好貓囉。

Luke iT邦新手 3 級 ‧ 2018-07-30 09:41:01 檢舉

https://ithelp.ithome.com.tw/upload/images/20180730/20096781B0Ckvq9Og7.png

rogermou iT邦新手 5 級 ‧ 2018-07-30 09:45:23 檢舉

好的,我一項一項試試,謝謝!!

暐翰 iT邦大師 1 級 ‧ 2018-07-30 10:03:31 檢舉

fn_trace只能查詢Drop、Create,不能查詢update
要查詢update需要使用sys.fn_dblog

rogermou iT邦新手 5 級 ‧ 2018-07-31 08:18:42 檢舉

謝謝,我試試看~~

0
humming
iT邦研究生 4 級 ‧ 2018-07-31 09:14:32

我覺得這種事情,等查清楚再說,我之前也碰到過類似的情形,被懷疑真的感覺很差。
任何一個還想在資訊業混下去的人,應該都不會幹這種自我毀滅的事情。
我自己的作法是
1.從備份主機直接將備份檔案還原成VM,查詢當時的系統LOG與ERP LOG。
2.查ERP系統的記錄,確定非USER誤改。
3.確定ERP系統本身沒有操作資料庫的介面,之前碰過兩個ERP都內建下SQL命令的介面。
4.查SQL SERVER的LOG紀錄,不過你說LOG紀錄是亂碼,就要先確定有沒有加密。
5.查 TRACE 紀錄,確認在懷疑的時間點內,有沒有人員登入的紀錄。

查完之後的結論MIS沒有登入紀錄、人員也沒有誤改資料,所以懷疑是ERP本身的BUG,
不過人都有先入為主的行為模式,所以MIS的下場多慘可想而知。

rogermou iT邦新手 5 級 ‧ 2018-08-01 08:13:36 檢舉

Hi Humming,

謝謝,您說的前三項我都已經查證過了才發此文,透過其他網友的指導,昨天也查過4 & 5,目前也沒有記錄,目前我在查詢系統中的Stored Procedure是否有寫錯的地方,謝謝!!

我要發表回答

立即登入回答