Data Management

Secure SQL Server: Application roles

When it comes to managing database security, at times you'd rather manage security for an application rather than for individual users. In SQL Server, this administrative flexibility is granted through application roles.

Coauthored by Mike Gunderloy

Welcome to the fourth installment of CNET/Builder’s series on SQL Server security. So far, you know how to set up your SQL Server, manage users and groups, and manage objects and permissions. These concepts form the basis for a fine-grained security model that lets you manage security for individual users. But at times, you’d rather manage security for an application rather than for users. That’s when you need to know about application roles, which are the subject of this article.

Builder's series on SQL Server security
Our goal is to give you the tools and confidence you need to set up SQL Server securely, so that your data is protected from accidental or deliberate theft and corruption. Our series starts with some of the basic decisions and techniques for making sure that you have a secure platform to build on. Throughout the series, we'll concentrate on SQL Server 2000, which introduces some new security features to help protect your data. In case you've missed the previous articles in this series, you can read them here:

Overview of application roles
You already know about regular roles, which contain users and allow you to manage security in bulk. For example, you might have an accounting role in your database, to which you assign all members of the accounting department. By managing the permissions of the accounting role, you manage the permissions of every member of the role.

By contrast, application roles contain no users, though you can still assign permissions to application roles. Application roles are designed to let an application take over the job of authenticating users from SQL Server. When an application activates an application role by supplying the proper password, anyone using that application gets the permissions assigned to that application role.

For example, you might have an accounting application role that was assigned the necessary permissions to perform accounting tasks in your database. Somewhere in your general ledger application, you would code the password for this application role. Then anyone using the application would be able to access the accounting data. In other words, the database administrator can restrict access based on the specific application in use instead of relying on the user's individual or group permissions. Your permissions are based solely on your ability to gain access to the application.

Working with application roles
Using application roles to secure a database application is a four-step process:
  1. Create an application role.
  2. Assign permissions to the new role.
  3. Create a connection to the server.
  4. After connecting to the server, activate the application role.

From a user standpoint, the user connects to the application, and in doing so, connects the database to SQL Server. Although the user is probably oblivious to what's going on under the surface, the connection process executes a system stored procedure that passes the username and password. Once accepted, the user's existing permissions (if any) are temporarily usurped by the permissions granted by the application role. At this point, the only way the user can resume user or group permissions is to disconnect from the application and SQL Server and then reconnect to SQL Server using a different application.

A few points about application roles are worth noting before we continue:
  • Application roles are supported by both authentication modes (Mixed and Windows Authentication).
  • Application roles don't contain members like other roles do, nor can you add a group or role to an application role.
  • Application role permissions exist only within the database.

Step one: Creating application roles
You can create a new application role in two ways. You can execute a Transact-SQL statement or you can use SQL Server Enterprise Manager. You can choose either method, depending on whether SQL statements or the graphical user interface is easier for you to use.

Using the Enterprise Manager method, locate the database in question and expand its components in the utility's left pane. Right-click the Roles node and choose New Database Role to display the Database Roles Properties window. Enter a new name for the new role, as we've done in Figure A, and select the application role option to indicate the role type. (The Permissions button will remain disabled.) Enter a password for the new role and click OK. Doing so will add the new role to the hierarchy. Just remember that right now, the new role has no permissions—we'll discuss that in the next section.

Figure A
Use the Enterprise Manager to create a new application role.

If you prefer, you can accomplish the same task by executing the sp_addapprole stored procedure using the following syntax:
sp_addapprole [@rolename = ] 'rolename', [@password = ] 'password'

Here, rolename identifies the new role by name and password is used to activate the role. Both arguments are required (although you don't have to explicitly include the argument name) and there are no default values. For instance, to create the same role we created in the Enterprise Manager example, you'd run this statement:
EXEC sp_addapprole 'NorthwindAppRole', 'mypassword'

The stored procedure returns the value 0 or 1 to denote success or failure, respectively. In other words, if the procedure returns the value 1, you know the system rejected the new role. Just why might require a bit of troubleshooting, but consider the following before expending too much energy elsewhere: The rolename argument can contain from 1 to 128 characters. All letters, symbols, and numbers are allowed except the backslash character (\), a Null value, or an empty string.

Step two: Assigning permissions
After creating the new role, you must define its permissions. Using the Enterprise Manager, double-click the new role in the Roles pane to redisplay the Database Roles Properties window. This time, the role type options are disabled and the Permissions button is enabled. Click the Permissions button to display the Permissions tab shown in Figure B. (We won't discuss specific permissions in this article.)

Figure B
Assign permissions to the new application role.

You can assign permissions to an application role the same way you assign permissions to a user or regular role. Currently, there are no permissions assigned to the application role—select the List Only Objects With Permissions For This Role option to confirm the current permissions. Selecting a permission type for an object will enable the Columns button at the bottom of the page. Click this to further limit access to specific columns in the table or view rather than allowing access to all the data in the object.

Using T-SQL, use the GRANT keyword in this form, where applicationrole identifies the new role to which you're assigning permissions:
GRANT ALL | keyword1 [, keyword2, …]
[ON table | view (column1, [column2, … ]) |
ON storedprocedure | extendedprocedure |
ON udf]
TO applicationrole

For instance, to add SELECT permission to all the data in the Employees table in Northwind, you'd run a statement similar to the following:
ON Employees
TO NorthwindAppRole

You could limit access to specific data by limiting access to a specific column as shown in Listing A.

The ALL keyword grants all permissions to the application role. (For more specific information on granting permissions, look up GRANT in SQL Server Books Online or read the third article in this series, "Secure SQL Server: Identify user issues.")

Step three: Connecting to the server
Application roles are used exclusively from applications. This means, of course, that the application needs to connect to SQL Server before it can use an application role. Fortunately, application roles are supported by all of the SQL Server client APIs. So you can program the application to connect to SQL Server using any method you like—ODBC, OLE DB, or the .NET System.Data.SqlClient objects, for example—and the application role will work just fine.

Step four: Activating application roles
By default, an application role is inactive. To activate the role, use the sp_setapprole system stored procedure to activate an application role for a specific connection. When you do so, the connection permanently loses any and all earlier defined permissions for the duration of the connection. The user associated with the connection has only those permissions defined by the application role attached to the database to which they're currently connected.

Links to a second database by an application enforcing an application role are allowed access on the guest account to that database. The administrator must remember to grant the guest account in the linked-to database access to the data. Otherwise, the attempt to link will fail.

To activate the permissions associated with the application role, use sp_setapprole stored procedure from the client application, which takes the form shown in Listing B.

The final argument, encryptstyle denotes the ODBC Encrypt function. There are two possible values: None and Odbc. Encrypting the password isn't required, but certainly recommended when possible. You must use an ODBC or OLE DB connection from your application to SQL Server for password encryption to work. As with the earlier stored procedure, sp_setapprole returns a 0 or 1 to indicate activation success or failure, respectively.

Benefits of application roles
So why use application roles? By now, you can probably come up with your own list, but here’s ours:
  • Regular network administrators can manage data access without needing to consult the database administrator (DBA), simply by controlling who has access to an application.
  • You don’t need to worry about keeping track of changing users with SQL Server itself. Set up an application role and you can delegate that back to the network level.
  • You can limit data availability to a single application. For example, a user might be able to modify accounting information only when using the general ledger application, and not when he connects directly to SQL Server.

While certainly not applicable in every situation, application roles do give you more flexibility in how you manage access to SQL Server.

Coming up next
In the final article of this series, we’ll step back to take a look at some big picture application issues. You’ll learn about encryption in SQL Server. You’ll also learn about SQL injection, one of the most common vulnerabilities in SQL Server applications.

Mike Gunderloy and Susan Sales Harkins are coauthors of Que's Absolute Beginner's Guide to Microsoft Access 2002. Their latest collaboration, Absolute Beginner's Guide to Microsoft Access 2003 by QUE is due to be released this summer.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox