A Few tricks for testing security by Steve Jones

This article first appeared in the SQLServerGeeks Magazine.
Author: Steve Jones
Subscribe to get your copy.

One of the things that I have often seen developers ignore in building applications is security. Too often they either do not understand or take the time to test how different logins and users might interact with their code and often assign too many permissions. In fact, one of the main problems in SQL Server for decades has been developers requiring dbo, or worse, sa permissions for their code.
This is not because the application needs those permissions, but because the developers didn’t bother to create a better security structure.
In this article, I will look at a couple of tricks that can help you ensure that you easily incorporate a security model as you are writing your application that allows you to test in the same way a user will.

Preparation is Key
When a developer is working on an application, often they connect to SQL Server with their own credentials. These are often sa or dbo, which gives the developer a skewed view of the security model. SQL Server tries to be secure, so new accounts don’t have rights to anything by default.
A good technique when you start an application is to create a couple of users and roles to help you easily test your code. These give you different views into how your application is actually working for non-privileged users. Here is the type of script that I keep around for beginning work on any application:

CREATE LOGIN Joe_Admin WITH PASSWORD = 'Dem012#4'
CREATE LOGIN Joe_User WITH PASSWORD = 'Dem012#4'
GO
USE MyNewApp
GO
CREATE USER Joe_Admin FOR LOGIN Joe_Admin
CREATE USER Joe_User FOR LOGIN Joe_User
GO
CREATE ROLE AppAdmin
CREATE ROLE AppUser
GO
ALTER ROLE AppAdmin ADD MEMBER Joe_Admin
ALTER ROLE AppUser ADD MEMBER Joe_User

Note that even if this is a legacy application, I’ll add these logins and users to help me test how things work.

Now, as I build objects, I will assign them permissions for the roles. For example, if I add a table and stored procedure, I’ll use a script like this:

CREATE TABLE OrderHeader
(   OrderHeaderID INT
  , OrderDate     DATE
  , Complete      BIT);
GO

CREATE OR ALTER PROCEDURE GetOrderHeader @OrderHeaderID INT
AS
BEGIN
    IF @orderheaderID IS NULL
        SELECT OrderHeaderID, OrderDate, Complete FROM orderheader;
    ELSE
        SELECT
              OrderHeaderID
            , OrderDate
            , Complete
        FROM  orderheader
        WHERE OrderHeaderID = @OrderHeaderID;
END;
GO
GRANT EXECUTE ON dbo.GetOrderHeader TO AppUser
GO

Once this is complete, I can begin to test my security by simulating one of these users. I can do this in one of two ways. First, I can open a new query window and log in as a user. For quick testing, I’ll log in as Joe_Admin in SSMS.

In my query window, I can see which user I have logged in within the status bar.

When I run this script, I get an error. It’s a permission error, because I didn’t assign rights to this object to either the user or role.

I can open a second window for Joe_User and then check their permissions, which we can see below are correct.

The other option I have is to use the EXECUTE AS command to change my user context. If I am developing code in a window, I can run an EXECUTE AS [login | user] with the name of the login or user, and then run my code. This changes my security context, so I can test code. You can see this working below:

With this code, I don’t need to change query windows. I do, however, need to change back to my developer context with REVERT at the end. This allows me to use my developer credentials to change code, but switch to other users.

Since I can get confused, I do often want to add a couple of lines to my code. First, I’ll use multiple EXECUTE AS statements to quickly switch users with the same code. You can see below I have one commented out and one executing. I also want to be sure I know which user was running code, so I will add a USER_NAME() SELECT to allow me to look at results and see which user is running code. With either of these techniques, I can keep testing the code I am writing under different contexts to be sure that when the application is deployed, it does not require any special privileges.

Existing Security Context
For many existing applications, I find that developers or administrators have often granted rights to individual users, especially when a shared login is used for something like a web application. While this works, it is cumbersome and difficult to maintain over time.
In these situations, I would start to use roles as a developer, in addition to granting individual rights. This helps me quickly add a new user to test something, but also starts to show other developers or administrators how cumbersome individual rights are. This is the first step to refactoring to a better security model. This is true whether you use SQL, Windows, or AAD authentication.
If I have an existing user from production, I might ensure they exist on this system, with a standard password for the login. I can script out all the permissions and ensure the user on my development instance works the same as production knowing production credentials or having simple security.

Summary
Having a known set of logins and users makes development work easier. I prefer to use standard names on my development databases, with standard roles that simulate the way that different users will connect in production. This ensures I am testing security under contexts other than my own.
I have shown two ways to do this, with separate query windows for each login and by changing context. Of these, I find that separate windows, often on separate monitors allow me to keep developing in one window without confusion. I can easily copy and paste code from my developer connection to a normal user connection for easy testing. The context switch sometimes causes me issues, especially when I create errors and the REVERT doesn’t execute.
It is important to test your application, both the features and security. Many of the software bugs and problems experienced by users come from inadequate testing. With many systems under constant probing and attack, good development habits can help ensure we do not accidentally release code without vulnerabilities that could cause data breaches. This also helps ensure we deploy code that our users can execute without simple security errors.

This article first appeared in the SQLServerGeeks Magazine.
Author: Steve Jones
Subscribe to get your copy.