在 SQL Server 中使用模拟自定义权限Customizing Permissions with Impersonation in SQL Server

许多应用程序都使用存储过程来访问数据,依靠所属权链接来限制对基表的访问。Many applications use stored procedures to access data, relying on ownership chaining to restrict access to base tables. 您可以授予针对存储过程的 EXECUTE 权限,撤消或拒绝针对基表的权限。You can grant EXECUTE permissions on stored procedures, revoking or denying permissions on the base tables. 如果存储过程和表具有相同的所有者,则 SQL Server 不检查调用方的权限。SQL Server does not check the permissions of the caller if the stored procedure and tables have the same owner. 但是,如果对象具有不同的所有者或使用动态 SQL,则所属权链接不起作用。However, ownership chaining doesn't work if objects have different owners or in the case of dynamic SQL.

当调用方对引用的数据库对象没有权限时,您可以在存储过程中使用 EXECUTE AS 子句。You can use the EXECUTE AS clause in a stored procedure when the caller doesn't have permissions on the referenced database objects. EXECUTE AS 子句的作用是将执行上下文切换到代理用户。The effect of the EXECUTE AS clause is that the execution context is switched to the proxy user. 所有代码以及任何对嵌套存储过程或触发器的调用均在代理用户的安全上下文下执行。All code, as well as any calls to nested stored procedures or triggers, executes under the security context of the proxy user. 只有在执行该过程或发出 REVERT 语句后,执行上下文才会恢复到原始调用方。Execution context is reverted to the original caller only after execution of the procedure or when a REVERT statement is issued.

使用 EXECUTE AS 语句切换上下文Context Switching with the EXECUTE AS Statement

使用 Transact-SQL EXECUTE AS 语句可以通过模拟其他登录名或数据库用户来切换一个语句的执行上下文。The Transact-SQL EXECUTE AS statement allows you to switch the execution context of a statement by impersonating another login or database user. 这是一种以其他用户身份测试查询和过程的有用技术。This is a useful technique for testing queries and procedures as another user.

EXECUTE AS LOGIN = 'loginName';  
EXECUTE AS USER = 'userName';  

您必须对要模拟的登录名或用户具有 IMPERSONATE 权限。You must have IMPERSONATE permissions on the login or user you are impersonating. 所有数据库的 sysadmin 及其拥有的数据库中的 db_owner 角色成员都隐含具有此权限。This permission is implied for sysadmin for all databases, and db_owner role members in databases that they own.

使用 EXECUTE AS 子句授予权限Granting Permissions with the EXECUTE AS Clause

您可以在存储过程、触发器或用户定义函数(内联表值函数除外)的定义头中使用 EXECUTE AS 子句。You can use the EXECUTE AS clause in the definition header of a stored procedure, trigger, or user-defined function (except for inline table-valued functions). 这会使过程在 EXECUTE AS 子句中指定的用户名或关键字的上下文中运行。This causes the procedure to execute in the context of the user name or keyword specified in the EXECUTE AS clause. 您可以在数据库中创建一个不映射到登录名的代理用户,仅为其授予对过程所访问的对象的必要权限。You can create a proxy user in the database that is not mapped to a login, granting it only the necessary permissions on the objects accessed by the procedure. 只有在 EXECUTE AS 子句中指定的代理用户才必须对模块所访问的所有对象具有权限。Only the proxy user specified in the EXECUTE AS clause must have permissions on all objects accessed by the module.

备注

某些操作(如 TRUNCATE TABLE)没有可授予的权限。Some actions, such as TRUNCATE TABLE, do not have grantable permissions. 通过在过程中合并该语句并指定具有 ALTER TABLE 权限的代理用户,可以将截断表的权限扩展到仅对过程具有 EXECUTE 权限的调用方。By incorporating the statement within a procedure and specifying a proxy user who has ALTER TABLE permissions, you can extend the permissions to truncate the table to callers who have only EXECUTE permissions on the procedure.

EXECUTE AS 子句中指定的上下文在过程(包括嵌套的过程和触发器)期间有效。The context specified in the EXECUTE AS clause is valid for the duration of the procedure, including nested procedures and triggers. 当执行完成或发出 REVERT 语句时,上下文恢复到调用方。Context reverts to the caller when execution is complete or the REVERT statement is issued.

在过程中使用 EXECUTE AS 子句包含三个步骤。There are three steps involved in using the EXECUTE AS clause in a procedure.

  1. 在数据库中创建一个不映射到登录名的代理用户。Create a proxy user in the database that is not mapped to a login. 这不是必需的,但它有助于管理权限。This is not required, but it helps when managing permissions.
CREATE USER proxyUser WITHOUT LOGIN  
  1. 授予代理用户必要的权限。Grant the proxy user the necessary permissions.

  2. 将 EXECUTE AS 子句添加到存储过程或用户定义函数中。Add the EXECUTE AS clause to the stored procedure or user-defined function.

CREATE PROCEDURE [procName] WITH EXECUTE AS 'proxyUser' AS ...  

备注

要求审核的应用程序可能中断,因为未保留调用方的原始安全上下文。Applications that require auditing can break because the original security context of the caller is not retained. 返回当前用户标识的内置函数(如 SESSION_USER、USER 或 USER_NAME)返回与 EXECUTE AS 子句关联的用户,而不是原始调用方。Built-in functions that return the identity of the current user, such as SESSION_USER, USER, or USER_NAME, return the user associated with the EXECUTE AS clause, not the original caller.

与 REVERT 一起使用 EXECUTE ASUsing EXECUTE AS with REVERT

可以使用 Transact-SQL REVERT 语句来返回到原始执行上下文。You can use the Transact-SQL REVERT statement to revert to the original execution context.

可选子句 WITH NO REVERT COOKIE = @variableName,如果将执行上下文切换回调用方允许@variableName变量包含正确的值。The optional clause, WITH NO REVERT COOKIE = @variableName, allows you switch the execution context back to the caller if the @variableName variable contains the correct value. 这允许您在使用连接池的环境中将执行上下文切换回调用方。This allows you to switch the execution context back to the caller in environments where connection pooling is used. 因为值@variableName才知道调用方的 EXECUTE AS 语句中,调用方可以保证调用应用程序的最终用户无法更改执行上下文。Because the value of @variableName is known only to the caller of the EXECUTE AS statement, the caller can guarantee that the execution context cannot be changed by the end user that invokes the application. 当连接关闭时,它将返回到池中。When the connection is closed, it is returned to the pool. 有关详细信息连接池在 ADO.NET 中,请参阅SQL Server 连接池 (ADO.NET)For more information on connection pooling in ADO.NET, see SQL Server Connection Pooling (ADO.NET).

指定执行上下文Specifying the Execution Context

除了指定用户外,还可以将 EXECUTE AS 与以下任意关键字一起使用。In addition to specifying a user, you can also use EXECUTE AS with any of the following keywords.

  • CALLER。CALLER. 以 CALLER 身份执行是默认设置;如果未指定其他选项,则过程将在调用方的安全上下文中运行。Executing as CALLER is the default; if no other option is specified, then the procedure executes in the security context of the caller.

  • OWNER。OWNER. 以 OWNER 身份执行将在过程所有者的上下文中执行过程。Executing as OWNER executes the procedure in the context of the procedure owner. 如果过程是在 dbo 或数据库所有者拥有的架构中创建的,则此过程将以无限制权限执行。If the procedure is created in a schema owned by dbo or the database owner, the procedure will execute with unrestricted permissions.

  • SELF。SELF. 以 SELF 身份执行将在存储过程创建者的安全上下文中执行。Executing as SELF executes in the security context of the creator of the stored procedure. 这相当于以指定用户的身份执行,这里的指定用户是指创建或改变过程的人。This is equivalent to executing as a specified user, where the specified user is the person creating or altering the procedure.

请参阅See also