“The SQL Guy” Post # 9: SQL Server Database Engine Permission Model–Part 3


SQLServerIn this third and final instalment of our series on the SQL Server Database Engine Permission Model, we look execution context switching – in other words when I access an object, from whose perspective should I be assigned permissions. 

As always, if you want to test these out and don’t have SQL Server handy, you can download a full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!

 

SQL SERVER DATABASE ENGINE PERMISSION MODEL (PART 3)

The principal of least privilege requires that users be granted the most restrictive set of privileges required to perform tasks in order to limit the damages caused by security incidents. In the context of a database application, users must only be allowed to perform those operations required within the context of an application and nothing more. Restricting user permissions thus helps to limit the potential for inappropriate data access and database actions.

 

By using the SQL Server permission model, database administrators can associate permissions directly to user objects or to groups. To be successful, however, database administrators must apply due diligence in setting up user accounts and should carefully provisioning groups and control access to objects by user/group entities.

 

Stored procedures and functions are very common in modern database applications and quite often it is required for users to get access to application database modules (i.e. stored procedures and functions) without have additional permissions to access the objects referenced by the modules directly. In SQL Server, this pattern can be accomplished through ownership chaining, context switching and module signing.

 

This article will focus on explaining about context switching in SQL Server. In the next article, we will explain about module signing in SQL Server. To refresh your understanding about ownership chaining, please review the ownership chaining article here.

 

Execution Context Switching

It’s very common to have multiple users owning various objects in the database. Quite often, you might want to give access of your object to another user. Context switching can be used when a module needs to be executed under the permission of a different user and was introduced in SQL Server 2005 to alleviate the administrative burden on the database administrator.

 

 

clip_image001

Figure 1: Explaining execution context in SQL Server

 

As illustrated in figure 1, when user ‘User A’ calls stored procedure ‘Proc2’ in UserB schema, the execution context of user ‘User A’ is switched to user ‘UserZ’ execution context. The procedure selects data from Table2 and since UserZ does not own Table2 permissions are checked for UserZ on Table2.

 

Within functions and stored procedures, the EXECUTE AS clause supports the following qualifiers –

(1)    CALLER – causes the module to be executed in the context of the user executing the module.

(2)    SELF – causes the module to execute in the context of the user who created or last altered the module

(3)    OWNER – causes the module to execute under the context of the module owner.

(4)    ‘user name’ – causes the module to execute under the context of a given username.

 

It is important to note that the IMPERSONATE permission is required for creating / altering any module that specifies the EXECUTE AS clause. The REVERT statement switches the execution context back to the caller of the last EXECUTE AS statement.

 

A T-SQL example illustrating context switching

use master

GO

 

create database ExecutionContextDB

GO

 

create login BarneyLogin with password='1GoodPassword'

create login FredLogin with password='2GoodPassword'

create login WilmaLogin with password='3GoodPassword'

GO

 

use ExecutionContextDB

GO

 

--Create our database users mapped to their login and default schema

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

 

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

--The stored proc executes under whomever is calling it using EXECUTE AS CALLER

 

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 have given Wilma access to Fred's Stored Proc

--Let’s begin by logging in as Fred and seeing if this stored proc works

 

execute as user='FredUser'

GO

 

exec ListHits 1961

 

 

--go back to sysadmin

REVERT

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.

 

REVERT

GO

 

DamirB-BlogSignature

Comments (0)

Skip to main content