浮雲雅築: [研究]SQL Server資料庫稽核紀錄 (Database Audit Log)

2021年1月14日星期四

[研究]SQL Server資料庫稽核紀錄 (Database Audit Log)

[研究]SQL Server資料庫稽核紀錄 (DB Audit Log)

2021-01-14

因為某些緣故，臨時被要求查SQL Server 2019是否有提供這些稽核記錄，若無，如何設定、如何查詢，緊急查了一下，記錄下來。

一、資料庫的帳號變動(新增、刪除、修改)相關紀錄

之前被要求的是網站帳號 (或機敏資料表) 變動記錄，這次是資料庫帳號。

可用下面方法查詢

select * FROM sys.server_principals

或

SELECT *
FROM sys.server_principals AS pr   
JOIN sys.server_permissions AS pe   
    ON pe.grantee_principal_id = pr.principal_id;

(下圖) 查詢結果 (Click 圖片可看 100% 尺寸圖)

********************************************************************************

二、資料庫的帳號登出/登入行為相關紀錄

參考

[研究] SQL Server 2019 Audit 資料庫稽核 - 登入成功或失敗紀錄檢視

https://shaurong.blogspot.com/2020/06/sql-server-2019-audit.html

********************************************************************************

三、資料庫結構新增、刪除、修改等行為相關紀錄

sys. 追蹤 (Transact-sql) - SQL Server | Microsoft Docs

https://docs.microsoft.com/zh-tw/sql/relational-databases/system-catalog-views/sys-traces-transact-sql?view=sql-server-ver15

SELECT * FROM sys.traces ;

(上圖)max_files 顯示有5份logs

查詢 Log 中資訊

select * from

  fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_14.trc',5)
  where textdata is not null 
  ORDER BY StartTime DESC;

查詢有關 TestDB 的資訊

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\MSSQL15.MSSQLSERVER\MSSQL\Log\log_14.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 = 'TestDB'  
  ORDER BY f.StartTime DESC,f.EventSequence DESC