Application Roles: What Are They, Really?
Application Roles offer a better, more secure way for users to gain access to SQL Server data. Here's why.
- By Eric Johnson
- 12/07/2009
They have been around forever, but have you really ever implemented Application Roles in SQL Server? For that matter, do you really know what they are and how to use them? In this short tip, I hope to explain just that.
First off, we need to talk briefly about how application security can be implemented in SQL Server.There is some debate over which model is better and I am not endorsing any specific one here. Regardless of whether you use SQL Server Logins or Windows Authentication, you still have to decide whether an application will use a single login to access SQL Server (and all appropriate database objects), or allow each individual user to have their own login.
There are pros and cons associated with either choice, but I want to focus on a specific con of each user having a login. The biggest issue is that each user login has access to your server and to one or more databases. Does the user need to delete data as part of their job? If so, they will have this right whether they log in via an application or directly to the server. Often, the application controls what can and cannot be deleted based on a set of business rules; these rules usually don't exist on the SQL Server itself.
In short, if each user has their own login, they can access SQL Server directly and potentially cause some damage.
This brings us to application roles. You create them and assign permissions to them just like regular database roles but you can't put users in them. Instead, the goal of application roles is to provide a best of both worlds scenario for application and user security.
Here's how application roles work. You set up each user to have an account on the SQL Server with practically no rights. All they should be able to do is log in to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple parameters, including the name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only.
So what does this mean for security? As long as the password for activating the application role is only known to the application, your users will not have any rights when they login to the SQL Server directly. In order to have the permissions they need, they will be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual logins and still have a secure environment that uses the rules and filters in place within your applications.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.