SQLServerCentral Article

Loginless Database Users and Dynamic SQL

,

Overview

In this article we will explore how to leverage a SQL Server feature I informally call Loginless Database Users to maintain data security and preserve interface when dynamic SQL is being used within a stored procedure.

Target Audience

Developers or database administrators writing new or supporting existing stored procedures that make use of dynamic SQL where the Database User granted permission to execute the stored procedure should not also be granted direct access the tables referenced within the dynamic SQL. To summarize, this article aims to answer the following question:

How can I grant a Database User permission to execute a stored procedure that makes use of dynamic SQL while also preventing that User from connecting to the database using a tool like SQL Server Management Studio and directly accessing data in the tables referenced within the dynamic SQL?

Background

Dynamic SQL has a somewhat bad reputation in some SQL Server professional circles and recommending its use can be a provocative topic of discussion for some. At minimum, when recommended, it should come with disclaimers about SQL injection, data security, plan cache re-use, database performance and database design. While there are some use cases where employing dynamic SQL is not only justified but is a better choice than declared SQL, there are many more when it is a poor choice.

I have chosen to leave the topics of database performance and design as largely out of the scope of this article and will leave the decision of whether or not to employ dynamic SQL to solve the problem-at-hand to the reader. This article picks up after you have already decided to employ dynamic SQL to show how to maintain a higher level of database security by also employing Loginless Database Users along with the stored procedures that contain dynamic SQL.

My favorite use of dynamic SQL, which I learned from Microsoft Certified Master: SQL Server 2008 and SQL Server MVP Gail Shaw, is a very elegant way to solve the problem presented by catch-all queries, and so I’ll be coding to solve a simple version of this problem case in the examples throughout this article.

Technical Requirements

If you want to follow along by implementing the code samples in this article in your own environment so you can test the results you'll need the following:

  1. Basic knowledge of SQL Server security concepts and some basic T-SQL skills.
  2. Access to a SQL Server 2012 instance to which you can add a Login and that has a copy of the AdventureWorks2012 OLTP sample database and in which you can manage Users and Stored Procedures.

Ownership Chaining, The Happy Path

Let’s start with a simple example of a stored procedure that could exist within the Person schema of the AdventureWorks2012 sample database that selects from a table, also in the Person schema:

USE AdventureWorks2012;
GO
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'Person.GetPersonByLastName')
                    AND type IN (N'P', N'PC') )
    DROP PROCEDURE  Person.GetPersonByLastName;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE Person.GetPersonByLastName (@LastName dbo.Name)
AS
BEGIN
    SELECT  BusinessEntityID,
            PersonType,
            Title,
            FirstName,
            MiddleName,
            LastName,
            Suffix
    FROM    Person.Person
    WHERE   LastName = @LastName;
END;
GO
EXEC Person.GetPersonByLastName
    @LastName = N'Simon';
GO

Example 1 - stored procedure definition uses only declared SQL

In Example 1 any database user granted permission to execute the stored procedure Person.GetPersonByLastName will be able to retrieve data from Person.Person without being explicitly granted SELECT-permission on Person.Person. Permission to access Person.Person is implied within the scope of the stored procedure, i.e. the user who executed the stored procedure may not have been explicitly granted permission to select data directly from the table however those lack of permissions are never discovered because permissions on the table are never checked by the database engine due to a behavior of SQL Server known as Ownership Chaining.

Ownership Chaining supports users who have permission to an object in accessing other objects referenced within that object as long as the initial object and its referenced objects are owned by the same database principal. When two objects exist in the same schema and explicit ownership has not been set on either object (the default) then those objects are owned by the same database principal, the owner of the schema to which they belong. Ownership Chaining is a relevant concept for accessing many object types including views, stored procedures and user-defined functions.

As long as an ownership chain remains intact, i.e. all referenced objects have the same owner as the initially referenced object to which the user has permissions to access, the user who was granted permission to the initially referenced object does not require additional permissions to access those referenced objects. In fact, even if the user was explicitly denied access to a referenced object, ownership chaining will enable the bypassing of that DENY permission because permissions are not checked for referenced objects in an ownership chain so be careful of that fact when incorporating Ownership Chaining into your data access strategy.

Ownership Chaining and Dynamic SQL

The previous section explains how a basic ownership chain can work. You can extend the concept to implement a complete data access interface into your database that would allow users and applications to manage all aspects of the data while never granting them direct access to a table. I strongly favor this technique as a means to maintain a high level of data security as well as to provide a means to troubleshoot and tune the database after it has gone into production without affecting data clients, i.e. the users and applications. If all data access is occurring via stored procedures then as long as the stored procedure input and output interfaces are maintained, the procedure code, or even the underlying table structures, can be safely refactored without affecting data clients. It is worth mentioning that there are numerous other benefits to using stored procedures for data access in the areas of performance as well as database administration, however delving too deeply into the specific benefits of implementing stored procedures is outside the scope of this article.

As I see it leveraging ownership chaining and performing all data access via stored procedures is a great data access strategy. However, with any great strategy there can be barriers to adoption. When dynamic SQL is introduced into the equation the current basic approach to leverage Ownership Chains as shown in the previous section fails. Let’s look at another example:

USE AdventureWorks2012;
GO
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'Person.GetPersonSearch')
                    AND type IN (N'P', N'PC') )
    DROP PROCEDURE  Person.GetPersonSearch;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE Person.GetPersonSearch
(
@PersonType NCHAR(2) = NULL,
@FirstName dbo.Name = NULL,
@MiddleName dbo.Name = NULL,
@LastName dbo.Name = NULL
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT  BusinessEntityID,
                                           PersonType,
                                      Title,
                                      FirstName,
                                      MiddleName,
                                      LastName,
                                      Suffix
                               FROM    Person.Person ',
        @where NVARCHAR(MAX) = N'';
    IF @PersonType IS NOT NULL
        BEGIN
            SET @where += 'AND PersonType = @MyPersonType ';
        END
    IF @FirstName IS NOT NULL
        BEGIN
            SET @where += 'AND FirstName = @MyFirstName ';
        END
    IF @MiddleName IS NOT NULL
        BEGIN
            SET @where += 'AND MiddleName = @MyMiddleName ';
        END
    IF @LastName IS NOT NULL
        BEGIN
            SET @where += 'AND LastName = @MyLastName ';
        END
    IF LEN(@where) > 0
        SET @sql += 'WHERE ' + RIGHT(@where, LEN(@where) - 3);
    EXEC sys.sp_executesql
        @sql,
        N'@MyPersonType NCHAR(2), @MyFirstName dbo.Name, @MyMiddleName dbo.Name, @MyLastName dbo.Name',
        @MyPersonType = @PersonType,
        @MyFirstName = @FirstName,
        @MyMiddleName = @MiddleName,
        @MyLastName = @LastName;
END;
GO
EXEC Person.GetPersonSearch
    @FirstName = N'Britta',
    @MiddleName = N'L',
    @LastName = N'Simon';

Example 2 - stored procedure definition uses dynamic SQL

If a user were granted permission to execute the stored procedure in Example 2, they could obviously execute that procedure successfully, however they would encounter the following error if they were not also granted select permission on Person.Person:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'Person', database 'AdventureWorks2012', schema 'Person'.

The reason why this error occurs is because of a broken ownership chain. Although Person.GetPersonSearch and Person.Person are owned by the same database principal, namely the owner of the Person schema dbo, when dynamic SQL is executed, any existing ownership chain is broken. This means all permissions must again be checked to potentially begin a new ownership chain. In this case it means that the user executing the stored procedure would require SELECT permission on Person.Person for the stored procedure to successfully complete and deliver results to the caller. If you remember our problem case, granting permissions to tables referenced within the dynamic SQL is what we want to avoid having to do.

The Solution

With the help of a Loginless Database User, some carefully crafted security scripts and use of WITH EXECUTE AS within our stored procedures we can achieve our goal of allowing users to execute stored procedures that employ dynamic SQL to access data without granting those users permission to select directly from any of the tables referenced within the dynamic SQL.

Here are the steps:

  1. If you have not done so already create the stored procedure in Example 2 above, Person.GetPersonSearch, in your AdventureWorks2012 database.

  2. Create a low privilege login and database user that you want to allow to execute the stored procedure that contains Dynamic SQL, but whom you do not want to grant select permission to any tables. You must create a separate login and user for this test because your login likely is in the sysadmin role and therefore would not be a valid login to use for testing. 

    USE master;
CREATE LOGIN LowPrivilegePerson WITH PASSWORD = 'hidden', CHECK_POLICY = OFF;
USE AdventureWorks2012;
CREATE USER LowPrivilegePerson FROM LOGIN LowPrivilegePerson;

  3. Grant permission to the new User to execute the stored procedure that contains dynamic SQL. 

    USE AdventureWorks2012;
GRANT EXECUTE ON Person.GetPersonSearch TO LowPrivilegePerson;

  4. Connect to your database instance using the LowPrivilegePerson Login and attempt to execute stored procedure Person.GetPersonSearch. 

    USE AdventureWorks2012;
EXEC Person.GetPersonSearch
    @FirstName = N'Britta',
    @MiddleName = N'L',
    @LastName = N'Simon';

    Notice you will receive this error:

    Msg 229, Level 14, State 5, Line 1

    The SELECT permission was denied on the object 'Person', database 'AdventureWorks2012', schema 'Person'.

We have recreated the problem case. At this point, facing the prospect of arriving at a working solution, many people will simply resort to granting LowPrivilegePerson select permission on Person.Person and living with the risk that the user may figure out they can select directly from Person.Person, therefore circumventing the stored procedure and potentially violating some company policy or industry regulation. In order to avoid granting select permission on Person.Person to LowPrivilegePerson thereby maintaining our data access interface we will employ a Loginless Database User and the WITH EXECUTE AS clause.

First, let's create a new Database Role:

USE AdventureWorks2012;
CREATE ROLE DynamicSQLRole;  

Next, create a new Loginless Database User: 

USE AdventureWorks2012;
CREATE USER DynamicSQLUser WITHOUT LOGIN;

Now add the Loginless Database User to the Role. In SQL Server 2012, use this code:    

USE AdventureWorks2012;
ALTER ROLE DynamicSQLRole ADD MEMBER DynamicSQLUser;

In SQL Server 2008 or SQL Server 2005, use this code: 

EXEC sys.sp_addrolemember
    @rolename = 'DynamicSQLRole',
    @membername = 'DynamicSQLUser';  

Now we will grant the role permissions to select from the tables referenced by the dynamic SQL:

USE AdventureWorks2012;
GRANT SELECT ON Person.Person TO DynamicSQLRole;

Alter the stored procedure Person.GetPersonSearch to add a WITH EXECUTE AS clause: 

USE AdventureWorks2012;
GO
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'Person.GetPersonSearch')
                    AND type IN (N'P', N'PC') )
    DROP PROCEDURE  Person.GetPersonSearch;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE Person.GetPersonSearch
(
@PersonType NCHAR(2) = NULL,
@FirstName dbo.Name = NULL,
@MiddleName dbo.Name = NULL,
@LastName dbo.Name = NULL
)
WITH EXECUTE AS 'DynamicSQLUser'
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT  BusinessEntityID,
                                           PersonType,
                                      Title,
                                      FirstName,
                                      MiddleName,
                                      LastName,
                                      Suffix
                               FROM    Person.Person ',
        @where NVARCHAR(MAX) = N'';
    IF @PersonType IS NOT NULL
        BEGIN
            SET @where += 'AND PersonType = @MyPersonType ';
        END
    IF @FirstName IS NOT NULL
        BEGIN
            SET @where += 'AND FirstName = @MyFirstName ';
        END
    IF @MiddleName IS NOT NULL
        BEGIN
            SET @where += 'AND MiddleName = @MyMiddleName ';
        END
    IF @LastName IS NOT NULL
        BEGIN
            SET @where += 'AND LastName = @MyLastName ';
        END
    IF LEN(@where) > 0
        SET @sql += 'WHERE ' + RIGHT(@where, LEN(@where) - 3);
    EXEC sys.sp_executesql
        @sql,
        N'@MyPersonType NCHAR(2), @MyFirstName dbo.Name, @MyMiddleName dbo.Name, @MyLastName dbo.Name',
        @MyPersonType = @PersonType,
        @MyFirstName = @FirstName,
        @MyMiddleName = @MiddleName,
        @MyLastName = @LastName;
END;
GO

Example 3 - stored procedure definition uses dynamic SQL and WITH EXECUTE AS

Now when you attempt to execute the stored procedure Person.GetPersonSearch while connected as the LowPrivilegePerson login the ownership chain is still broken within the stored procedure when the dynamic SQL runs however the data access done within the dynamic SQL will be performed under the security context of the loginless database user, DynamicSQLUser. Because DynamicSQLUser has been granted permissions to select from Person.Person via membership in Database Role DynamicSQLRole, LowPrivilegePerson can have success yet still have no explicit permissions to select from Person.Person.

Disadvantages

There is always at least one disadvantage to every technique, even if it amounts to something you consider minor. There are also alternatives to every technique. If you think there aren't, then you aren't thinking it through. The use of WITH EXECUTE AS amounts to an impersonation of another user inside the scope of the stored procedure where it is used. In essence, the code within Person.GetPersonSearch will execute as if DynamicSQLUser had executed the stored procedure. This could mean trouble for some auditing systems that are not attuned to the idea that the security context within a SQL Server session may be explicitly changed during execution.

For catch-all queries where you are selecting data this could affect an audit specification that is important to maintain compliance with an industry regulation. If you are using dynamic SQL to carry out DML operations, then the risk can be even higher if the auditing functions being used to determine who made a change are also not expecting the use of WITH EXECUTE AS. An example of a potentially bad side-effect: calling the CURRENT_USER function within the revised version of Person.GetPersonSearch will return DynamicSQLUser whereas calling the ORIGINAL_LOGIN() function will return LowPrivilegePerson. Make sure you perform your due diligence before and after implementing this technique to ensure existing or future auditing capabilities will not be adversely affected by the implementation.

As an alternative to using WITH EXECUTE AS in your stored procedures, signing your stored procedures with a certificate offers similar benefits in terms of being able to avoid granting select permissions on referenced objects. With certificate signing the proverbial "technical bar" is a little higher and the technique has its own set of properties that can be barriers to adoption but all in all it is worth a separate and detailed examination before deciding which technique may work best for you. I must leave it there for now but may cover certificate signing as a way to solve this same problem in a future article.

Conclusion

Stored procedures are an important component of securing your data and implementing a successful data access strategy but using them for all data access does not have to result in the complete avoidance of dynamic SQL in those specific cases when it would be beneficial. In this article I have highlighted one of the many uses for Loginless Database Users which specifically allows conscientious database developers the flexibility to employ dynamic SQL data while still maintaining a consistent data access interface via stored procedures. Thank you for taking the time to read this article.

References

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.76 (17)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.76 (17)

You rated this post out of 5. Change rating

Related content