SQL Server审核概述

To fill an optimal role in all business’s computerized operations, information technology systems and infrastructure should be guaranteed, well controlled, aligned with the business goals and secured. Technology auditing ensures the evaluation and examination of these business requirements on an organization.

为了在所有业务的计算机化运营中发挥最佳作用，应保证，良好地控制信息技术系统和基础架构，使其与业务目标保持一致并得到保护。 技术审核可确保对组织上的这些业务需求进行评估和检查。

Database auditing is one of the most important parts of IT auditing process, ensuring that company data is secured, starting from evaluating the infrastructure where these data is stored and used, examining the business operations that are processing the data and finishing with the backup solutions that are used to keep these data. The result of a database audit will potentially affect business continuity and will help in building the trust bridge between customers and the organization, as the customer will be more amenable dealing with an organization that keeps sensitive data secured and always available.

数据库审核是IT审核过程中最重要的部分之一，从评估存储和使用这些数据的基础结构开始，检查正在处理数据的业务操作并完成备份解决方案，以确保公司数据的安全性。用于保留这些数据。 数据库审核的结果将潜在地影响业务连续性，并有助于建立客户与组织之间的信任桥，因为客户将更愿意与保持敏感数据安全且始终可用的组织打交道。

In environments that use SQL Server, a SQL Server Audit is a requirement for most types of security, financial and healthcare compliances, such as the ISO27001, PCI-DSS, BASEL3, GPDR, IG and HIPAA standards. Auditing SQL Server instances is the process of tracking and logging all events occurring on that SQL instance. Prioritizing auditing proactively is important because the process of developing a SQ Server audit strategy can be a daunting and time-consuming task. It requires specifying what to audit, how to audit, who should perform the audit, whom to audit and the acceptable auditing result. And the result of this audit will be provided directly to the organization management and decision makers to build the correct decisions based on the analysis and reports. You may not like the auditing process as you see the tremendous amount of work and planning involved, but you will like the auditing more when your company achieves critical milestones, like PCI Compliance for example, and becomes a trusted portal for Online Payments.

在使用SQL Server的环境中， SQL Server审核是大多数类型的安全性，财务和医疗保健合规性的要求，例如ISO27001，PCI-DSS，BASEL3，GPDR，IG和HIPAA标准。 审核SQL Server实例是跟踪和记录该SQL实例上发生的所有事件的过程。 主动确定审核的优先级非常重要，因为制定SQ Server审核策略的过程可能是艰巨且耗时的任务。 它要求指定要审计的内容，如何审计，应由谁执行审计，应由谁审计以及可接受的审计结果。 审核结果将直接提供给组织管理人员和决策者，以根据分析和报告建立正确的决策。 当您看到涉及大量的工作和计划​​时，您可能不喜欢审核过程，但是当您的公司达到关键的里程碑（例如PCI合规性）并成为在线支付的受信任门户时，您会更喜欢审核。

数据库审核级别 (Database auditing levels)

The level of audit, that specifies the type and amount of collected information, depends on the business compliance requirements and particular organization regulations and other considerations.

指定所收集信息的类型和数量的审核级别取决于业务合规性要求以及特定的组织法规和其他注意事项。

• For example, you may find an organization that tracks the operations on one table that contains financial data
  例如，您可能会发现一个组织，该组织跟踪包含财务数据的一个表上的操作
• The audit level will be higher for other organizations, that track all the changes and operations on a SQL Server database
  对于跟踪SQL Server数据库上所有更改和操作的其他组织，审核级别将更高。
• On the other hand, you may deal with and even higher level of auditing in international companies, that audit the SQL Server by tracking all network traffic that is coming to the server, the proxy and firewall server operations, the server changes where the databases are hosted, the login and logout events at the server and database levels and going deeper and deeper by tracking all changes on the table records
  另一方面，在国际公司中，您可能需要处理甚至更高级别的审核，即通过跟踪进入服务器的所有网络流量，代理和防火墙服务器操作，服务器更改数据库位置的方式来审核SQL Server。在服务器和数据库级别托管，登录和注销事件，并通过跟踪表记录上的所有更改来深入了解

数据库审核清单 (Database auditing checklist)

For most SQL Server audit strategies, there are number of common and critical events that you should keep an eye on, as a minimum requirement for any audit. These events include Failed Logins, that track the users who tried to connect to the SQL Server instance but failed. The importance of keeping an eye on such event is that, an excessive number of incidences of this event could be an indication of an attack on that SQL Server.

对于大多数SQL Server审核策略，作为所有审核的最低要求，您应关注许多常见事件和关键事件。 这些事件包括“ 登录失败” ，该事件跟踪尝试连接到SQL Server实例但失败的用户。 密切注意此类事件的重要性在于，该事件的发生次数过多可能表明该SQL Server受到攻击。

Another important action that should be monitored is the SQL Server Login Changes. This includes adding new login to the SQL Serve instance, dropping a login or changing the privileges of that login on the SQL Server instance. This action is acceptable if it is performed by the authorized person and logged properly in the changes log. Otherwise, it is may be a fake key that will be used to hack the SQL Server. A rule of thumb also here for the SQL Server logins, is having the Password Policy Enforced. In this case, you will guarantee that the password for any new SQL user or the new password for an existing user is following the Operating System password policy, such as the password complexity and expiration, configured in the Active Directory.

另一个应监视的重要操作是SQL Server 登录更改 。 这包括向SQL Server实例添加新的登录名，删除登录名或更改该登录名在SQL Server实例上的特权。 如果此操作由授权人员执行并正确记录在更改日志中，则可以接受。 否则，它可能是将用于破解SQL Server的伪造密钥。 对于SQL Server登录，这里的经验法则是强制执行密码策略。 在这种情况下，您将确保任何新SQL用户的密码或现有用户的新密码都遵循Active Directory中配置的操作系统密码策略，例如密码复杂性和有效期。

Tracking the database successful and unsuccessful Users Changes is a significant event that should be considered in any SQL Server auditing strategy. This includes creating or dropping a database user, or changing the permission granted to that database user. Successful changes should be performed by an authorized person and logged properly in the changes log. Otherwise it is an alarm for an attack on the SQL Server instance.

跟踪数据库成功和失败的用户更改是一个重大事件，任何SQL Server审核策略中都应考虑。 这包括创建或删除数据库用户，或更改授予该数据库用户的权限。 成功的更改应由授权人员执行，并正确记录在更改日志中。 否则，它是对SQL Server实例进行攻击的警报。

When the previous SQL Server audit base is specified, you can now go deeper based your organization’s requirement. Tracking Schema Changes, such as creating new database object, dropping an existing one or changing its structure, is also important and should be monitored. This is helpful to catch any illegal schema changes, as all official schema changes should be logged properly.

指定以前SQL Server审核基础之后，您现在可以根据组织的要求进行更深入的研究。 跟踪架构更改 （例如创建新的数据库对象，删除现有的对象或更改其结构）也很重要，应该对其进行监视。 这有助于捕获任何非法的架构更改，因为所有正式的架构更改均应正确记录。

To have a strong SQL Server auditing strategy, you shouldn’t leave any key under the carpet for the hackers. Tracking the Audit Changes, such as disabling the audit solution, dropping or altering the tracking events or performing changes on the audit result destination, will protect you from the hidden actions, or the actions that are performed under the absence of the auditing strategy. Hackers are malevolent but also clever. To be cleverer than them, we should track and consider any illegal change performed on the SQL audit solution, that is not logged in the changes log.

为了拥有强大SQL Server审核策略，您不应该将任何密钥留给黑客。 跟踪审核更改 ，例如禁用审核解决方案，删除或更改跟踪事件或在审核结果目标上执行更改，将保护您免受隐藏操作或在没有审核策略的情况下执行的操作的影响。 黑客既恶毒又聪明。 为了比它们聪明，我们应该跟踪并考虑在SQL审核解决方案上执行的任何非法更改，这些更改未记录在更改日志中。

频率 (Frequency)

Auditing your SQL Server instance works fine only if it is done regularly, without long gaps between the audits. In this way, the audit process will be less complex, result with meaningful report and achieve its goal. In addition, you should compare the result of the current audit against the previous audit results. In this way, you can identify the normal actions from the critical ones.

仅当定期进行SQL Server实例审核时，审核之间才能保持良好的间隔。 这样，审核过程将变得不那么复杂，可以产生有意义的报告并实现其目标。 此外，您应该将当前审核的结果与以前的审核结果进行比较。 这样，您可以从关键操作中识别出正常操作。

范围 (Scope)

The scope of the SQL Instance audit should be specified correctly. You will be happy with the visibility into all corners of your SQL Server instance and Operating System, but you may not be happy when you start suffering from the performance impact of you SQL audit solution, such as increasing Memory, CPU and I/O utilization. For this reason, it is recommended to start with a narrow audit scope, then tune it to a wider scope that covers what you want to audit. And this should be performed on your development environment first then replicated to the production once tuned and tested correctly.

应正确指定SQL实例审核的范围。 您将对SQL Server实例和操作系统的各个角落的可见性感到满意，但是当您开始遭受SQL审核解决方案对性能的影响（例如增加内存，CPU和I / O利用率）时，您可能会感到不满意。 。 因此，建议先从狭窄的审计范围开始，然后再将其调整到涵盖您要审计的范围的更大范围。 并且应该首先在您的开发环境上执行此操作，然后在正确调整和测试之后将其复制到产品中。

In the next articles of this series, we will go through the different techniques that can be used to audit the SQL Server instances. Stay tuned!

在本系列的下一篇文章中，我们将介绍可用于审核SQL Server实例的各种技术。 敬请关注！

目录 (Table of contents)

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