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

In 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 https://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 https://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.

 

 

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