在 SQL Server 中使用存储过程管理权限Managing Permissions with Stored Procedures in SQL Server

为数据库创建多道防线的一个方法是使用存储过程或用户定义的函数实现所有数据访问。One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions. 撤消或拒绝对基础对象(如表)的所有权限,并授予对存储过程的 EXECUTE 权限。You revoke or deny all permissions to underlying objects, such as tables, and grant EXECUTE permissions on stored procedures. 这会为数据和数据库对象有效创建安全外围防线。This effectively creates a security perimeter around your data and database objects.

存储过程的优点Stored Procedure Benefits

存储过程具有以下优点:Stored procedures have the following benefits:

  • 可以包装数据逻辑和业务规则,以便用户可以仅通过开发人员和数据库管理员打算使用的方式访问数据和对象。Data logic and business rules can be encapsulated so that users can access data and objects only in ways that developers and database administrators intend.

  • 验证所有用户输入的参数化存储过程可用于阻止 SQL 注入攻击。Parameterized stored procedures that validate all user input can be used to thwart SQL injection attacks. 如果使用动态 SQL,请确保将命令参数化,并绝对不能将参数值直接包括在查询字符串中。If you use dynamic SQL, be sure to parameterize your commands, and never include parameter values directly into a query string.

  • 可禁止即席查询和数据修改。Ad hoc queries and data modifications can be disallowed. 这样将阻止用户恶意或无意中损坏数据或执行查询,以避免降低服务器或网络的性能。This prevents users from maliciously or inadvertently destroying data or executing queries that impair performance on the server or the network.

  • 可以在过程代码中处理错误,而无需将错误直接传递给客户端应用程序。Errors can be handled in procedure code without being passed directly to client applications. 这样可防止返回错误消息,以避免其可能有助于探测攻击。This prevents error messages from being returned that could aid in a probing attack. 在服务器上记录错误并对其进行处理。Log errors and handle them on the server.

  • 存储过程只能编写一次,可由很多应用程序访问。Stored procedures can be written once, and accessed by many applications.

  • 客户端应用程序不需要知道有关基础数据结构的任何信息。Client applications do not need to know anything about the underlying data structures. 只要更改不影响参数列表或返回的数据类型,就可以更改存储过程代码,而无需在客户端应用程序中进行更改。Stored procedure code can be changed without requiring changes in client applications as long as the changes do not affect parameter lists or returned data types.

  • 存储过程可通过将多个操作组合到一个过程调用中来减少网络通讯。Stored procedures can reduce network traffic by combining multiple operations into one procedure call.

存储过程的执行Stored Procedure Execution

存储过程利用所有权链接来提供对数据的访问,这样,用户就不必拥有访问数据库对象的显式权限。Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. 如果按顺序相互访问的对象由同一个用户所拥有,就会存在所有权链接。An ownership chain exists when objects that access each other sequentially are owned by the same user. 例如,存储过程可以调用其他存储过程,或者存储过程可访问多个表。For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. 如果执行链中的所有对象的所有者相同,则 SQL Server 只检查调用方的 EXECUTE 权限,而不检查调用方对其他对象的权限。If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. 因此,只需对存储过程授予 EXECUTE 权限;可以撤消或拒绝对基础表的所有权限。Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

最佳做法Best Practices

仅编写存储过程不足以保证应用程序的安全,Simply writing stored procedures isn't enough to adequately secure your application. 还应当考虑以下潜在的安全漏洞。You should also consider the following potential security holes.

  • 为您希望其能够访问数据的数据库角色授予对存储过程的 EXECUTE 权限。Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.

  • 撤消或拒绝数据库中所有角色和用户(包括 public 角色)对基础表的所有权限。Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. 所有用户会从公共角色中继承权限。All users inherit permissions from public. 因此,拒绝对 public 的权限表示只有所有者和 sysadmin 成员具有访问权;所有其他用户将无法从其他角色的成员资格继承权限。Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles.

  • 请不要将用户或角色添加到 sysadmindb_owner 角色。Do not add users or roles to the sysadmin or db_owner roles. 系统管理员和数据库所有者可访问所有数据库对象。System administrators and database owners can access all database objects.

  • 禁用 guest 帐户。Disable the guest account. 这样将阻止匿名用户连接到数据库。This will prevent anonymous users from connecting to the database. 默认情况下,会在新数据库中禁用来宾帐户。The guest account is disabled by default in new databases.

  • 实现错误处理和记录错误。Implement error handling and log errors.

  • 创建用于验证所有用户输入的参数化存储过程。Create parameterized stored procedures that validate all user input. 将所有用户输入视为不受信任。Treat all user input as untrusted.

  • 除非绝对必要,否则应避免使用动态 SQL。Avoid dynamic SQL unless absolutely necessary. 使用 Transact-SQL QUOTENAME() 函数可分隔字符串值,并对输入字符串中的任何分隔符进行转义。Use the Transact-SQL QUOTENAME() function to delimit a string value and escape any occurrence of the delimiter in the input string.

外部资源External Resources

有关更多信息,请参见以下资源。For more information, see the following resources.

资源Resource 描述Description
SQL Server 联机丛书中的存储过程SQL 注入Stored Procedures and SQL Injection in SQL Server Books Online 说明如何创建存储过程和 SQL 注入工作原理的主题。Topics describe how to create stored procedures and how SQL Injection works.

请参阅See also