SQL Server中使用REVERT切换存储过程执行上下文

【 tulaoshi.com - 编程语言 】

　　问题

　　以前，我们学习过在SQL Server 2005中用EXECUTE AS命令来授予权限的方法，你已经看到如何用EXECUTE AS从句来授予粒度权限。在一个可编程的对象比如存储过程或函数内切换上下文是很重要的，尤其是如果访问存储过程的用户没有足够的权限去运行一个代码块时。但为了把它更加粒度化，只有在需要的时候和返回到调用方的原来的执行上下文情况下，我们才允许切换执行上下文。我们要怎么实现这一点？

　　专家解答

　　SQL Server 2005中的EXECUTE AS从句让我们拥有控制代码模块执行的安全上下文的选择权。扩展这一概念，我们用REVERT从句把执行上下文切换回执行上一个EXECUTE AS语句的调用方。这让我们能够允许用户在需要的时候扮演高权限的账户并且在有限的权限下返回到原来的执行上下文中。例如，在数据库中基于一些业务逻辑做出一些改动的存储过程。在运行代码块来改变数据之前，你需要创建一个数据库备份，这个数据库备份可以在任何需要的时候回滚这些变化。因为我们不想给将要执行存储过程的用户更多的权限，所以我们将使用EXECUTE AS从句去扮演一个具有更多权限的账户，让这个账户来做数据库备份，也使用REVERT从句切换回调用方的原来的执行上下文。让我们来看看一个示例脚本演示这两个从句如何进行。

　　首先，我们登录到SQL Server.其中，有一个是具有很小权限的普通用户，另一个是系统管理员角色中的一个成员。

　　
　　USEmaster

　　GO

　　--AddWindowsloginstoSQLServer

　　IFNOTEXISTS(SELECT*FROMsys.sysloginsWHEREname='SQLSRV90SQLUser1')

　　CREATELOGIN[SQLSRV90SQLUser1]

　　FROMWINDOWS

　　WITHDEFAULT_DATABASE=AdventureWorks

　　IFNOTEXISTS(SELECT*FROMsys.sysloginsWHEREname='SQLSRV90SQLDBA')

　　CREATELOGIN[SQLSRV90SQLDBA]

　　FROMWINDOWS

　　WITHDEFAULT_DATABASE=AdventureWorks

　　接着，我们在AdventureWorks数据库中增加用户的登录。

　　USEAdventureWorks

　　--AddthenewloginstotheAdventureWorksdatabase

　　CREATEUSERSQLUser1FORLOGIN[SQLSRV90SQLUser1]

　　CREATEUSERSQLDBAFORLOGIN[SQLSRV90SQLDBA]

　　--AddSQLDBAWindowsaccounttothedb_ownerrole

　　EXECsp_addrolemember'db_owner','SQLDBA'

　　GO

　　然后，让我们创建一个存储过程，这个存储过程将压缩我们先前展示的业务逻辑。为了达到演示的目的，我们只执行一个SELECT命令，所以我们只需要把SELECT权限授给SQLUser1用户。

　　--CreateprocedurethatexecutesaSELECTwithaBACKUPDATABASEcommand

　　CREATEPROCEDUREdbo.DisplayContextwithRevert

　　WITHEXECUTEASCALLER

　　AS

　　--Theuserwillonlybegrantedpermissiontodothissectionofthecode

　　SELECT*FROMPerson.Contact

　　--Wewilljustdisplaytheexecutioncontextoftheuserexecutingthissectionofthecodefordemonstration

　　SELECTCURRENT_USERASUserName;

　　--Wewillswitchexecutioncontexttoamoreprivilegedusertodothisportionofthecode

　　EXECUTEASUSER='SQLDBA';

　　BACKUPDATABASEAdventureWorksTODISK='C:AdventureWorks.BAK'WITHINIT,STATS=10;

　　--Wewilljustdisplaytheexecutioncontextoftheuserexecutingthissectionofthecode

　　SELECTCURRENT_USERASUserName;

　　--Wewillreverttotheexecutioncontextoftheoriginalcallertolimittheprivilegesback

　　REVERT;

　　SELECT*FROMPerson.Contact

　　SELECTCURRENT_USERASUserName;

　　GO

　　现在，我们接着对数据库用户SQLUser1只授予适当的权限。注意，SQLUser1没有权限去做数据库备份，这一点既不像dbo成员的用户也不像db_backupoperator角色。但是，由于他需要在存储过程中做数据库备份，所以他需要切换上下文到一个具有更多权限的用户中。接着，我们授予他IMPERSONATE权限，让他扮演SQLDBA。

　　nGrantuserpermissions

　　GRANTEXECUTEONdbo.DisplayContextwithRevertTOSQLUser1

　　GRANTSELECTONPerson.ContactTOSQLUser1

　　GO

　　--GranttheIMPERSONATEpermissionontheSQLUser1usersoitcanswitchexecutioncontexttoSQLDBA

　　GRANTIMPERSONATEONUSER::SQLDBATOSQLUser1

　　测试存储过程

　　让我们以SQLUser1的身份登录到SQL Server，运行一个备份数据库的命令。

　　注意，SQLUser1没有直接的权限去运行数据库备份的命令。运行存储过程

　　dbo.DisplayContextwithRevert，可以让用户SQLUser1作为SQLDBA以提高了的权限来执行数据库备份命令。突出显示的CURRENT_USER变量值描述了当在存储过程内部运行不同代码块时的执行上下文。