Using Dynamic SQL inside a Stored Procedure

2019-07-31 · by Hannah Vernon · in t-sql

Preventing direct access to database tables for users is a widely considered “best practice” for many DBAs. The pattern typically looks like User -> Stored Procedure -> Tables. Since the stored procedure owner has access to the referenced tables, the user can be granted EXECUTE access to the stored procedure without requiring them to have SELECT access to the tables. However, if you use dynamic T-SQL inside a stored procedure, that security model breaks. T-SQL executed via EXEC ('some T-SQL') or EXEC sys.sp_executesql @cmd will execute under the security context of the caller, not the principal who created the stored procedure. Luckily, SQL Server offers a simple workaround for this problem, defining the stored procedure using the WITH EXECUTE AS OWNER clause.

The Execution of Marie Stuart - Abel de Pujol (after using Dynamic T-SQL without parameters)

The Execution of Marie Stuart (after using Dynamic T-SQL without parameters) – Abel de Pujol

The Setup

Before we get into the WITH EXECUTE AS OWNER clause, lets look at the problem with some code. I find code, with output, is often easier to understand than a description of the problem.

Lets create a database we can use for testing:

USE master;
IF DB_ID(N'owner_testing') IS NOT NULL
BEGIN
    ALTER DATABASE owner_testing SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE owner_testing;
END
CREATE DATABASE owner_testing;
GO

USE master;

IF DB_ID(N'owner_testing') IS NOT NULL

BEGIN

ALTER DATABASE owner_testing SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE owner_testing;

END

CREATE DATABASE owner_testing;

Next, we’ll create a table, and add some data to it.

USE owner_testing;
CREATE TABLE dbo.somedata
(
    somedata_key int NOT NULL
        CONSTRAINT somedata_pk
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , somechardata varchar(100) NOT NULL
    , somedate datetime NOT NULL
        CONSTRAINT somedata_somedate_df
        DEFAULT ((GETDATE()))        
);

INSERT INTO dbo.somedata (somechardata)
VALUES (REPLICATE('a', 100));

USE owner_testing;

CREATE TABLE dbo.somedata

(

somedata_key int NOT NULL

CONSTRAINT somedata_pk

PRIMARY KEY

CLUSTERED

IDENTITY(1,1)

, somechardata varchar(100) NOT NULL

, somedate datetime NOT NULL

CONSTRAINT somedata_somedate_df

DEFAULT ((GETDATE()))

);

INSERT INTO dbo.somedata (somechardata)

VALUES (REPLICATE('a', 100));

Next we’ll create a stored procedure that returns results from the table.

CREATE PROCEDURE dbo.somedata_query
(
    @StartDate datetime
    , @EndDate datetime
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SELECT sd.somedata_key
        , sd.somechardata
        , sd.somedate
    FROM dbo.somedata sd
    WHERE sd.somedate >= @StartDate
        AND sd.somedate <= @EndDate;
END
GO<

CREATE PROCEDURE dbo.somedata_query

(

@StartDate datetime

, @EndDate datetime

)

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT ON;

SELECT sd.somedata_key

, sd.somechardata

, sd.somedate

FROM dbo.somedata sd

WHERE sd.somedate >= @StartDate

AND sd.somedate <= @EndDate;

END

GO<

We need a login and user to complete the setup in a manner similar to what you’re likely to find in production. The last statement grants EXECUTE on the stored procedure to the newly created user.

CREATE LOGIN owner_testing_login 
WITH PASSWORD = N'#TheftNewtonCaputJensen6'
    , DEFAULT_DATABASE = owner_testing
    , DEFAULT_LANGUAGE = us_english;

CREATE USER owner_testing_user
FOR LOGIN owner_testing_login
WITH DEFAULT_SCHEMA = dbo;

GRANT EXECUTE ON dbo.somedata_query TO owner_testing_user;
GO

CREATE LOGIN owner_testing_login

WITH PASSWORD = N'#TheftNewtonCaputJensen6'

, DEFAULT_DATABASE = owner_testing

, DEFAULT_LANGUAGE = us_english;

CREATE USER owner_testing_user

FOR LOGIN owner_testing_login

WITH DEFAULT_SCHEMA = dbo;

GRANT EXECUTE ON dbo.somedata_query TO owner_testing_user;

The Initial Test

In this next bit of code, I’m using the EXECUTE AS USER = N'owner_testing_user'; to simulate logging into the SQL Server as that user. If you like, you can simply open a new SSMS query window logged in as owner_testing_login, and run the EXEC and SELECT statements there.

EXECUTE AS USER = N'owner_testing_user';
EXEC dbo.somedata_query @StartDate = N'2019-07-01 00:00:00', @EndDate = N'2019-07-31 23:59:59.997';
SELECT *
FROM dbo.somedata;
REVERT
GO

EXECUTE AS USER = N'owner_testing_user';

EXEC dbo.somedata_query @StartDate = N'2019-07-01 00:00:00', @EndDate = N'2019-07-31 23:59:59.997';

SELECT *

FROM dbo.somedata;

REVERT

The stored procedure executes without error, and returns results as expected. However, the SELECT statement results in an error:

Msg 229, Level 14, State 5, Line 57
The SELECT permission was denied on the object 'somedata', database 'owner_testing', schema 'dbo'.

That’s by design; we want the user to be able to retrieve data out of the table only via the stored procedure. If we let them select rows directly from the table, all hell might break loose™.

Uh-oh! Dynamic T-SQL!

Now, what if we want to make the @StartDate and @EndDate parameters optional? Perhaps the user wants to select every row that exists prior to January 1st, 2017 for example. Or sometimes, they want to get only today’s rows, via @StartDate >= '2019-07-30 00:00:00'. A good way to do that is with Dynamic T-SQL since it prevents what is known as kitchen-sink-coding where the T-SQL query contains every possible parameter, even if they are unused. Let’s reconstruct the stored procedure to accomplish that:

DROP PROCEDURE IF EXISTS dbo.somedata_query;
GO
CREATE PROCEDURE dbo.somedata_query
(
    @StartDate datetime = NULL
    , @EndDate datetime = NULL
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @cmd nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @params nvarchar(max);
    SET @params = N'@StartDate datetime, @EndDate datetime';
    SET @cmd = N'
    SELECT sd.somedata_key
        , sd.somechardata
        , sd.somedate
    FROM dbo.somedata sd
    ';
    SET @connector = 'WHERE ';
    IF @StartDate IS NOT NULL 
    BEGIN
        SET @cmd = @cmd + @connector + N'sd.somedate >= @StartDate';
        SET @connector = ' AND ';
    END
    IF @EndDate   IS NOT NULL 
    BEGIN
        SET @cmd = @cmd + @connector + N'sd.somedate <= @EndDate';
        SET @connector = ' AND ';
    END
    SET @cmd = @cmd + ';';
    EXEC sys.sp_executesql @cmd, @params, @StartDate = @StartDate, @EndDate = @EndDate;
END
GO
GRANT EXECUTE ON dbo.somedata_query TO owner_testing_user;
GO

DROP PROCEDURE IF EXISTS dbo.somedata_query;

CREATE PROCEDURE dbo.somedata_query

(

@StartDate datetime = NULL

, @EndDate datetime = NULL

)

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @cmd nvarchar(max);

DECLARE @connector nvarchar(max);

DECLARE @params nvarchar(max);

SET @params = N'@StartDate datetime, @EndDate datetime';

SET @cmd = N'

SELECT sd.somedata_key

, sd.somechardata

, sd.somedate

FROM dbo.somedata sd

SET @connector = 'WHERE ';

IF @StartDate IS NOT NULL

BEGIN

SET @cmd = @cmd + @connector + N'sd.somedate >= @StartDate';

SET @connector = ' AND ';

END

IF @EndDate IS NOT NULL

BEGIN

SET @cmd = @cmd + @connector + N'sd.somedate <= @EndDate';

SET @connector = ' AND ';

END

SET @cmd = @cmd + ';';

EXEC sys.sp_executesql @cmd, @params, @StartDate = @StartDate, @EndDate = @EndDate;

END

GRANT EXECUTE ON dbo.somedata_query TO owner_testing_user;

Now, let’s re-run the stored procedure:

EXECUTE AS USER = 'owner_testing_user';
EXEC dbo.somedata_query @StartDate = N'2019-07-01 00:00:00', @EndDate = N'2019-07-31 23:59:59.997';
REVERT
GO

EXECUTE AS USER = 'owner_testing_user';

EXEC dbo.somedata_query @StartDate = N'2019-07-01 00:00:00', @EndDate = N'2019-07-31 23:59:59.997';

REVERT

We get that same error about SELECT permission denied:

Msg 229, Level 14, State 5, Line 102
The SELECT permission was denied on the object 'somedata', database 'owner_testing', schema 'dbo'.

That’s because the dynamic T-SQL is executed under the users security context, not that of the stored procedure.¹

The Solution™

We can get around the problem by redefining the stored procedure to make use of the EXECUTE AS OWNER clause:

DROP PROCEDURE IF EXISTS dbo.somedata_query;
GO
CREATE PROCEDURE dbo.somedata_query
(
    @StartDate datetime = NULL
    , @EndDate datetime = NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @cmd nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @params nvarchar(max);
    SET @params = N'@StartDate datetime, @EndDate datetime';
    SET @cmd = N'
    SELECT sd.somedata_key
        , sd.somechardata
        , sd.somedate
    FROM dbo.somedata sd
    ';
    SET @connector = 'WHERE ';
    IF @StartDate IS NOT NULL 
    BEGIN
        SET @cmd = @cmd + @connector + N'sd.somedate >= @StartDate';
        SET @connector = ' AND ';
    END
    IF @EndDate   IS NOT NULL 
    BEGIN
        SET @cmd = @cmd + @connector + N'sd.somedate <= @EndDate';
        SET @connector = ' AND ';
    END
    SET @cmd = @cmd + ';';
    EXEC sys.sp_executesql @cmd, @params, @StartDate = @StartDate, @EndDate = @EndDate;
END
GO
GRANT EXECUTE ON dbo.somedata_query TO owner_testing_user;
GO

DROP PROCEDURE IF EXISTS dbo.somedata_query;

CREATE PROCEDURE dbo.somedata_query

(

@StartDate datetime = NULL

, @EndDate datetime = NULL

)

WITH EXECUTE AS OWNER

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @cmd nvarchar(max);

DECLARE @connector nvarchar(max);

DECLARE @params nvarchar(max);

SET @params = N'@StartDate datetime, @EndDate datetime';

SET @cmd = N'

SELECT sd.somedata_key

, sd.somechardata

, sd.somedate

FROM dbo.somedata sd

SET @connector = 'WHERE ';

IF @StartDate IS NOT NULL

BEGIN

SET @cmd = @cmd + @connector + N'sd.somedate >= @StartDate';

SET @connector = ' AND ';

END

IF @EndDate IS NOT NULL

BEGIN

SET @cmd = @cmd + @connector + N'sd.somedate <= @EndDate';

SET @connector = ' AND ';

END

SET @cmd = @cmd + ';';

EXEC sys.sp_executesql @cmd, @params, @StartDate = @StartDate, @EndDate = @EndDate;

END

GRANT EXECUTE ON dbo.somedata_query TO owner_testing_user;

Note in the above code I am using parameters for everything that is passed in by the user, instead of simply appending the values directly to the @cmd string. That’s important because it prevents SQL Injection vulnerabilities. It’s doubly-important that you don’t create a SQL Injection vulnerability in a stored procedure that uses EXECUTE AS OWNER since the resulting dynamic T-SQL code will be executed in security context of the login that created the stored procedure, typically a sysadmin.² But enough about that. Let’s run the proc now:

EXECUTE AS USER = 'owner_testing_user';
EXEC dbo.somedata_query @StartDate = N'2019-07-01 00:00:00', @EndDate = N'2019-07-31 23:59:59.997';
REVERT
GO

EXECUTE AS USER = 'owner_testing_user';

EXEC dbo.somedata_query @StartDate = N'2019-07-01 00:00:00', @EndDate = N'2019-07-31 23:59:59.997';

REVERT

Now, we get the results we expect:

╔══════════════╦════════════════════════╦═════════════════════════╗
║ somedata_key ║      somechardata      ║        somedate         ║
╠══════════════╬════════════════════════╬═════════════════════════╣
║            1 ║ aaaaaaaaaaaaaaaaaaa... ║ 2019-07-25 12:58:18.233 ║
╚══════════════╩════════════════════════╩═════════════════════════╝

Success! This allows us to run Dynamic T-SQL from inside a stored procedure, without granting the caller rights to the underlying table.

Thanks for reading, and I hope you enjoyed this part of our series on T-SQL.

If you found this post helpful, would you consider sharing it on social media? That would really help me out!

^{1 – See Solomon’s comment below for a more technically accurate picture of what’s actually happening here.}
^{2 – Code is not executed in the security context of the login that created the procedure, it is actually executed with the rights of the owner of the procedure, typically the schema, which in the case of the above example is dbo. See Solomon’s comment below for important details.}

2 Responses

Solomon Rutzky

2019-08-13 at 8:59 PM · Reply →
Hi Hannah. Nice post. There are a few inaccuracies towards the end:

That’s because the dynamic T-SQL is executed under the users security context, not that of the stored procedure.

The statement above is incorrect. Dynamic SQL executes in the same security context as the rest of the procedure (assuming the “AS” clause was not used). Stored procedures, by default, execute in the security context of the caller. The reason for the permission error is that Dynamic SQL breaks ownership chaining. Ownership chaining is not a different security context; it merely skips permission checks (for DML and EXECUTE permissions) on objects within modules that are owned by the owner of the module. When ownership chaining is broken, permissions on objects are checked.

in a stored procedure that uses EXECUTE AS OWNER since the resulting dynamic T-SQL code will be executed in security context of the login that create the stored procedure, typically a sysadmin

There are two things to fix here:
- “OWNER” does not refer to the User that created the Stored Procedure; it refers to the physical owner of the module (by default this defers to the Schema if ALTER AUTHORIZATION has not been executed on that module) or the owner of the Schema in which the module exists. If the module changes ownership (either directly or moved to a Schema having a different owner), then the EXECUTE AS will resolve to a different principal_id (as found in sys.database_principals).
- The distinction between User and Login needs to be clarified in order to understand the actual execution context. Users are at the Database level while Logins are at the Instance level. The User / DB level is always part of the execution context. If the User is “dbo”, then the execution context will be “dbo”, but that is <em>not</em> a sysadmin. “dbo” permissions, by default, do not extend beyond the current DB. Whether or not the Login / Instance level is part of the execution context depends on the TRUSTWORTHY setting of the DB. If the DB is set to TRUSTWORTHY OFF (the default), then there is no Login / Instance level context. If the DB is set to TRUSTWORTHY ON (bad idea, largely for this reason), AND the User SID has a matching Login SID, <em>then</em> there is also a Login context (as found in sys.server_principals logins WHERE logins.SID = User.SID).
Meaning, if User [a] creates a stored procedure [b].[proc] using “WITH EXECUTE AS OWNER”, and Schema [b] is owned by “dbo”, then if User [c] executes [b].[proc], the execution context will be as follows:
- At the very least, User will be “dbo” (this is DB-level only and implies nothing for the Instance/Server-level)
- If DB is TRUSTWORTHY OFF: there is no Login / Instance-level context (i.e. no Instance-level permissions, hence no “sysadmin”)
- If DB is TRUSTWORTHY ON: If there is a Login with an SID that matches the User’s SID, then the Login / Instance-level context is of that matching SID (and the process now has any Instance-level permissions associated with that Login, which might be “sysadmin”)
Yes, using “WITH EXECUTE AS OWNER” (or any value for “EXECUTE AS”) is quick and easy, but it is also problematic for a few reasons:
- execution context is now fully the “owner” of that module (I use “module” because this ability exists for UDFs, Multi-Statement TVFs, Triggers, and a few other types of modules). And when the low-privileged web/app User is now “dbo” or any other owner, they have ALL of the permissions of that other User (hence why you are warning about SQL Injection), but all you really wanted to do was simply add one or two permissions for some combination of INSERT / UPDATE / DELETE / SELECT / EXECUTE.
- The new security context continues through all level of nested calls in this process, even if you only needed it for that one statement.
For these reasons, I would recommend against using the “WITH EXECUTE AS” clause as the ease-of-use does not seem to outweigh the security risks. Instead, I highly recommend Module Signing – see details at ModuleSigning.info. It takes a couple of extra steps (2 one-time setup steps, 1 per-module step, and 1 step that might cover all modules but might not), but they are not complicated and the benefits are well worth it:
- Security Context does not change (User and Login remain as they were: the web / app account)
- Only the additional permissions necessary for the operation are added (i.e. very fine-grained / specific permissions; tight control over which permissions are granted)
- Additional permissions are only for the executing module (including Dynamic SQL within that module). Nested module calls are not included (i.e. tight control over where permissions are applied)
- ANY change to the code or ownership of the module removes the added permissions (i.e. forcing a code-review to ensure that someone didn’t try to sneak in additional calls after extra permissions were added)
Applying this to your example, you would only need to do the following to get the Dynamic SQL working:
- Create a Certificate (one-time setup)
- Create User from Certificate (one-time setup)
- Grant Certificate-based User “SELECT ON dbo.somedata” (one-time setup per needed Permission; many modules can use this permission but maybe in the future you need to do an additional one-time GRANT for INSERT on this, or some other, table)
- Sign the Stored Procedure (i.e. module) using “ADD SIGNATURE” and that Certificate
That’s it. All you needed was the SELECT permission, and that is all that has been granted (and only to that one Stored Procedure, not to any User that can actually log in / connect). If “dbo.somedata_query” were to “EXEC dbo.LogRequest @User, @StartDate, @EndDate;” (or anything), then the additional SELECT permission would not be available to “dbo.LogRequest” unless that Stored Procedure were all signed with that Certificate.

I have a more thorough explanation / walk-through of all of this in my post PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining. And there is even a separate post that is a step-by-step walk-through of doing what I described above (link is towards the bottom of that post, in the “Examples” section, titled “Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level”.

Take care,

Solomon

Loading...
1. Hannah Vernon
  
  2019-08-14 at 8:19 AM · Reply →
  
  Hi Solomon – Thanks for such a detailed comment. This post is designed to provide a quick, easy-to-understand introduction to using the WITH EXECUTE AS clause in a stored procedure for the simple case of executing parameterized dynamic SQL inside a stored procedure. While you have accurately denoted the security implications, I think for most cases where the requirement is well understood, and the code is parameterized, WITH EXECUTE AS OWNER is not a security risk, and offers an easy and succinct method of making the code work. Of course, if you don’t parameterize your dynamic T-SQL, then you’ve got a problem, as noted in my post.
  
  Loading...

Using Dynamic SQL inside a Stored Procedure

The Setup

The Initial Test

Uh-oh! Dynamic T-SQL!

The Solution™

Related

2 Responses

Leave a ReplyCancel reply

Search

Categories

Pages

Meta