soar ddl审核规范
介绍 (Introduction)
In a previous article “SQL Server Audit feature – discovery and architecture“, we’ve seen the basics to build an auditing system based on SQL Server Audit feature. In the introduction, we talked a little bit about the « General Data Protection Regulation » a.k.a. GDPR that allows personal data circulation (in Europe) that is a subject in the mouth of a lot of IT professionals.
在上一篇文章“ SQL Server审核功能–发现和体系结构 ”中,我们已经了解了基于SQL Server审核功能构建审核系统的基础。 在引言中,我们讨论了“通用数据保护法规”(又称GDPR),该法规允许(在欧洲)流通个人数据,这是很多IT专业人员都想知道的话题。
GDPR makes audit a requirement and that’s a sufficient reason to take a look at different solution to achieve the same goal. For each one, we can pinpoint advantages and disadvantages of adopting it and eventually take a final decision to define which the «best» tool is.
GDPR将审核作为一项要求,这是充分考虑采用不同解决方案以实现同一目标的充分理由。 对于每个工具,我们都可以查明采用该工具的优缺点,并最终做出最终决定以定义“最佳”工具。
In this article, we will take a close look at three ways to achieve the following goal: audit all DDL activity. We will review implementation of this goal using:
在本文中,我们将仔细研究实现以下目标的三种方法:审核所有DDL活动。 我们将使用以下方法审查该目标的实施情况:
- DDL Triggers, DDL触发器
- SQL Trace SQL跟踪
- SQL Server Audits (which are built on top of SQL Server审核 (基于Extended Events) 扩展事件构建)
I recommend users interested in security and particularly auditing to read Minette Steynberg’s article on SQLShack.com entitled “Creating a successful auditing strategy for your SQL Server databases“.
我建议对安全性特别是审计感兴趣的用户阅读Minette Steynberg在SQLShack.com上的文章“ 为SQL Server数据库创建成功的审计策略 ”。
As you could expect, in the following sections, we will then implement the example of auditing DDL activity at the server and database levels in three manners using DDL Triggers, default SQL Trace mining and finally SQL Server Audits. This will allow us to enlist pros and cons to the adoption of each of these three methods, at least in my point of view. Once we are done with the example, we could conclude on the best choice from my own perspective.
如您所料,在以下各节中,我们将使用DDL触发器,默认SQL Trace挖掘以及最后SQL Server审计,以三种方式在服务器和数据库级别实现审计DDL活动的示例。 至少在我看来,这将使我们能够对采用这三种方法中的每一种都有利有弊。 一旦完成示例,就可以从我自己的角度得出最佳选择。
一个具体的例子:审核DDL事件 (A concrete example: auditing DDL events)
In my opinion, there is no fixed rule that should apply once for all, for every company, for every application or software. It’s actually the responsibility of the audit implementer to choose the appropriate solution for a given situation. After all, the only question his manager will ask him is to know whether there is or not an audit on a particular aspect of SQL Server.
我认为,没有固定的规则适用于所有公司,每个应用程序或软件一次。 审计实施人员实际上有责任为给定情况选择适当的解决方案。 毕竟,经理所要问的唯一问题是知道是否对SQL Server的特定方面进行了审核。
But, there are so many elements that can influence this implementation:
但是,有太多因素会影响此实现:
- Degree of knowledge and experience of the audit implementer. We tend to choose the solution that is familiar. 审计实施者的知识和经验程度。 我们倾向于选择熟悉的解决方案。
- Application environment or context. For instance: SQL Server version and edition or available disk space that we can dedicate to auditing. 应用程序环境或上下文。 例如:SQL Server版本和版本或我们可以专用于审核的可用磁盘空间。
- Business requirements and needs for auditing. Sometimes, they are not justified, but have to be implemented, sometimes it’s the contrary like enabling C2 auditing for non-C2 certified environments. 业务需求和审计需求。 有时,它们是没有道理的,但必须加以实施,有时恰恰相反,例如为非C2认证的环境启用C2审核。
- Available resources: number of persons to assign, time, efforts, budget… 可用资源:分配人数,时间,工作量,预算…
But this is not the subject here, so to convince you that « All Roads Lead to Rome », let’s implement DDL Auditing using three different techniques (included SQL Audits). We will then review them and try to find advantages to use SQL Audits in comparison to the two other techniques.
但这不是这里的主题,因此,为了使您确信“通向罗马的所有道路”,让我们使用三种不同的技术(包括SQL审计)来实施DDL审计。 然后,我们将对它们进行审查,并尝试寻找与其他两种技术相比使用SQL审核的优势。
Note
Alternatively, we can use ApexSQL Log to perform DDL auditing.
注意
另外,我们可以使用ApexSQL日志执行DDL审核。
Auditing DDL Events with triggers
使用触发器审核DDL事件
As we have seen in the presentation of the architecture of SQL Audits, there are mainly two kinds of DDL events: those which are database-related and those which are server-related. It’s the same for DDL triggers! So we will have a server ddl trigger and multiple database ddl triggers.
正如我们在SQL审计体系结构的介绍中所看到的,主要有两种DDL事件:与数据库相关的事件和与服务器相关的事件。 DDL触发器也一样! 因此,我们将有一个服务器ddl触发器和多个数据库ddl触发器。
As most of us already know, DDL Triggers are programmable objects in SQL Server. It means we can do more than just auditing with them. On technet, you will read the following list of use cases that DDL triggers are fit to accomplish:
众所周知, DDL触发器是SQL Server中的可编程对象。 这意味着我们可以做的不仅仅是审核他们。 在technet上 ,您将阅读DDL触发器适合完成的用例的以下列表:
DDL triggers can be used for administrative tasks such as auditing and regulating database operations.
DDL触发器可用于管理任务,例如审核和调节数据库操作。
Use DDL triggers when you want to do the following:
当您想执行以下操作时,请使用DDL触发器:
- You want to prevent certain changes to your database schema. 您要防止对数据库架构进行某些更改。
- You want something to occur in the database in response to a change in your database schema. 您希望数据库中发生某些事情以响应数据库模式的更改。
- You want to record changes or events in the database schema. 您想要在数据库架构中记录更改或事件。
Each DDL trigger implied in the audit should perform as follows:
审核中隐含的每个DDL触发器应执行以下操作:
So, one who wants to audit DDL events with this solution must define which information he wants to collect and where he wants to store it. For demonstration purpose, we will keep it simple and say that we will collect the following information and store it to tables:
因此,想要使用此解决方案审核DDL事件的人必须定义他想收集哪些信息以及他想将其存储在何处。 出于演示目的,我们将使其保持简单,并说我们将收集以下信息并将其存储到表中:
- The moment when the event occurred 事件发生的那一刻
- The login name 登录名
- The client computer name and application 客户端计算机名称和应用程序
- The current database name of the session 会话的当前数据库名称
- The kind of event that occurred 发生的那种事件
- The information about the object (SchemaName, ObjectName) – We could also include a « SubObjectName » information in the list in order to handle, for example, events related to table partitionning management 有关对象的信息(SchemaName,ObjectName)–我们还可以在列表中包含«SubObjectName»信息,以便处理例如与表分区管理有关的事件
- The T-SQL statement that fired the trigger 触发触发器的T-SQL语句
- Optionally, we could also keep the complete event descriptor which is of XML data type. (可选)我们还可以保留XML数据类型的完整事件描述符。
In order to get this information, we will use the EVENTDATA built-in function which returns an XML describing the event that has just occurred. The returned value of this function is the optional information listed above. As we get an XML data type, we can query it using XQuery.
为了获得此信息,我们将使用EVENTDATA内置函数,该函数返回描述刚刚发生的事件的XML。 此函数的返回值是上面列出的可选信息。 获得XML数据类型后,可以使用XQuery对其进行查询。
You will find below two examples of what we get back using this function. The first one is extracted from a server-level DDL event and the next one from a database-level DDL event.
您将在下面找到两个使用此功能后得到的结果的示例。 第一个是从服务器级DDL事件中提取的,第二个是从数据库级DDL事件中提取的。
<EVENT_INSTANCE>
<EventType>ALTER_VIEW</EventType>
<PostTime>2015-08-19T14:51:45.500</PostTime>
<SPID>60</SPID>
<ServerName>TestServer</ServerName>
<LoginName>MGSBUSINESS\Jefferson </LoginName>
<UserName>dbo</UserName>
<DatabaseName>DbaTools</DatabaseName>
<SchemaName>maintenance</SchemaName>
<ObjectName>CleanupSettings</ObjectName>
<ObjectType>VIEW</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>
ALTER VIEW [maintenance].[CleanupSettings]
AS
SELECT
DbName,
ObjectOwner,
ObjectName,
ObjectType,
CleanupEnabled,
CleanupColumn,
CleanupType,
CleanupParam1,
CleanupParam2,
CleanupParam3,
CleanupParam4
FROM
maintenance.MaintenanceSettings
;
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
As we can see, we don’t get exactly the same structure. This means there are two different Xml Schema Definitions and we can specialize our change logs.
如我们所见,我们没有完全相同的结构。 这意味着有两个不同的Xml模式定义,我们可以专门化更改日志。
The following table structure should be defined in order to store data about DDL events:
为了存储有关DDL事件的数据,应定义以下表结构:
CREATE TABLE [auditlog].[ChangeLog](
[ChangeLogID] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NULL,
[LoginName] [sysname] NULL,
[ComputerName] [sysname] NULL,
[ProgramName] [nvarchar](255) NULL,
[DBName] [sysname] NOT NULL,
[SQLEvent] [sysname] NOT NULL,
[SchemaName] [sysname] NULL,
[ObjectName] [sysname] NULL,
[SQLCmd] [nvarchar](max) NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED (
[ChangeLogID] ASC
)
) ;
And the following trigger code should be defined:
并且应该定义以下触发代码:
BEGIN
DECLARE @EventDataXml XML;
DECLARE @SchemaName SYSNAME;
DECLARE @ObjectName SYSNAME;
DECLARE @EventType SYSNAME;
-- getting back event data
SET @EventDataXml = EVENTDATA();
SELECT
@EventType = @EventDataXml.value(''(/EVENT_INSTANCE/EventType)[1]'', ''SYSNAME'')
@SchemaName = @EventDataXml.value(''(/EVENT_INSTANCE/SchemaName)[1]'', ''SYSNAME'')
@ObjectName = @EventDataXml.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''SYSNAME'')
;
INSERT [auditlog].[ChangeLog] (
[CreateDate],[LoginName], [ComputerName],[ProgramName],[DBName],[SQLEvent], [SchemaName], [ObjectName], [SQLCmd], [XmlEvent]
)
SELECT
GETDATE(),
SUSER_NAME(),
HOST_NAME(),
PROGRAM_NAME(),
@EventDataXml.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''SYSNAME''),
@EventType,
@SchemaName,
@ObjectName,
@EventDataXml.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''NVARCHAR(MAX)''),
@EventDataXml
;
END;
As explained above, there are DDL events on server scope and on database scope and it’s not possible to create a single trigger that will do the job. Actually, you will need to create previous table and trigger multiple times:
如上所述,在服务器范围和数据库范围上都有DDL事件,并且不可能创建一个可以完成此任务的触发器。 实际上,您将需要创建上一个表并多次触发:
- A server DDL change log table and a server DDL trigger using the following CREATE statement:
CREATE TRIGGER [Server_DDL_Audit] ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS AS …
- 使用以下CREATE语句的服务器DDL更改日志表和服务器DDL触发器:
CREATE TRIGGER [Database_DDL_Audit] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS …
Note:
注意:
- You can create a central table to store DDL events data for all audited databases, but you will need to create a database trigger in every database you want to audit. Plus, you will eventually need to review login mappings and user permissions as well. 您可以创建一个中央表来存储所有审计数据库的DDL事件数据,但是您需要在要审计的每个数据库中创建一个数据库触发器。 另外,您最终还需要查看登录映射和用户权限。
-
- ServerChangeLog for server-related DDL events ServerChangeLog用于服务器相关的DDL事件
- DatabaseChangeLog for database-related DDL events DatabaseChangeLog用于数据库相关的DDL事件
You should also adjust the insert statement in triggers so that it uses those names.
您还应该在触发器中调整插入语句,以便它使用这些名称。
We’ve seen the first method. Let’s review some its advantages and disadvantages.
我们已经看到了第一种方法。 让我们回顾一下它的优点和缺点。
Advantages | Disadvantages |
Flexibility and « storage-friendly »
| Additional management tasks
|
优点 | 缺点 |
灵活性和“便于存储”
| 其他管理任务
|
We could modify this trigger-based approach to output to a file, but it would require either to assign advanced permission or to be enabled xp_cmdshell and it’s not necessarily the best approach…
我们可以修改这种基于触发器的方法来输出到文件,但是它需要分配高级权限或启用xp_cmdshell,并且不一定是最好的方法……
Let’s now do the same using default SQL Trace.
现在让我们使用默认SQL跟踪执行相同的操作。
Auditing DDL events using default trace
使用默认跟踪审核DDL事件
The SQL Server default trace is an old functionality (since at least SQL Server 2005) that provides the ability to track some key events, primarily related to the configuration options. This feature is considered depreciated and should not be used in new developments. We should use Extended Events instead.
SQL Server默认跟踪是一项旧功能(至少从SQL Server 2005开始),提供了跟踪某些主要与配置选项有关的关键事件的功能。 该功能被认为已弃用,不应在新的开发中使用。 我们应该改用扩展事件。
The default trace tracks DDL Changes, password changes, server configuration and database settings changes or file growth…
默认跟踪跟踪DDL更改,密码更改,服务器配置和数据库设置更改或文件增长…
It’s enabled when default trace enabled Server Configuration Option is set to 1. So, when implementing auditing using default trace, we must ensure that this setting is always up and running.
当启用默认跟踪的“服务器配置选项”设置为1时, 将启用此功能 。因此,在使用默认跟踪实施审核时,我们必须确保此设置始终处于运行状态。
Another point that has to be mentioned is that the output of this feature is limited to a set of 5 files of maximum 20 MB which are rolled over. This means that to keep a suitable history, we must take a copy of trace files as regularly as possible.
必须提到的另一点是,此功能的输出仅限于5个最大20 MB的文件集,这些文件将被翻转。 这意味着要保留适当的历史记录,我们必须尽可能定期地获取跟踪文件的副本。
This means that we do not need a lot of work to audit DDL events. We “just” need to:
这意味着我们不需要太多工作来审核DDL事件。 我们“只是”需要:
- Enable default trace. 启用默认跟踪。
- Create a scheduled task that will ensure the functionality is still on and adjust when it’s not the case. 创建一个计划任务,以确保功能仍然可用,并在情况并非如此时进行调整。
- Build and regularly schedule a script that will copy audit files. 构建并定期计划将复制审核文件的脚本。
Until now, we’ve just talked about writing and storing default trace files. Let’s now talk about how to read from a trace file.
到目前为止,我们仅讨论了如何编写和存储默认跟踪文件。 现在让我们讨论一下如何从跟踪文件读取。
Reading from a trace file requires the use of fn_trace_gettable built-in function:
从跟踪文件读取需要使用fn_trace_gettable内置函数:
USE AdventureWorks2012;
GO
SELECT *
FROM fn_trace_gettable('c:\temp\mytrace.trc', default);
GO
For default trace, the following query should fit the basic need to know that « something happened »:
对于默认跟踪,以下查询应符合了解“发生了什么事”的基本需求:
SELECT
TE.name AS [EventName],
TT.DatabaseName ,
TT.DatabaseID ,
TT.ApplicationName ,
TT.LoginName ,
TT.Duration ,
TT.StartTime ,
TT.EndTime
FROM sys.fn_trace_gettable(
CONVERT(VARCHAR(4000),
(
SELECT TOP 1
f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT
) TT
JOIN
sys.trace_events TE
ON TT.EventClass = TE.trace_event_id
ORDER BY TT.StartTime desc ;
This will give us the following kind of results:
这将为我们带来以下结果:
As we did for DDL Trigger auditing, let’s review some advantages and disadvantages of auditing using default trace.
正如我们对DDL触发器审核所做的那样,让我们回顾一下使用默认跟踪进行审核的优点和缺点。
Advantages | Disadvantages |
« User-friendly »
| Additional management tasks
|
优点 | 缺点 |
“ 方便使用的 ”
| 其他管理任务
|
Auditing DDL events using SQL Audits
使用SQL审核审核DDL事件
Reminder
提醒
SQL Server Audits fundamentals have been introduced in my previous article. For readers who don’t have time to read this full article, let’s review a summary of its contents (it’s actually taken out of this article).
我的上一篇文章介绍了SQL Server审核的基础知识。 对于没有时间阅读这篇全文的读者,让我们回顾一下其内容摘要(实际上摘自本文)。
SQL Server Audit takes advantage of the Extended Events feature and uses events groups as input. We also refer to these groups as Audit Action groups. Audit action groups relate to a « depth level » in SQL Server: either they are server-, or database- or audit- related. Audit action groups are mapped to a server audit specification or a database audit specification, primarily based on our audit policy.
SQL Server审核利用扩展事件功能并将事件组用作输入。 我们还将这些组称为“审核行动”组。 审核操作组与SQL Server中的“深度级别”相关:它们与服务器,数据库或审核相关。 审核操作组主要根据我们的审核策略映射到服务器审核规范或数据库审核规范。
All these components together form what we call a server audit. The product of an audit must be stored somewhere. SQL Server audit has some flexibility for it and provides three different kinds of output, which are known as audit targets. Audit target is either a file on the server host, the application log or the security log.
所有这些组件共同构成了我们所谓的服务器审核。 审核产品必须存放在某个地方。 SQL Server审核具有一定的灵活性,并提供三种不同的输出,称为审核目标。 审核目标是服务器主机上的文件,应用程序日志或安全日志。
You will find below a diagram that summarizes the architecture of SQL Server Audits.
您将在下面的图表中总结SQL Server审核的体系结构。
Back to the example
回到例子
We will first create our audit object. It will be called Audit-Demo-DDL. Here is the T-SQL statement to do so. Alternately, you can use SSMS.
我们将首先创建审核对象。 它将被称为Audit-Demo-DDL 。 这是这样做的T-SQL语句。 或者,您可以使用SSMS。
CREATE SERVER AUDIT [Audit-Demo-DDL]
TO FILE
( FILEPATH = N'C:\Windows\Temp'
,MAXSIZE = 64 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
This object is created with an OFF status. It should not appear in the list returned by the following query:
创建的对象处于关闭状态。 它不应出现在以下查询返回的列表中:
select * from sys.dm_server_audit_status
Now, let’s create the server audit specification using the following statement:
现在,让我们使用以下语句创建服务器审核规范:
CREATE SERVER AUDIT SPECIFICATION [Spec-Demo-DDL]
FOR SERVER AUDIT [Audit-Demo-DDL]
ADD (DATABASE_CHANGE_GROUP), -- database is created, altered, or dropped
ADD (DATABASE_OBJECT_CHANGE_GROUP), -- CREATE, ALTER, or DROP statement is executed on database objects, such as schemas
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), --
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), -- a GRANT, REVOKE, or DENY has been issued for database objects, such as assemblies and schemas
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), -- use of ALTER AUTHORIZATION statement to change the owner of a database, and the permissions that are required to do that are checked
ADD (DATABASE_PERMISSION_CHANGE_GROUP), -- GRANT, REVOKE, or DENY is issued for a statement permission by any principal in SQL Server
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), -- raised when principals, such as users, are created, altered, or dropped from a database.
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), -- a login is added to or removed from a database role. This event class is raised for the sp_addrolemember, sp_changegroup, and sp_droprolemember stored procedures
ADD (LOGIN_CHANGE_PASSWORD_GROUP), -- a login password is changed by way of ALTER LOGIN statement or sp_password stored procedure
ADD (SERVER_OBJECT_CHANGE_GROUP), -- CREATE, ALTER, or DROP operations on server objects
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), -- owner is changed for objects in the server scope.
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), -- GRANT, REVOKE, or DENY is issued for a server object permission by any principal in SQL Server
ADD (SERVER_PERMISSION_CHANGE_GROUP), -- GRANT, REVOKE, or DENY is issued for permissions in the server scope, such as creating a login.
ADD (SERVER_PRINCIPAL_CHANGE_GROUP), -- server principals are created, altered, or dropped.
-- a principal issues the sp_defaultdb or sp_defaultlanguage stored procedures or ALTER LOGIN statements
-- sp_addlogin and sp_droplogin stored procedures.
-- sp_grantlogin or sp_revokelogin stored procedures
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP) -- a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures.
WITH (STATE=ON)
Now, we can enable the server audit:
现在,我们可以启用服务器审核:
ALTER SERVER AUDIT [Audit-Demo-DDL] WITH (STATE=ON) ;
Now, let’s try it out! You will find below a little script which creates a table, adds a primary key constraint, inserts some data and drops the table.
现在,让我们尝试一下! 您将在下面找到一个小的脚本,该脚本创建一个表,添加一个主键约束,插入一些数据并删除该表。
use testJEL;
CREATE TABLE dbo.TestAuditSettings (
IdCol BIGINT IDENTITY(1,1) NOT NULL,
ValCol VARCHAR(256)
);
alter table dbo.TestAuditSettings
add CONSTRAINT PK_TestAuditSettings
PRIMARY KEY CLUSTERED (
IdCol
)
;
insert into dbo.TestAuditSettings (
ValCol
)
select *
FROM (
VALUES
('First'),
('Second'),
('Third')
) as vals (
cols
)
select * from dbo.TestAuditSettings
DROP TABLE dbo.TestAuditSettings
USE [master]
GO
CREATE LOGIN [TestAudit] WITH PASSWORD=N'5rKenvzzgIlynLjdI4krhuO7kXT1tiEjXFZWlVVJoi4=', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER LOGIN [TestAudit] DISABLE
DROP LOGIN [TestAudit] ;
GO
I said previously that the sys. server_audit_status was a particularly useful table. Here is the first usage of this great view: getting the location of the current server audit file. This is performed with the following query:
我之前说过该系统。 server_audit_status是一个特别有用的表。 这是这种出色视图的首次使用:获取当前服务器审核文件的位置。 这是通过以下查询执行的:
select
status_desc, audit_file_path
From sys.dm_server_audit_status
where name = 'Audit-Demo-DDL'
As I get back the audit_file_path, I am able to read this file and get the list of actions that have been audited so far using sys.fn_get_audit_file function.
当我取回audit_file_path时,我能够读取该文件并使用sys.fn_get_audit_file函数获取到目前为止已审核的操作列表。
We can by the way put the value of the audit_file_path column into a variable. So the query to read current audit file looks like this:
顺便说一下,我们可以将audit_file_path列的值放入变量中。 因此,读取当前审核文件的查询如下所示:
DECLARE @AuditFilePath VARCHAR(8000);
select
@AuditFilePath = audit_file_path
From sys.dm_server_audit_status
where name = 'Audit-Demo-DDL'
select * from sys.fn_get_audit_file(@AuditFilePath,default,default)
And here is what I get back when I run this statement:
这是我运行此语句后得到的结果:
Apparently, the ALTER TABLE and DROP TABLE statement have not been tracked… We should maybe add the SCHEMA_OBJECT_CHANGE_GROUP action group or create a database audit specification if this option is available…
显然,未跟踪ALTER TABLE和DROP TABLE语句…如果此选项可用,我们应该添加SCHEMA_OBJECT_CHANGE_GROUP操作组或创建数据库审核规范…
Anyway, this shows us we must really take care of the action groups we add to a server audit and also test that this audit does the job we think it should do.
无论如何,这表明我们必须真正照顾我们添加到服务器审核中的操作组,并测试该审核是否完成了我们认为应该做的工作。
Let’s clean up our stuff. You will find below the statements to drop the objects we created in this section.
让我们收拾东西。 您将在语句下面找到要删除在本节中创建的对象。
use master;
alter server audit specification [Spec-Demo-DDL] with (state = off);
drop server audit specification [Spec-Demo-DDL]
alter server audit [Audit-Demo-DDL] with (state = off)
drop server audit [Audit-Demo-DDL]
As for previous ways to implement a DDL audit, let’s review some advantages and disadvantages of using SQL Server Audits
至于实现DDL审核的先前方法,让我们回顾使用SQL Server审核的一些优点和缺点
Advantages | Disadvantages |
« User-friendly »
| Additional management tasks
|
优点 | 缺点 |
“ 方便使用的 ”
| 其他管理任务
|
结论 (Conclusion)
As a conclusion, we can say that SQL Server Audit is a great feature delivered starting with Standard Edition that is scalable to ensure a professional enterprise-level auditing of server activity. Its implementation is easy, but needs additional tasks to be performed to ensure the kind of zero audit data loss.
总而言之,我们可以说SQL Server Audit是从Standard Edition开始提供的一项很棒的功能,该功能可伸缩以确保对服务器活动进行专业的企业级审核。 它的实现很容易,但是需要执行其他任务以确保零审计数据丢失。
While it’s not perfect, SQL Server Audit is, to me, the best feature available out of the box to implement security auditing in SQL Server and go a step forwards to « GDPR compliance ».
虽然并不完美,但对我来说,SQL Server审核是开箱即用的最佳功能,可以在SQL Server中实施安全审核,并进一步迈向“ GDPR合规性”。
Previous article in this series:
本系列的上一篇文章:
资源资源 (Resources)
- SQL Server Audit (Database Engine) SQL Server审核(数据库引擎)
- SQL Server Audit Action Groups and Actions SQL Server审核操作组和操作
- Understanding DDL Triggers 了解DDL触发器
- EVENTDATA (Transact-SQL) EVENTDATA(Transact-SQL)
- SQL Server Extended Events Targets SQL Server扩展事件目标
翻译自: https://www.sqlshack.com/sql-server-audit-feature-ddl-event-auditing-examples/
soar ddl审核规范