关于SQL Server 存储过程的EXECUTE AS CALLER选项

icecity1306 2014-10-08

展开全文

与SQL 2005之前的版本相比，2005及之后的版本对数据库对象的管理及查询的执行上下文行为做了两个重要的改变：

1. 用户-架构(User-Schema)分离。即用户不再作为数据库对象的直接拥有者(owner)，而是在数据库对象和用户之前加了一层schema。Schema即数据库对象的集合，用户可以拥有多个schema，每个用户有一个default schema。

2. 执行查询的时候可以切换用户上下文。这包括在批处理中通过EXECUTE AS语句直接切换上下文，以及另一个更常见的用法：在创建存储过程或其它模块时指定用户执行的上下文(WITH EXECUTE AS)。

关于第二点，有一个有意思的实验：

1. 创建一个测试数据库及表：

    CREATE DATABASE mydb
    go
    USE mydb
    go
    create table dbo.tbl_TestPer(ID INT, name sysname)

INSERT INTO tbl_TestPer SELECT 1, 'Iori'

2. 创建如下存储过程：

CREATE PROC [dbo].[sp_TestPer1]
WITH EXECUTE AS CALLER

    AS
    BEGIN
      SELECT SYSTEM_USER, CURRENT_USER
      SELECT * FROM dbo.tbl_TestPer
    END

3. 创建一个测试用户并修改其权限：

     CREATE LOGIN testusr WITH PASSWORD='Password@123!'
     go
     CREATE USER testusr FROM LOGIN testusr
     go

     GRANT CONNECT TO testusr
     go
     DENY SELECT ON dbo.tbl_TestPer TO testusr
     go

GRANT EXECUTE ON dbo.sp_TestPer1 TO test
go

4. 以testusr登录并执行如下语句，可以成功查询到记录

EXEC mydb..[sp_TestPer1]

这个时候的testusr用户实际上是没有权限查看tbl_TestPer表的，那么它是如何成功执行SELECT语句的呢？答案就在于Ownership chaining。由于sp_TestPer1和tbl_TestPer都属于schema dbo，而dbo这个schema的拥有者是用户dbo（好像有点绕），所以第二次对SELECT 语句的权限检查直接被by pass了。在联机丛书中有如下描述：

Regardless of the execution context that is specified in the module, the following actions always apply:

When the module is executed, the Database Engine first verifies that the user executing the module has EXECUTE permission on the module.
Ownership chaining rules continue to apply. This means if the owners of the calling and called objects are the same, no permissions are checked on the underlying objects.

因此要让SQL在用户在执行存储过程的时候对引用的对象进行权限检查，需要将存储过程与其引用的对象放在不同的schema下面，被不同的数据库用户所拥有。比如可以将表转到guest schema下面：

ALTER SCHEMA guest TRANSFER dbo.tbl_TestPer
go

再修改存储过程使其指向转移过的表：

    ALTER PROC dbo.sp_TestPer1
    WITH EXECUTE AS CALLER
    AS
    BEGIN
      SELECT SYSTEM_USER, CURRENT_USER
      SELECT * FROM guest.tbl_TestPer
    END

这时再用testuser执行存储过程将遇到错误229。