SQL Server 中的应用程序安全性方案Application Security Scenarios in SQL Server

没有用于创建安全 SQL Server 客户端应用程序的一种通用的正确方法。There is no single correct way to create a secure SQL Server client application. 每个应用程序在其需求、部署环境和用户群方面都是不同的。Every application is unique in its requirements, deployment environment, and user population. 最初部署时相当安全的应用程序随着时间的推移可能会变得不太安全。An application that is reasonably secure when it is initially deployed can become less secure over time. 无法准确预测未来可能出现的威胁。It is impossible to predict with any accuracy what threats may emerge in the future.

作为一种产品,SQL Server 已演变了多种版本,以合并有助于开发人员创建安全的数据库应用程序的最新安全功能。SQL Server, as a product, has evolved over many versions to incorporate the latest security features that enable developers to create secure database applications. 但是,安全性并非一成不变;它需要不断监视和更新。However, security doesn't come in the box; it requires continual monitoring and updating.

常见威胁Common Threats

开发人员需要了解安全威胁、针对安全威胁所提供的工具,以及如何避免自己造成的安全漏洞。Developers need to understand security threats, the tools provided to counter them, and how to avoid self-inflicted security holes. 最好可将安全性视为一个链,其中任何一个环节中出现中断就会损害整个链的牢固性。Security can best be thought of as a chain, where a break in any one link compromises the strength of the whole. 以下列表中包括了一些常见安全威胁,在本节的主题中对这些安全威胁进行了详细讨论。The following list includes some common security threats that are discussed in more detail in the topics in this section.

SQL 注入SQL Injection

SQL 注入是恶意用户输入 Transact-SQL 语句来取代有效输入的过程。SQL Injection is the process by which a malicious user enters Transact-SQL statements instead of valid input. 如果未经验证而将输入直接传递到服务器,且应用程序无意中执行了该注入的代码,则攻击可能就会损坏或破坏数据。If the input is passed directly to the server without being validated and if the application inadvertently executes the injected code, then the attack has the potential to damage or destroy data. 可以通过以下方式来阻止 SQL Server 注入攻击:使用存储过程和参数化的命令,避免动态 SQL,并限制所有用户的权限。You can thwart SQL Server injection attacks by using stored procedures and parameterized commands, avoiding dynamic SQL, and restricting permissions on all users.

特权提升Elevation of Privilege

当用户能够具有某个可信帐户的特权(如所有者或管理员)时,就会发生特权提升攻击。Elevation of privilege attacks occur when a user is able to assume the privileges of a trusted account, such as an owner or administrator. 始终以最小特权的用户帐户运行,并仅分配所需的权限。Always run under least-privileged user accounts and assign only needed permissions. 避免使用管理员帐户或所有者帐户来执行代码。Avoid using administrative or owner accounts for executing code. 这样就会降低因攻击成功而导致的损坏程度。This limits the amount of damage that can occur if an attack succeeds. 当执行需要额外权限的任务时,仅在该任务的持续时间内使用过程签名或模拟。When performing tasks that require additional permissions, use procedure signing or impersonation only for the duration of the task. 可以使用证书为存储过程签名,或使用模拟来临时分配权限。You can sign stored procedures with certificates or use impersonation to temporarily assign permissions.

探测和智能观测Probing and Intelligent Observation

探测攻击可使用由应用程序生成的错误消息来搜索安全漏洞。A probing attack can use error messages generated by an application to search for security vulnerabilities. 在所有程序代码中实现错误处理可阻止 SQL Server 错误信息返回到最终用户。Implement error handling in all procedural code to prevent SQL Server error information from being returned to the end user.

身份验证Authentication

如果在运行时构造了基于用户输入的连接字符串,则当使用 SQL Server 登录名时,就会发生连接字符串注入攻击。A connection string injection attack can occur when using SQL Server logins if a connection string based on user input is constructed at run time. 如果未检查该连接字符串中是否存在有效的关键字对,则攻击者可插入额外字符,进而可能访问服务器上的敏感数据或其他资源。If the connection string is not checked for valid keyword pairs, an attacker can insert extra characters, potentially accessing sensitive data or other resources on the server. 尽量使用 Windows 身份验证。Use Windows authentication wherever possible. 如果必须使用 SQL Server 登录名,请在运行时使用 SqlConnectionStringBuilder 创建和验证连接字符串。If you must use SQL Server logins, use the SqlConnectionStringBuilder to create and validate connection strings at run time.

密码Passwords

许多攻击成功的原因在于,入侵者能够获取或推测特权用户的密码。Many attacks succeed because an intruder was able to obtain or guess a password for a privileged user. 密码是阻止入侵者的第一道防线,因此设置强密码对于保护您的系统安全非常重要。Passwords are your first line of defense against intruders, so setting strong passwords is essential to the security of your system. 为混合模式身份验证创建和实施密码策略。Create and enforce password policies for mixed mode authentication.

始终为 sa 帐户分配强密码,即使在使用 Windows 身份验证时也是如此。Always assign a strong password to the sa account, even when using Windows Authentication.

本节内容In This Section

在 SQL Server 中使用存储过程管理权限Managing Permissions with Stored Procedures in SQL Server
描述如何使用存储过程来管理权限和控制数据访问。Describes how to use stored procedures to manage permissions and control data access. 使用存储过程是应对许多安全威胁的一种有效方法。Using stored procedures is an effective way to respond to many security threats.

在 SQL Server 中编写安全的动态 SQLWriting Secure Dynamic SQL in SQL Server
描述用于使用存储过程编写安全的动态 SQL 的技术。Describes techniques for writing secure dynamic SQL using stored procedures.

在 SQL Server 中对存储过程签名Signing Stored Procedures in SQL Server
描述如何使用证书为存储过程签名,以使用户可以使用其无直接访问权限的数据。Describes how to sign a stored procedure with a certificate to enable users to work with data they do not have direct access to. 这就使存储过程可执行调用方无直接执行权限的操作。This enables stored procedures to perform operations that the caller does not have permissions to perform directly.

在 SQL Server 中使用模拟自定义权限Customizing Permissions with Impersonation in SQL Server
描述如何使用 EXECUTE AS 子句来模拟另一用户。Describes how to use the EXECUTE AS clause to impersonate another user. 模拟将执行上下文从调用方切换到指定用户。Impersonation switches the execution context from the caller to the specified user.

在 SQL Server 中授予行级权限Granting Row-Level Permissions in SQL Server
描述如何实现行级权限以限制数据访问。Describes how to implement row-level permissions to restrict data access.

在 SQL Server 中创建应用程序角色Creating Application Roles in SQL Server
描述应用程序角色的功能。Describes features and functionality of application roles.

在 SQL Server 中启用跨数据库访问Enabling Cross-Database Access in SQL Server
描述如何在不损害安全性的情况启用跨数据库访问。Describes how to enable cross-database access without jeopardizing security.

请参阅See also