THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Changing the security context with Dynamic SQL [T-SQL]

I’ve always found that understanding the SQL security model can be a finicky affair; that’s no criticism, its more to do with my reluctance to actually spend time immersing myself in it – I generally know what I need to know and that’s all. I remember back when I was first learning T-SQL I found the whole area of security around dynamic SQL difficult to grok until I saw an actual example of it and I figured it might be useful to anyone in the same position if I were to share a code sample demonstrating these peculiarities. Hence this blog post.

The particular vagary that I want to demonstrate is that where the use of dynamic T-SQL inside a stored procedure will change the security context under which that dynamic T-SQL is executed. Code inside a stored procedure will execute as the owner of the stored procedure until some dynamic T-SQL is encountered at which time the security context switches to that of the user that called the stored procedure. Allow me to demonstrate:

   --Create demo DB with objects
  
SET NOCOUNT ON;
  
USE MASTER
   BEGIN
TRY
      
DROP DATABASE DynSQL;
  
END TRY
  
BEGIN CATCH
  
END CATCH
  
CREATE DATABASE DynSQL;
  
GO
  
USE DynSQL
   GO
  
CREATE SCHEMA [sch];
  
GO
  
CREATE TABLE [sch].[foo] ([bar] INT);
  
GO
  
--===The important bit============================
  
CREATE PROC [sch].[demoproc] AS
   BEGIN
      
--The following statement will be executed as [sch]
      
SELECT * FROM [sch].[foo];
      
DECLARE @sql NVARCHAR(MAX) = 'SELECT * from [sch].[foo];';
      
--The dynamic SQL in @sql will be executed as the user that called [sch].[demoproc]
      
EXEC sp_executesql @sql;
  
END
  
GO

  
CREATE LOGIN [u] WITH PASSWORD = 'p@ssw0rd';
  
CREATE USER [u] FOR LOGIN [u];
  
GRANT EXECUTE ON [sch].[demoproc] TO [u]

  
EXECUTE AS LOGIN = 'u';
  
EXEC [sch].[demoproc]; -- <-Will throw error "The SELECT permission was denied on the object 'foo', database 'master', schema 'sch'."
  
REVERT;
  
--===============================================

   --Cleanup
  
DROP USER [u];
  
DROP PROC [sch].[demoproc];
  
DROP TABLE [sch].[foo];
  
DROP SCHEMA [sch];
  
DROP LOGIN [u]
  
USE MASTER
   DROP DATABASE
[DynSQL];

Let’s take a look at what happens. Firstly we get a (empty) resultset returned from the none-dynamic T-SQL inside the stored procedure because user [u] has got permission to execute [sch].[demoproc] and [sch].[demoproc] has got permission to select data from [sch].[foo]:

image

However over on the messages tab we see that we also get an error from the dynamic T-SQL even though its the same T-SQL statement (SELECT * FROM [sch].[foo];). This is because the security context switches to the caller (i.e. [u]) and that caller does not have permission to select from [sch].[foo]:

image

You can copy/paste the code above and execute it – it shouldn’t leave any objects lying around.

Hope this helps!

@Jamiet 

P.S. The code above was formatted for the web using The Simple-Talk Code Prettifier.

Published Monday, October 04, 2010 9:17 AM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul White said:

Hi Jamie,

Using dynamic SQL breaks ownership chaining, so permissions are checked.  One way to workaround this is by signing the procedure:

USE DynSQL;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

CREATE CERTIFICATE SelectOnFoo WITH SUBJECT = 'Grants Select Permission On Sch.Foo';

CREATE USER SelectOnFooUser FROM CERTIFICATE SelectOnFoo;

GRANT SELECT ON sch.foo TO SelectOnFooUser;

ADD SIGNATURE TO sch.demoproc BY CERTIFICATE SelectOnFoo;

BTW there's a 'USE DynSQL' statement missing from your script :)

Paul

October 4, 2010 9:28 PM
 

jamiet said:

See that's what I love about blogging. Even when I'm trying to share some knowledge I end up learning something! Much obliged Paul, and good spot on the missing USE! :)

October 5, 2010 3:36 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement