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?
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:
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.
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:
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:
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.
- 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.
Last Update: 2018-01-10
About the author
View all my tips