Application Database Security Design Part 2 - Multiple Levels of Access to SQL Server

By: K. Brian Kelley | Comments | Related: > Security

Problem

I'm part of a new development effort which will use a SQL Server back-end. We want a secure database layer. What is the best way to architect/design this?

Solution

This is a big topic, so let's break this down into multiple tips for easier understanding. In the first part, we talked about how the application and, potentially, the users connect to the database. In this second part, let's look at the situation where the application requires multiple access levels with respect to accessing and modifying the data. An example will make this clearer.

Let's consider an application which tracks loans. For this application, there are three different levels of access:

Loan Department Manager
Loan Officer
Financial Auditor

A Loan Department Manager should be able to view and modify any loan. A Loan Officer should only be able to view and modify his or her own loans. A Financial Auditor should be able to view all loans, but not modify any of them. Here we have three levels of access for the application. There are many applications that require the same functionality. How these multiple levels of access are controlled is primarily determined by how the application authenticates. Let's consider these typical scenarios:

The application connects as a service account or single login.
The application connects as the user.
The application connects as the user, but if the user connects to the database directly, a different level of access is required.

The Application Connects as a Service Account or Single Login

If the application connects as a single login, then just with the login alone SQL Server cannot handle multiple levels of access. It has no way of differentiating one user from the other with just the login name. Either the application is going to have to handle the differing levels of access within the application code itself or it's going to have to pass more information to SQL Server. The first means there's nothing else to do with regards to how SQL Server has access. Give the login the application uses the permissions it needs to do everything it has to do and from a database perspective we're done. However, if the application can pass in more information, then with T-SQL code we can let SQL Server do some of the "heavy lifting."

Stored Procedures and Functions and the use of Parameters

The easiest way is if the information that's needed can be passed in as a parameter to a stored procedure and all access to the data is handled through stored procedures. Let's go back to our example. Let's say there are the following tables:

Loan
User
BusinessRole
RoleMembership

The data we care about is in the Loan table. However, to determine who can see what, we need other tables to be able to associate a user with access. I’m using a role-based approach example, hence the reason for three tables instead of just one. Assume there’s the typical join scenario to determine the roles a user is a member of in order to finally determine access. I’m also leaving out how the roles apply to specific access because that’s going to be specific to the application. In some cases certain roles will have access to specific loans, for instance, while in other apps the role will determine what you can do with a loan: view it versus modify or delete it. As a result, I’ve intentionally left any details around actual permissions out.

In order to make this work from a stored procedure perspective, we only need to pass in the information related to the loan as well as who the user is:

CREATE PROC GetLoan @LoanID int, @User varchar(50)
.
.
.

Because the application is connecting with a single account, we have to rely on the application to pass the user’s information in. If we’re using stored procedures, the easiest way is to add a parameter for this, just as I’ve prototyped here. This is a case where we don’t want a default value for @User. We want an explicit value. As long as every stored procedure which is called by the app includes a parameter for user, we’re in good shape.

But what if we’re retrofitting an existing application? What if we can’t change the parameters of the stored procedures or what if we’re using direct queries? There’s a potential solution for that as well.

SET CONTEXT_INFO

There is a session like variable in SQL Server, CONTEXT_INFO. It’s specific to the connection. So if we set it, anything within that connection can use it. There’s a catch with CONTEXT_INFO, and that it uses varbinary. As a result, we will need to do a conversion to get the information we want to pass into CONTEXT_INFO. For instance:

DECLARE @Context varbinary(128);
SET @Context = CONVERT(varbinary(128), 'John Doe');
SET CONTEXT_INFO @Context;

However, we’re not quite done. Just as must do a conversion to get information into CONTEXT_INFO, we must to the same to get useful information back out. Here’s what happens when we just get CONTEXT_INFO:

SELECT CONTEXT_INFO();

We see that we get back a binary stream:

Therefore, we must do the explicit conversion:

SELECT CONVERT(varchar, CONTEXT_INFO());

And now we get more along the lines of what we were expecting:

I’ve seen this used when auditing had to be added after the fact. An application had a set of previously defined stored procedures and it would take a massive engineering effort to redo all of them and fix every call in code. Since the application had methods for opening the database connection, the use of CONTEXT_INFO was inserted there. In the database, triggers were built which utilized CONTEXT_INFO.

However, the topic at hand is multiple levels of access, not auditing. So how would we make CONTEXT_INFO work in that situation? If the application is using queries built in the application, there’s no point using CONTEXT_INFO. You’ll have to modify the queries within the application to take into account CONTEXT_INFO, so you are better served building the queries to include the user. There’s no point in setting a value in CONTEXT_INFO and then extracting it.

Where this CONTEXT_INFO does make sense is if you’ve had to implement multiple tiers of security in an existing application which previously didn’t have it and the application is using stored procedures. You likely can save work if you don’t have to redo the application code. Then you take a similar approach to the audit example I gave, where the connection method you’ve built (hopefully you’ve built that separately) does the job of setting CONTEXT_INFO. Then you modify the stored procedures to retrieve CONTEXT_INFO and use it to determine the user’s access.

The Application Connects as the User

In this case, the recommended approach is rather simple:

Create database roles corresponding to the levels of access.
Make the users members of those roles.
Base your security on those roles.

You’d still likely have a BusinessRole table and the RoleMembership table from the example above. After all, there will always be roles in a database that don’t correspond to one an application requires (such as db_owner, which has no meaning to an application).

Since the application is connecting as the user, we just need to get that information from SQL Server. Within the context of the database, there are two equivalent ways of getting that:

CURRENT_USER
USER_NAME()

Either works.

Different User Access Outside the Application

What about the case where the application connects as the user but if the user connects from outside the application, the user should have a different access level? In this case, the only real solution is to use application roles. Here’s how things would need to be set up:

You set the minimal permissions for the user, assuming the user is coming from outside the application.
You create the appropriate application roles with the differing layers of access.
The application calls sp_setapprole to set the application role so the user can gain the appropriate access from within the application.

Sounds easy enough, right? Unfortunately, it’s not if you have the situation where a user needs multiple layers of access. Only one application role can be set at a time and while you can unset an application role so you can set a new one, it means the application has to handle flipping back and forth. That’s a real headache.

Regardless of how many roles a user may be a member of, the application still has to execute the appropriate sp_setapprole call in the first place. Part of successfully executing that call is passing in the correct password. Therefore, there’s the additional overhead of managing and storing those passwords securely. So while application roles sound great initially, the issues with managing them has meant they haven’t been an often chosen solution.

Next Steps

Read Part 1 of this series!
See other community questions with regards to SQL Server security.
Learn how you can leverage SQL Server 2016’s Row-Level Security to restrict access.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips