Random Security Trap 002

For post number two in my “Random Security Trap” series, let’s take a look at an issue that may cost you a few minutes of head-scratching if you haven’t seen it before. First, the business setup: you need to enable a group of users to have read-only access to a database, but also have controlled insert permissions to one table. A common way to solve this is to create a database user, add it to the database db_datareader role, create a stored procedure to manage the insert operation, and then grant execute permission on the stored procedure to the database user. And, in most cases, that solution works just fine.

However… what if the insert operation is a little more complicated and uses dynamic SQL to perform the insert? Well, in that case, you may fall right into the trap. Let’s demonstrate.

We’ll begin by creating a SQL login to the server and then using that login for a database user with read-only permission.

-- set context to the master database
USE [master];
GO

-- if the ReadOnlyLogin already exists, drop it
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ReadOnlyLogin')
    DROP LOGIN ReadOnlyLogin;

-- create a ReadOnlyLogin login
CREATE LOGIN ReadOnlyLogin
WITH
    PASSWORD = N'ReadOnly'
    , DEFAULT_DATABASE = devDB
    , CHECK_EXPIRATION = OFF
    , CHECK_POLICY = OFF
    ;

-- set context to the working database
USE devDB;
GO

-- if the ReadOnlyUser database user already exists, drop it
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ReadOnlyUser')
    DROP USER ReadOnlyUser;

-- create a ReadOnlyUser database user for the ReadOnlyLogin login
CREATE USER ReadOnlyUser
FOR LOGIN ReadOnlyLogin
WITH DEFAULT_SCHEMA = dbo;

-- add the database user to the datareader role
EXECUTE sp_addrolemember
    @rolename = N'db_datareader'
    , @membername = N'ReadOnlyUser'
    ;

Okay. Now that we have our login and database user created, let’s create a table so we can demonstrate controlling insert operations. Since we’ll end up simulating logging in as the ReadOnlyLogin, we’ll use the table to capture information about our testing operations.

-- if the sample table exists, drop it
IF OBJECT_ID(N'tbl', N'U') IS NOT NULL DROP TABLE tbl;

-- create the sample table
CREATE TABLE tbl (
    ID INT IDENTITY(1,1) NOT NULL
    , DateStamp DATETIME2(3) CONSTRAINT DF_tbl_DateStamp DEFAULT(GETDATE())
    , [Login] SYSNAME
    , ContextLogin SYSNAME
    , [User] SYSNAME
    , IsDynamic BIT
    );

With our table in place, let’s create a procedure to insert data to the table. We’ll capture information about the login and execution context as a means of confirming the execution state related to our business case.

-- if the insert procedure already exists, drop it
IF OBJECT_ID(N'uspInsert', N'P') IS NOT NULL DROP PROCEDURE uspInsert;
GO

-- create the demo procedure
CREATE PROCEDURE uspInsert (
    @bolUseDynamic BIT = 0
    )
AS
BEGIN
    IF @bolUseDynamic = 1
    BEGIN
        -- insert a record via dynamic SQL
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = N'INSERT INTO tbl ([Login], ContextLogin, [User], IsDynamic)
            VALUES (ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME(), 1);'
        ;
        EXECUTE (@sql);
    END
    ELSE
        -- insert a record via static SQL
        INSERT INTO tbl([Login], ContextLogin, [User], IsDynamic)
        VALUES (ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME(), 0)
        ;
END
GO

The last step in our setup is to grant execute permission to the database user.

-- grant the ReadOnlyUser permission to execute the procedure
GRANT EXECUTE ON uspInsert TO ReadOnlyUser;

Okay. Everything is in place. Let’s run a test! We’ll run the first test under our own login simply to confirm that the procedure works and captures the information we need to see.

-- test the procedure
EXECUTE uspInsert @bolUseDynamic = 0;
EXECUTE uspInsert @bolUseDynamic = 1;

-- confirm results
SELECT * FROM tbl;

So far, so good. Next, let’s run the same two procedure executions under the context of the ReadOnlyLogin. To achieve this, we could reconnect under the other login. Alternately, let’s just impersonate it instead!

-- test the procedure
EXECUTE AS LOGIN = 'ReadOnlyLogin';
EXECUTE uspInsert @bolUseDynamic = 0;
EXECUTE uspInsert @bolUseDynamic = 1;
REVERT;

-- confirm results
SELECT * FROM tbl;

As you can see, the second procedure execution failed when executed under the context of the ReadOnlyLogin:

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'tbl', database 'devDB', schema 'dbo'.

Why did it fail? Because even though the dynamic SQL is executed within a stored procedure on which the ReadOnlyLogin is granted permission, dynamic SQL executes within a separate session. The login impersonation is passed through, but the sql is no longer strictly within the context of the procedure. And outside of the procedure, the ReadOnlyLogin does not have permission to insert into the “tbl” table.

As ever, let’s be sure to clean up after ourselves.

-- drop objects
IF OBJECT_ID(N'uspInsert', N'P') IS NOT NULL DROP PROCEDURE uspInsert;
IF OBJECT_ID(N'tbl', N'U') IS NOT NULL DROP TABLE tbl;
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ReadOnlyUser')
    DROP USER ReadOnlyUser;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ReadOnlyLogin')
    DROP LOGIN ReadOnlyLogin;

For additional information on pitfalls of dynamic SQL related to execution context, give this post a read.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: