SQLServer 創建伺服器和資料庫級別審計

来源:https://www.cnblogs.com/chenmh/archive/2018/01/16/8296446.html
-Advertisement-
Play Games

一、概述 在上一篇文章中已經介紹了審計的概念；本篇文章主要介紹如何創建審計，以及該收集哪些審核規範。 二、常用的審核對象 2.1、伺服器審核對象 1.FAILED_LOGIN_GROUP( Audit Login Failed Event Class) 指示主體嘗試登錄到 SQL Server，等效 ...


一、概述

在上一篇文章中已經介紹了審計的概念；本篇文章主要介紹如何創建審計，以及該收集哪些審核規範。

 

二、常用的審核對象

2.1、伺服器審核對象

1.FAILED_LOGIN_GROUP( Audit Login Failed Event Class)

指示主體嘗試登錄到 SQL Server，等效於 Audit Login Failed Event Class，
比如：登入失敗的操作

2.SERVER_OBJECT_CHANGE_GROUP(Audit Server Object Management)

針對伺服器對象執行 CREATE、ALTER 或 DROP 操作時將引發此事件。等效於 Audit Server Object Management 事件類。
比如：刪除鏈接伺服器對象等凌駕於資料庫級別以上的對象(許可權除外)。

3.SERVER_ROLE_MEMBER_CHANGE_GROUP

向固定伺服器角色添加登錄名或從中刪除登錄名時將引發此事件, 此事件由 sp_addsrvrolemember 和 sp_dropsrvrolemember 存儲過程引發。 等效於 Audit Add Login to Server Role Event Class。
比如：授予、撤銷伺服器角色等

4.SERVER_PRINCIPAL_CHANGE_GROUP

創建、更改或刪除伺服器主體時將引發此事件
比如：創建刪除登入名等

5.SUCCESSFUL_LOGIN_GROUP

指示主體已成功登錄到 SQL Server。 此類中的事件由新連接引發或由連接池中重用的連接引發。 等效於 Audit Login Event Class。
註意:每一個連接會話都會記錄，開啟SUCCESSFUL_LOGIN_GROUP會產生很多的記錄

2.2、審核自身審核對象

AUDIT_CHANGE_GROUP

2.3、資料庫審核對象

DDL相關

1.DATABASE_PRINCIPAL_CHANGE_GROUP

在資料庫中創建、更改或刪除主體（如用戶）時，將引發此事件。 等效於 Audit Database Principal Management Event Class。
比如：創建刪除登入名等

2.DATABASE_ROLE_MEMBER_CHANGE_GROUP

向資料庫角色添加登錄名或從中刪除登錄名時將引發此事件。 此事件類與 sp_addrolemember、sp_changegroup 和 sp_droprolemember 存儲過程一起使用。等效於 Audit Add Member to DB Role 事件類
比如：授予、撤銷伺服器角色等

3.DATABASE_CHANGE_GROUP(Audit Database Management 事件)

創建、更改或刪除資料庫時將引發此事件。創建、更改或刪除任何資料庫時均將引發此事件。等效於 Audit Database Management 事件類。
比如：刪除創建資料庫、修改資料庫屬性等資料庫本身的修改操作

4.DATABASE_OBJECT_CHANGE_GROUP(Audit Database Object Management 事件)

針對資料庫對象（如架構）執行 CREATE、ALTER 或 DROP 語句時將引發此事件。創建、更改或刪除任何資料庫對象時均將引發此事件。等效於 Audit Database Object Management 事件類。
比如：Serive Broker相關對象、存儲、安全等凌駕於用戶創建的對象以上的對象(許可權除外)。

5.SCHEMA_OBJECT_CHANGE_GROUP( Audit Schema Object Management Event Class)

針對架構執行 CREATE、ALTER 或 DROP 操作時將引發此事件。等效於 Audit Schema Object Management 事件類。此事件針對架構對象引發。等效於 Audit Object Derived Permission 事件類。任何資料庫的任何架構發生更改時，均將引發此事件。等效於 Audit Statement Permission 事件類。
比如：表、存儲過程、視圖、函數、架構等對象。

DML相關

SELECT、DELETE、INSERT、UPDATE

三、創建服務審核

1.創建審核

USE [master]
GO
----創建審核,命名規範AuditServer_描述
CREATE SERVER AUDIT [AuditServer_All]
TO FILE 
(    FILEPATH = N'D:\Audit\AuditServer_All'  ----文件路徑
    ,MAXSIZE = 2 GB                     ----文件最大大小，單位可以是MB、GB、TB
    ,MAX_FILES = 2147483647             ----最大文件數，最大2147483647也就是無限制
-----,MAX_ROLLOVER_FILES = 2147483647   ----最大滾動更新文件數，最大2147483647也就是無限制，不能和MAX_FILES一起配置
    ,RESERVE_DISK_SPACE = ON            ----保留磁碟空間
)
WITH
(    QUEUE_DELAY = 1000                  ----隊列延時，預設1S
    ,ON_FAILURE = CONTINUE              ----審核失敗繼續，還可以指定SHUTDOWN關閉資料庫伺服器，但是必須有相關許可權
)

GO

註意：預設創建審核是禁用的，必須手動啟用，同樣修改審核之前也必須先禁用然後再修改。路徑‘D:\Audit\AuditServer_All’必須事先創建好。

 

2.啟用和禁用審核

---啟用
USE [master]
GO
ALTER SERVER AUDIT AuditServer_All WITH(STATE=ON);
---禁用
USE [master]
GO
ALTER SERVER AUDIT AuditServer_All WITH(STATE=OFF);

3.創建審核規範

----創建伺服器審核規範,命名規範:AuditSpecification_描述（和審核描述保持一致）
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION AuditSpecification_All
FOR SERVER AUDIT AuditServer_All
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
GO

審核的事件包括：登入失敗的操作、伺服器級別對象的操作、創建刪除登入用戶、伺服器角色的授予和撤銷、對審核配置的操作。還有一些其它的審核類型暫時不做審計。其實這裡也可以將將資料庫級別的審核操作創建到伺服器級別下，這樣的話

4.啟用和禁用審核規範

--啟用
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=ON);
GO
---禁用,註意作業會影響禁用
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=OFF);

5.測試

----創建登入名,並授予sysadmin角色
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [test]
GO

四、創建資料庫審核

1.創建資料庫審核 

USE [master]
GO
----創建審核,審核只能創建在伺服器級別，命名規範AuditDatabase_資料庫名_描述
CREATE SERVER AUDIT [AuditDatabase_chenmh_AllObjectChange]
TO FILE 
(    FILEPATH = N'D:\Audit\AuditDatabase_chenmh_AllObjectChange'    ----文件路徑
    ,MAXSIZE = 2 GB                     ----文件最大大小，單位可以是MB、GB、TB
    ,MAX_FILES = 2147483647             ----最大文件數，最大2147483647也就是無限制
-----,MAX_ROLLOVER_FILES = 2147483647   ----最大滾動更新文件數，最大2147483647也就是無限制，不能和MAX_FILES一起配置
    ,RESERVE_DISK_SPACE = ON            ----保留磁碟空間
)
WITH
(    QUEUE_DELAY = 1000                  ----隊列延時，預設1S
    ,ON_FAILURE = CONTINUE              ----審核失敗繼續，還可以指定SHUTDOWN關閉資料庫伺服器，但是必須有相關許可權
)

GO
---啟用
USE [master]
GO
ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=ON);
---禁用
USE [master]
GO
--ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=OFF);

註意：資料庫審核也是創建在伺服器級別，資料庫審核規範是創建在具體的資料庫下。

2.創建資料庫審核規範

CREATE DATABASE Audit;
GO
----創建伺服器審核規範,命名規範：AuditSpecification_描述（和審核描述保持一致）
USE [Audit]
GO
CREATE DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange
FOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange
    ---DDL相關操作
    ADD (DATABASE_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ---DML相關操作,PUBLIC代表所有用戶
    -----ADD (SELECT ON SCHEMA::[dbo] BY PUBLIC),
    ADD (DELETE ON SCHEMA::[dbo] BY PUBLIC),
    ADD (INSERT ON SCHEMA::[dbo] BY PUBLIC),
    ADD (UPDATE ON SCHEMA::[dbo] BY PUBLIC);
GO

--啟用
USE [Audit]
GO
ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);
---禁用,註意作業會影響禁用
USE [Audit]
GO
--ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=OFF);

3.測試

USE [Audit]
GO
CREATE TABLE a(id int not null);
GO
ALTER TABLE a ADD name varchar(10);
GO
INSERT INTO a values(1,'a')
GO
DELETE FROM a WHERE id=1;
GO
DROP TABLE a;

備註：審計記錄的日期和使用GETDATE()查詢的時間8個小時

五、查詢

----查詢審核日誌
select * from  sys.fn_get_audit_file('D:\Audit\AuditServerFile_66D8F97A-B495-4CB4-83EA-564D1ECF9988_0_131604628132230000.sqlaudit',DEFAULT, DEFAULT) AS a


--伺服器審核相關視圖
SELECT * FROM SYS.server_audits
SELECT * FROM sys.server_audit_specifications
SELECT * FROM sys.server_audit_specification_details

SELECT sa.name as AuditName,
sa.type_desc AS StoreType,
sas.name AS AuditSpecificationsName,
sasd.audit_action_id,
sasd.audit_action_name,
sasd.class_desc as AuditGrade
FROM SYS.server_audits sa INNER JOIN sys.server_audit_specifications sas ON sa.audit_guid=sas.audit_guid
INNER JOIN  sys.server_audit_specification_details sasd ON sas.server_specification_id=sasd.server_specification_id
LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc


--資料庫審核規範視圖
SELECT * FROM chenmh.sys.database_audit_specifications
SELECT * FROM chenmh.sys.database_audit_specification_details

SELECT sa.name as AuditName,
sa.type_desc AS StoreType,
sas.name AS AuditSpecificationsName,
sasd.audit_action_id,
sasd.audit_action_name,
sasd.class_desc as AuditGrade
FROM SYS.server_audits sa INNER JOIN chenmh.sys.database_audit_specifications sas ON sa.audit_guid=sas.audit_guid
INNER JOIN  chenmh.sys.database_audit_specification_details sasd ON sas.database_specification_id=sasd.database_specification_id
LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc

----
SELECT * FROM  sys.dm_audit_actions  WHERE action_id='DL'

六、刪除

--刪除伺服器審核規範，先禁用才能刪除
USE [master]
GO
ALTER SERVER  AUDIT SPECIFICATION AuditSpecification_All WITH (STATE=OFF)
GO
DROP SERVER AUDIT SPECIFICATION AuditSpecification_All
GO

--刪除伺服器審核對象，先禁用才能刪除
USE [master]
ALTER SERVER AUDIT AuditServer_All WITH (STATE=OFF)
GO
DROP SERVER AUDIT AuditServer_All;

七、總結

審核是資料庫規範的一部分，在安全審計方面也非常的重要，建議生產系統都必須創建審核。

 

 

參考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

 

 

 

備註：

    作者：pursuer.chen

    博客：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責任的權利。

《歡迎交流討論》

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Centos上安裝jdk版本出錯的問題
    今天買了個阿裡雲的伺服器，於是手動安裝了一遍JavaWeb運行環境，首先安裝jdk與配置jdk就遇到了問題。 我下載的是解壓版的jdk-8u151-linux-i586.tar.gz。安裝和配置到是蠻簡單。和在Windows幾乎一樣。接下來問題就來了。 運行 java -version 來測試版本時 ...
  • 串口通訊（DMA模式）
    在高級語言中，I/O 流輸入（input）操作一般都要求指定要讀取的數據的最大長度（位元組數）。當接收到至少1位元組、最多所指定的位元組數時，函數返回。 STM32 串口接收數據時，HAL API 要求指定數據長度。但無論輪詢、中斷或是DMA方式，都必須完整地接收到這麼多位元組，程式流程才繼續。如何接收變長 ...
  • 為何要部署IPV6
    ·IPv4的局限性： 1.地址空間的局限性：IP地址空間的危機由來已久，並正是升級到IPv6的主要動力。 2.安全性：IPv4在網路層沒有安全性可言，安全性一直被認為是由網路層以上的層負責。 3.自動配置：對於IPv4節點的配置比較複雜，讓很多普通用戶無所適從。 4.NAT：破壞了Internet端 ...
  • 1、Linux命令隨筆
    來源： http://www.cnblogs.com/hackerer/p/5100208.html null ...
  • 資料庫設計三大範式（簡單易懂）
    資料庫設計的三大範式 為了建立冗餘較小、結構合理的資料庫，設計資料庫時必須遵循一定的規則。在關係型資料庫中這種規則就叫做範式。 範式就是符合某一種設計要求的總結，要想設計一個結構合理的關係型資料庫，必須滿足一定的範式。 在實際開發中最常見的設計範式有三個： 1、第一範式*（確保每列保持原子性） 第一 ...
  • mysql的select查詢語句
    1.簡單查詢 mysql> select * from students where sname like 'o%'; + + + + + | id | sname | sex | tid | + + + + + | 0000000001 | orna | 男 | NULL | + + + + + ...
  • mysql的表和約束操作
    在創建表是預設為加上數據引擎和字元集，如創建一個student表，代碼如下： 插入數據(insert和replace) 1.insert命令直接在表的插入一條記錄。 2.replace命令在插入記錄時判斷主鍵是否相同，相同就修改，否則插入一條新記錄。 用select 結果集創建一個表 修改表名 有兩 ...
  • 資料庫：MySQL查詢語句練習題
    Sutdent表的定義 欄位名 欄位描述 數據類型 主鍵 外鍵 非空 唯一 自增 Id 學號 INT(10) 是 否 是 是 是 Name 姓名 VARCHAR(20) 否 否 是 否 否 Sex 性別 VARCHAR(4) 否 否 否 否 否 Birth 出生年份 YEAR 否 否 否 否 否 D ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類