Tag Archives: security

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.

Random Security Trap 001

The downside of having been around for a while is that you (hopefully) have learned something. When new features are released it can be easy to stumble right over the obvious. Here is a tale of one such bloodied nose. 😉

The Setup: There is a physical server (Box 1) running Windows Server 2008 R2. On Box 1 is installed SQL Server 2008 R2 (SQL 1) as a default instance. A second physical server (Box 2) is also running Windows Server 2008 R2; Box 2 also has installed SQL Server 2008 R2 (SQL 2) as a default instance. Both boxes are on the same domain. There is a Windows user account configured as a Windows Domain Administrator (User).

The Twist: User can log into Box 1 and can log into SQL 2 from Box 1, but cannot log into SQL 1 from Box 1. User can log into Box 2 and can log into SQL 1 from Box 2, but cannot log into SQL 2 from Box 2. Furthermore, when connected to SQL, no databases are accessible and very few SQL artifacts are visible, despite User being a domain administrator.

The Solution: It turns out that this unexpected circumstance was the result of the overlap of two “new” features; user account control (UAC) in Windows and the default security model in SQL Server.

Issue 1 – UAC: Windows Vista and Windows Server 2008 were the first Windows operating systems to introduce UAC, a rule set that, simply put, prevents administrative privileges from being inherited by default. Windows Server 2008 R2 also includes UAC. Thus, even though User is logged into Box 1 as an administrator, when launching SQL Server Management Studio (SSMS) normally, it runs with the permissions of a normal user. Hence, trying to login to SQL 1 from Box 1 will not work because User does not have a defined SQL login (recall that User is connected to Box 1 as a member of the Windows Domain Administrators group). To work within the UAC constraints, User simply needs to explicitly run SSMS as an administrator and thus specify that administrative permissions are inherited by SSMS. This will enable SQL login… but User will still only have access as a public user — not as a member of the sysadmin role. (Note that User can connect to SQL 2 from Box 1 without explicitly running SSMS as an administrator because credentials are passed externally despite UAC; credentials are only restricted by default on the machine into which the user is connected.)

Issue 2 – default SQL security: Beginning with SQL Server 2008, the BUILTIN\Administrators group is no longer added as a SQL sysadmin by default. This change is part of an intentional effort by Microsoft to separate SQL administrative privilege from Windows administrative privilege. Thus, if a Windows administrator attempts to login to SQL, they will be granted access as a public user only. To address this issue, create a SQL login for Windows administrators and assign it the sysadmin role (or whichever role is desired). Keep in mind that a Windows administrator SQL sysadmin login was not created by default for a reason; consider the security implications carefully before creating such a login.

To enable User to have the expected experience requires addressing both issues. Issue two without one will expand visibility when logged in to SQL, but will not allow connection to the local SQL instance; issue one without two will allow connection to the local SQL instance, but User will still have very little SQL access as a public user.

The Lesson: Stay current on application features by version. It is almost always the case that subsequent software versions offer a superset of features over the prior versions; but here we have a case where two separate applications had a “regression” of privilege to enhance security, and the experience resulting from the application overlap was unusual-looking indeed.