|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Pragmatic TSQL Programmer > Posts > Application Roles in SQL and why to use them
|
4/10/2007
About a year ago, a manager of mine asked me to look into converting our major client-server C#/SQL2K application over to application role security. The business drivers were pretty clear:
- We needed to prevent our power users from doing ad-hoc queries on the server, misinterpreting the results from their query and then presenting that data to others as "facts." We wanted to limit the data leaving our application to the data that we published via reporting services, that we had validated was accurate and meaningful. (I'll leave the exact situations that caused this business driver to the reader's imagination.)
- We needed to keep the application working.
- We needed to be able to be able to continue using integrated security so that the internal auditing mechanism that logged the Active Directory user of the application would not have to be rewritten.
As I read up on the concept of using Application Role security, I was impressed. This would allow you drastically more granular control over what a user can do to your database. Instead of saying, "User A can write to Table B," we can now say "User A can only write to Table B through the algorithms that I have defined inside my application." Very cool. Very useful.
I am a big fan of using Application Role security in my apps.
How Application Roles work in the real world
There are a few steps to make Application Role security work for your application.
- If you have not already done so, set your connection string to use integrated authentication. (If you are not using integrated authentication, then you will not have the auditing ability of Application Role Security, and there is very little reason to use Application Role Security.) If you are still on SQL 2000, you must disable Connection Pooling by adding Pooling=False; to your connection string.
- Establish that the NT AUTHORITY\Authorized Users group has a login on the SQL server. This only needs to be done once per instance.
- Set the permissions of the NT AUTHORITY\Authorized Users login to be able to access the database, but to have no other read or write permissions in the database. We do this by adding them to the Public role, but if you have added permissions to the Public role, you may need to create a new role that has the same extremely limited permissions of the Public role.
- Create the application role in the database and assign it a password. Either use the GUI tools in SSMS or CREATE APPLICATION ROLE.
- Secure the password in a way that your application can use it. Don't do boneheaded things like leaving the password in your .config file in plaintext. Choose a reliable method to secure it so that it can be changed if the password is compromised and needs to be changed.
- Modify the data access layer (DAL) of your application to open connections and then immediately issue an sp_setapprole command on the connection to set the application role security. Note: If you are using SQL 2005 and want to use connection pooling, you need to keep track of the cookie, a varchar(8000), that is created when you execute this command.
- Do your work on the connection. If you would like to do auditing or access the current user's Windows login name, use the suser_sname() function or SYSTEM_USER to retrieve the Active Directory account that is using integrated security.
-
If you are using 2005 and want to use connection pooling, you need to issue a sp_unsetapprole command before closing the connection. This can be mildly tricky, since there is no useful event fired just before closing a connection that can be handled, and since you cannot inherit from the SqlCommand object, you can't add the functionality directly as a child class. Your primary options are:
- Write a wrapper class for the SqlCommand class and add the sp_unsetapprole command before closing the wrapped connection. (This is what I did)
- Meticulously add the sp_unsetapprole command everywhere in your DAL where a connection closes. (Being lazy, I don't like this method.)
Drawbacks
- Unfortunately, almost no SQL tools have good support for Application Roles Security. There isn't a lot of info on the web about them either.
- Reporting Services requires you to create your own Data Processing Extension (aka Custom Data Extension) in order to use Application Role Security. This is fairly tedious and requires a very large amount of boiler plate code to get working. Plus, there aren't a lot of useful DPE examples on the web that I have found. If you are going down this road and are stuck, email me, and I will see if I can get you some sample code that I use.
- For this to work in ASP.NET, you have to use integrated windows authentication or have users manually log in to your web app. Otherwise, you will get the anonymous IIS user when you use suser_sname() function or SYSTEM_USER.
You can mitigate the drawbacks above if you use SQL Server logins to utilize when Application Role Security is impractical. Just create a SQL login that has the needed permissions to use Reporting Services (or whatever) and just use that when appropriate.
Conclusion
Application Role Security is a great way to keep your data safe from internal power users. It allows your application to allow users to make the changes you want them to make, without opening up your data to an overzealous user making an inaccurate report in Excel and submitting it to management. I would love to see the standard SQL tools (Reporting Services team, are you reading this?) get some expanded support for Application Role Security, and hopefully future versions of ADO.NET will enhance the SqlConnection object to have an easy to handle event where the sp_unsetapprole command can be issued.
Even with its shortcomings, Application Role Security is a great way to improve security in your applications. Highly recommended. |
|
| | | | | |