查看SQL Server审核

Deciding what method to use to collect audit information can be a task in and of itself. Depending on your needs there are a number of different ways to collect the information. There are settings such as Login Auditing and Common Criteria Compliance that are fairly simple to implement but only collect very specific sets of information. There are also coding solutions such as Triggers and Audit Columns that are very flexible but can be tricky to get right. Then there are Profiler Traces but those have been deprecated and will be removed in a future version of SQL Server. Their replacement, Extended Events, has become easier and easier to use in recent versions and can be used to collect a huge variety of information. However Extended Events does not include any Audit specific information. If you are setting up an auditing solution in SQL Server you are probably going to want to consider using SQL Server Audits. Audits are based on Extended Events and so have all of the benefits of Extended Events but also include the audit specific events. Audits have a different interface than Extended Events and a different set of T-SQL Commands. Fortunately they are very easy.

决定使用哪种方法来收集审核信息本身就是一项任务。 根据您的需求，有多种收集信息的方法。 有些设置很容易实现，例如“登录审核”和“通用标准合规性”，但仅收集非常特定的信息集。 还有一些编码解决方案，例如“触发器”和“审计列”，它们非常灵活，但要想正确就很难。 然后有Profiler跟踪，但已弃用了这些跟踪，并将在SQL Server的将来版本中将其删除。 在最近的版本中，它们的替代产品“扩展事件”变得越来越容易使用，并可用于收集各种信息。 但是，扩展事件不包括任何特定于审计的信息。 如果要在SQL Server中设置审核解决方案，则可能要考虑使用SQL Server审核 。 审核基于扩展事件，因此具有扩展事件的所有优点，但也包括特定于审核的事件。 审核具有与扩展事件不同的界面，并且具有不同的T-SQL命令集。 幸运的是，它们非常容易。

There are two components to an Audit. The Audit and the Specification.

审核有两个组成部分。 审核和规范。

服务器审核 (Server Audit)

The top level of any SQL Server audit is the Server Audit. This is the "header" of the audit. It contains among other things the settings for where the audit information will be stored and its behavior on failure. To bring up the GUI right click on Security/Audits and select New Audit.

SQL Server审核的最高级别是服务器审核。 这是审核的“标题”。 除其他事项外，它还包含用于存储审核信息的设置及其在失败时的行为。 要调出GUI，请右键单击“安全性/审计”，然后选择“ 新建审计” 。

There are a number of settings available when creating an audit, some of which need to be considered very carefully.

创建审核时有许多可用的设置，其中一些需要非常仔细地考虑。

• Audit Name: Obviously the name of the Audit.
  审核名称：显然是审核的名称。
• This ties in very closely with the next setting. The lower the value the greater the chance of failure, particularly in a busy system.If the value is 0 then the action and the logging are synchronous.
  这与下一个设置密切相关。 值越低，发生故障的机会越大，尤其是在繁忙的系统中。如果值为0，则操作和日志记录是同步的。
• • • This is the default value and the only one that is really "safe".
      这是默认值，也是唯一真正“安全”的值。
    • The risk with this setting is that actions can fail without any obvious reason or way to track what happened.
      此设置的风险在于，如果没有任何明显的原因或方式来跟踪发生的事情，则操作可能会失败。
    • This option has the most absolute ramifications. If an action cannot be logged then the instance will be shut down. This option should only be used in the most secure systems where it is absolutely needed. If restarting the instance causes another failed audit event then the instance will shut right back down again. At this point there are two options to bring the instance back up. Either by fixing the cause (adding additional disk space for example) or the instance can be brought up in single user mode.
      此选项具有最大的影响。 如果无法记录操作，则实例将被关闭。 此选项仅应在绝对需要的最安全的系统中使用。 如果重新启动实例导致另一个失败的审核事件，则该实例将再次关闭。 此时，有两个选项可以使实例备份。 通过解决原因（例如添加额外的磁盘空间），或者可以在单用户模式下启动实例。
  • • here.此处找到更多信息。
    • Application log
      应用日志
    • Default)默认 ）
      • It’s fairly obvious but the path selected needs to have enough room for the all of the files that might be created.
        这很明显，但是所选的路径需要有足够的空间来容纳可能创建的所有文件。
      • If you select Maximum Rollover Files there is an unexpected consequence. When the maximum number of files is reached a new file is created and the oldest one is deleted. The problem is that it only attempts to delete the old file once. If it is unable to delete it then the file will remain. Over time this can cause the amount of space taken up by the audit files to increase dramatically.
        如果选择“最大滚动文件数”，则会发生意外结果。 当达到最大文件数时，将创建一个新文件，并删除最旧的文件。 问题在于它仅尝试删除一次旧文件。 如果无法删除它，则文件将保留。 随着时间的流逝，这可能导致审核文件占用的空间量急剧增加。
      • Max File Size – Maximum size of each file.
        最大文件大小–每个文件的最大大小。
      • unlimited.无限制，则此选项不可用。
      sys.fn_get_audit_file is used to read the information written to one or more Audit Files. Remember that while the files are being read they are locked and if the system tries to delete one (see Audit File Maximum Limit) it will not be able to. sys.fn_get_audit_file用于读取写入一个或多个审核文件的信息。 请记住，在读取文件时，它们已被锁定，并且如果系统尝试删除一个文件（请参阅审核文件最大限制），它将无法删除文件。

  The T-SQL for the above settings is as follows:

  以上设置的T-SQL如下：

   
  USE [master]
  GO
   
  CREATE SERVER AUDIT [Sample Audit for Server Specification]
  TO FILE 
  (	     FILEPATH = N'C:\AuditFiles'
    	     ,MAXSIZE = 500 MB
    	     ,MAX_ROLLOVER_FILES = 10
    	     ,RESERVE_DISK_SPACE = ON
  )
  WITH
  (	     QUEUE_DELAY = 1000
    	     ,ON_FAILURE = CONTINUE
  )
  GO
   
  

  审核规范 (Audit Specifications)

  The Specification is the "detail" of the audit. An Audit "header" can contain one and only one Specification "detail". While the Audit contains information about where the audit events will be logged and how failures will be handled, the Audit Specification contains information about what events will be logged. Some events are server specific and some are database specific. Because of this it makes sense that there are both Server and Database Specifications. There is however, a fair amount of overlap in the events that are available. BACKUP_RESTORE_GROUP for example is available in both Server and Database specifications. The difference is that in the Server Specification the event is triggered for all databases while in the Database Specification the event is triggered only for the local database. The process for generating Server Specifications and Database Specifications is pretty similar. Under the appropriate Security heading (Server or Database) there is an Audit Specifications heading. Right click and select New Server/Database Audit Specification.

  规范是审核的“细节”。 审核“标题”可以包含一个且只能包含一个规范“详细信息”。 审核包含有关将在何处记录审核事件以及如何处理故障的信息，而审核规范则包含有关将记录哪些事件的信息。 一些事件是特定于服务器的，而某些则是特定于数据库的。 因此，同时具有服务器规范和数据库规范是有道理的。 但是，在可用事件中存在大量重叠。 例如，BACKUP_RESTORE_GROUP在服务器和数据库规范中均可用。 区别在于，在服务器规范中，将为所有数据库触发事件，而在数据库规范中，将仅对本地数据库触发事件。 生成服务器规范和数据库规范的过程非常相似。 在适当的安全性标题（服务器或数据库）下，有一个审核规范标题。 右键单击并选择“ 新服务器/数据库审核规范” 。

  

  Once the Create Database/Server Audit Specification window opens you can fill in the following information.

  一旦“ 创建数据库/服务器审核规范”窗口打开，您可以填写以下信息。

  • Name: The name of the Specification.
    名称：规范的名称。
  • Audit: This is a drop down of the Audits available. Remember that you can only have one Specification per Audit.
    审核：这是可用审核的下拉列表。 请记住，每个审核只能有一个规范。
  • 
    You will notice that the Actions list has a number of columns.
    您会注意到“动作”列表有许多列。
    • 
      For some events no additional information is needed, BACKUP_RESTORE_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP for example. On the other hand some events do require additional information, INSERT, UPDATE, DELETE and SELECT events for example. It appears that these events exist only in Database Specifications.
      对于某些事件，不需要其他信息，例如BACKUP_RESTORE_GROUP和FAILED_DATABASE_AUTHENTICATION_GROUP。 另一方面，某些事件确实需要其他信息，例如INSERT，UPDATE，DELETE和SELECT事件。 看来这些事件仅存在于数据库规范中。
    • Object Class: This one is easy since it’s a drop down. There are three types of objects that can be audited. DATABASE, SCHEMA and OBJECT.
      对象类：这很简单，因为它是一个下拉列表。 可以审核三种类型的对象。 数据库，模式和对象。
    • Object & Object Name: These two columns are dependent on the Object Class field. If the Object Class is OBJECT then Object is the schema name of the object being audited, otherwise it is left blank. The Object Name column is the name of the object to be audited. If you want to audit everything then you use the DATABASE Object Class and the Object Name will be the database name. If on the other hand you only want to monitor a single object then use the OBJECT Object Class and the Object will be the schema and the Object Name will be the objects name. Both of these columns are filled in automatically from the ellipsis (…) to the right of the Object Name column.
      If the Object Class is DATABASE then the Object Name must be the current database.
    • 对象和对象名称：这两列取决于“ 对象类别”字段。 如果对象类为OBJECT，则对象为被审核对象的架构名称，否则为空。 “ 对象名称”列是要审核的对象的名称。 如果要审核所有内容，则可以使用DATABASE 对象类 ，并且对象名称将是数据库名称。 另一方面，如果您只想监视单个对象，则使用OBJECT 对象 类 ， 对象将是模式，而对象名称将是对象名称。 这两个列都从“ 对象名称”列右侧的省略号（...）自动填充。
      如果对象类是DATABASE，则对象名称必须是当前数据库。
    • Principal: This is who is to be audited. It can be a Role, User, or Application Role. If all users need to be audited then the Public role should be specified. This column is also filled in using the ellipsis (…) to the right of it.
      校长：这是要审核的人。 它可以是角色，用户或应用程序角色。 如果需要审核所有用户，则应指定“公共”角色。 此列也使用其右边的省略号（...）填充。

  Here is a sample database specification using the GUI and the associated T-SQL code.

  这是使用GUI和关联的T-SQL代码的示例数据库规范。

  

   
  USE [Test]
  GO
   
  CREATE DATABASE AUDIT SPECIFICATION [Sample Database Specification]
  FOR SERVER AUDIT [Sample Audit for Database Specification]
  ADD (BACKUP_RESTORE_GROUP),
  ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
  ADD (EXECUTE ON DATABASE::[AdventureWorks2014] BY [dbo]),
  ADD (INSERT ON OBJECT::[Baseball].[PlayerPosition] BY [guest]),
  ADD (UPDATE ON SCHEMA::[Baseball] BY [public])
  GO
   
  

  结论 (In Conclusion)

  SQL Server Audits are remarkably simple to set up and work with. They do require some limited maintenance (checking to make sure the Audit Files are being deleted for example) but even that is fairly simple to automate. One very important aspect of auditing that is frequently forgotten, however, is that the once the data has been collected it needs to be reviewed. It can be very simple to just set up and Audit and forget about it. But if the data is not being reviewed on a regular basis, preferably using some sort of automated report, then the Audit is a waste of resources.

  SQL Server审核的设置和使用非常简单。 它们确实需要进行一些有限的维护（例如，检查以确保删除了审计文件），但是即使这样，也很容易实现自动化。 但是，经常被遗忘的审计的一个非常重要的方面是，一旦收集了数据，就需要对其进行审查。 只需进行设置和审核，然后将其忽略就可以非常简单。 但是，如果不定期（最好使用某种自动报告）对数据进行审核，则审核将浪费资源。

  
  
  

翻译自: https://www.sqlshack.com/reviewing-sql-server-audit/