Fun with execution context switching

Fun with execution context switching

  • Comments 3

Having multiple users each owning various objects is commonplace in the database world. When one user wants to give access of their object to another user -- that’s when administration of databases gets rather interesting.

 

Consider the following SQL Server 2000 experience: 

  • User Barney has a table of Rock and Roll hits
  • User Fred wrote a stored procedure that accepts a time period and returns a list of the hits of that year

In the current scenario, Fred needs at least SELECT access to Barney's table in order for the stored procedure to work. This seems like a logical administrative task. 

 

Now suppose Wilma wants to use Fred's stored procedure.  In SQL Server 2000, Wilma would need explicit access to Barney's table or Fred would have to own the table in order for Wilma to accomplish this.  Now imagine trying to manage this for hundreds of users in an enterprise and you can see that managing all these permissions could be quite cumbersome unless we came up with some consistent strategy.

 

To help alleviate some of this administrative burden, SQL Server 2005 allows users the ability to specify the execution context of which an object like a stored procedure or user-defined function will run under.

 

Imagine in our scenario that Fred could say, when this stored proc executes, execute it under my credentials so that Wilma doesn't have to go and get permissions on everything within the stored procedure in order for her to use it.

 

In SQL Server 2005, this would be accomplished using EXECUTE AS OWNER.  In fact there are four possibilities when it comes to changing the execution context.  They are as follows:

 

EXECUTE AS CALLER – This will execute under the credentials of the caller.  This is the same default behavior as in previous versions of SQL Server.  I.e. when Wilma calls the stored proc, the proc runs under Wilma.

 

EXECUTE AS SELF – This will execute under the credentials of the user who last modified the stored procedure.  In our scenario if Bam-Bam modified Fred's stored proc and Wilma called Fred's Proc, the proc would run under Bam-Bam.

 

EXECUTE AS ‘(insert name of login)’ – This will execute under the credentials of the login identified.  In order for this to work, the user creating or modifying the stored procedure needs to have IMPERSONATE permission for the login specified.  In our scenario if Fred wanted to run the stored proc under Dino's credentials, Fred would need the IMPERSONATE permissions granted to him by the sysadmin first, then he could EXECUTE AS 'Dino'.

 

EXECUTE AS OWNER – This will execute under the credentials of the login who owns the stored procedure.  As explained previously, Fred's stored proc will be run under Fred regardless of who executes it.

 

Referring back to our example, let us write a few examples of execution context switching. To gain the most from this, it is best to walk through this line by line in your favorite TSQL editor or simply read through the comments.

-- Demo setup

-- create our logins, users and database

use master

go

create login BarneyLogin with password='!@w9Kfvn3'

create login FredLogin with password='MN3@8YU8u'

create login WilmaLogin with password='Nb29D%&2j'

go

create database Music

go

use Music

go

--Create our database users mapped to their login

create user BarneyUser for login BarneyLogin with default_schema=BarneySchema

create user FredUser for login FredLogin with default_schema=FredSchema

create user WilmaUser for login WilmaLogin with default_schema=WilmaSchema

go

--Create our schemas for each user

create schema BarneySchema

authorization BarneyUser

go

create schema FredSchema

authorization FredUser

go

create schema WilmaSchema

authorization WilmaUser

go

--Create a table that Barney's schema owns

use Music

go

create table BarneySchema.RockHits

(YearPublished int NOT NULL,

Title nvarchar(50) NOT NULL)

go

--Insert some data into the table

insert into BarneySchema.RockHits values('1960','Pebbles Jam')

insert into BarneySchema.RockHits values('1961','Dino Disco')

insert into BarneySchema.RockHits values('1961','Fred''s Dance Formula')

go

GRANT SELECT ON BarneySchema.RockHits to FredUser

--Create the stored procedure that Fred's Schema owns, remember

--EXECUTE AS CALLER is the same as SQL Server 2000 behavior

--The stored proc executes under whomever is calling it

create procedure FredSchema.ListHits

@Year int

WITH EXECUTE AS CALLER

AS

BEGIN

select CURRENT_USER as '(Execute as Caller), Current User Context='

select YearPublished,Title from BarneySchema.RockHits where

YearPublished=@Year

END

--Let's grant Wilma the ability to execute this stored proc

GRANT EXECUTE ON FredSchema.ListHits to WilmaUser

--At this point we can begin playing with context switching

--We have given Fred access to Barney's table of hits

--We created the stored proc with SQL Server 2000 behavior i.e. EXECUTE AS CALLER

--We have given Wilma access to Fred's Stored Proc

--Lets begin by logging in as Fred and seeing if this stored proc works

execute as user='FredUser'

go

exec ListHits 1961

--The result is "Executing as FredUser" and the two titles from 1961

--A note on using executing as:

--We used "Execute as user='FredUser'" to context switch inline to

--The database user "FredUser", we could have used, "Execute as login='FredLogin'"

--However, this would have expanded the scope of the current connection to

--FredLogin e.g. this connection could not only use FredUser but could use any database user

--that was mapped to FredLogin!  So its best to scope the context switch

--as narrow as possible, in our demo we only care about the database user FredUser within the Music database so we use execute as user.

--Now let's have Wilma try and execute the stored proc

REVERT --go back to sysadmin

go

EXECUTE AS user='WilmaUser'

go

exec FredSchema.ListHits 1961

--We get the SELECT permission denied error as expected

--because the stored proc is executing as WilmaUser

--Now let's ALTER the stored procedure so that it will run under

--its owner, Fred.

REVERT

GO

ALTER PROCEDURE FredSchema.ListHits

@Year int

WITH EXECUTE AS OWNER

AS

BEGIN

select CURRENT_USER as '(Execute as Owner), Current User Context='

select YearPublished,Title from BarneySchema.RockHits where

YearPublished=@Year

END

GO

--Now let's try Wilma again

EXECUTE AS user='WilmaUser'

go

exec FredSchema.ListHits 1961

--As you can see the current user context is FredUser! and we didn't

--have to give Wilma explicit permissions to the underlying table in

--Fred's stored proc.

 

The previous example showed how one might leverage execution context switching with stored procedures.  Have fun!

 

Side note: One of the things we have found recently is if you are switching to a domain user SQL Server needs to hit a domain controller so if the DC is offline, the command will fail.  SQL Server doesn't cache domain credentials.  We are looking into this issue.

 

Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post
Page 1 of 1 (3 items)