Mastering Application Roles

Microsoft introduced role-based security with SQL Server 7.0. Server and database roles simplify security administration by letting individual users inherit permissions granted, revoked, or denied to those roles. Moving users in and out of roles is much easier than assigning individual permissions to users. At the database level, you can add custom roles. In addition to the standard database roles to which you can assign users, a second type of SQL Server database role exists—an application role. You don't assign users to an application role, as you would to other roles; instead, all users of the client application that activates an application role automatically share that role. The permissions granted to the application role are in force for the duration of the connection that activated the application role.

Application roles are session-based (connection-based) rather than user-based. To activate an application role, you run the sp_setapprole system stored procedure, which requires two parameters: role name and password. After an application activates an application role, SQL Server applies the permissions granted to the application role to the session that the application is using.

Application roles work with both Windows NT and SQL Server authentication, even though application roles don't apply directly to users. After executing the sp_setapprole stored procedure to invoke an application role, you can't disable or deactivate that role for the duration of that connection. SQL Server ignores permissions that an application user might have that are independent of the application role*SQL Server considers only the application role's permissions. To return a database user's original individual security context, the user must disconnect the application from SQL Server and reconnect without activating the application role.

Uses for Application Roles

If you want your users to work with SQL Server data only when they run a specific application, application roles can be useful tools. For example, suppose you have an inventory-control database and you've built a Visual Basic (VB) application that lets users work with inventory. You might not want your users to connect directly to the inventory-control database or to modify sales data. By granting permissions to an application role, rather than to users or database roles, you ensure that users can access inventory data only when running the application.

Another use for an application role might be to prevent an application from executing ad hoc queries. For example, you might use Microsoft Excel as a front end for a decision-support application that queries SQL Server, creating pivot tables and charts for further analysis. However, you don't want Excel users to use Excel's querying tools to perform unauthorized ad hoc queries. You can force users to run the Excel Visual Basic for Applications (VBA) macros that you've programmed to activate the appropriate application role, rather than granting users permissions that they might use in unapproved ways.

The steps you take to use an application role with VBA macros are essentially the same as the steps you take to use ADO code in VB or any VBA host. The GetData() function opens an ADO Connection object, executes sp_setapprole, creates an ADO Recordset, and copies the ADO Recordset's values into a worksheet. Executing the Close method on the Connection object deactivates the application role.

Application roles can also simplify security administration. Suppose you have many users who access SQL Server through only one application and you don't want to have to manage individual logins and enable database access for each user. An application role requires only one login, which many users can share.

Like a standard database role, an application role exists only in relation to the particular database for which you created the role. After you activate the application role, you can directly access another database only if you've created a guest account in the second database. The application role will have only the permissions granted to the guest account in the second database.

Creating an Application Role

To add an application role to a database, you must, by definition, be a member of the sysadmin fixed server role or of either the db_owner or the db_securityadmin fixed database role. To use Enterprise Manager to create an application role in a database, right-click the Roles node under the database you want and choose New Role from the menu. Select the Application role option, and type in a role name and password for the application role. The sp_setapprole stored procedure requires the role name and password as arguments when the stored procedure activates the application role. You can also create an application role by using the sp_addapprole system stored procedure, as the following script—which creates an application role named ProductApprole with a password of password on the Products database—shows:

USE ProductsEXEC sp_addapprole  @rolename = 'ProductApprole',  @password = 'password'

Next, you assign permissions to the application role. Note that the application role inherits permissions from the public role, so if you want to close security loopholes, revoke all permissions for the public role on objects that you want to secure. When you deny all public permissions on the database tables, as Figure 1 shows, no role, not even the application role, can directly access the database tables. Denying all permissions on the database tables effectively prevents all ad hoc table querying. When you grant permissions to execute selected stored procedures to the application role after you revoke the public role's permissions, as Figure 2 shows, the application role can access the underlying table's data through the stored procedures if the tables and related stored procedures have a common owner.

If you deny all permissions to the database's tables, you must create views, stored procedures, or—for SQL Server 2000—user-defined functions (UDFs) to allow access to your data. Permissions that you grant to views, stored procedures, and UDFs take precedence over permissions that you deny to the underlying tables, as long as the views, stored procedures, and UDFs have the same owner.

Authentication

After you create the application role and adjust database object permissions, you need to consider how SQL Server is going to authenticate users before the application role is activated. SQL Server Books Online (BOL) states that application roles let the application, rather than SQL Server, assume the responsibility of authenticating users—but that statement isn't strictly true. A user or client application must still pass SQL Server authentication to activate an application role (i.e., you can't get to a database's application role unless SQL Server lets you in).

Regardless of whether you use a SQL Server login or Windows logon, SQL Server preserves a user's identity even after activating the application role, so you can track individual user activity. Application roles provide the security context within which the database object permissions are checked, but the actual user's identity isn't lost.

The Guest Account

If you create a guest account in a database that has an application role, you don't have to grant database access to individual user or group logins. You still need a SQL Server login, but you don't need to enable database access for that login. The guest account enables anonymous access without sacrificing identity or security.

You can easily test application roles by using Query Analyzer. A Query Analyzer connection for login Rocky, which Figure 3 shows, displays the results of running the suser_sname() and user_name() functions to return the login name and database username both before and after an application role is activated in a database, which Rocky has accessed by using the guest account. Using the guest account for an application role doesn't sacrifice the logged-on user's identity; SQL Server hasn't enabled Rocky as a database user. Note that the database username is guest before the sp_setapprole stored procedure runs and ProductApprole (the application role's name) after the stored procedure runs. However, SQL Server still returns the login name (Rocky).

If Rocky had been enabled as one of this database's users, user_name() would have returned Rocky, rather than guest, before the sp_setapprole stored procedure activated the application role. Even after sp_setapprole activates the role, you can use suser_sname() to obtain Rocky's login identity.

Application Roles and Systems Administrators

Any member of the sysadmin fixed server role, including the systems administrator (sa) account, has unlimited and irrevocable permissions to perform any action in a SQL Server instance. SQL Server automatically maps the sysadmin fixed server role to the database owner (dbo) database account. Whenever you see dbo as a database object's owner, an sa created the object. The dbo user (or sa) is immune to all DENY restrictions on database objects. However, if you use a sysadmin connection to activate an application role, SQL Server sets the sa's permissions aside for the duration of the connection and uses only the application role's permissions.

Before activating the application role, the sa has full table access, even after denying to the public role all permissions on user tables. After the sp_setapprole stored procedure executes, as Figure 4 shows, SQL Server limits the administrator to the application role's permissions, which include the public role's DENY. However, when the application role is activated, SQL Server doesn't consider sysadmin or dbo, permissions. The only way for an sa to regain sa privileges is to close the connection, then reconnect to the Products database.

Visual Basic Techniques

If you want to use an application role with a VB application, you'll probably use an ADO connection to invoke the application role. (You could also use Data Access Objects—DAOs—Remote Data Objects—RDOs—or any other library capable of executing a stored procedure.) When the VB application opens, you instantiate one public or module-level Connection object to share among your procedures; then you run the sp_setapprole system stored procedure immediately after connecting to the database. Or you can open multiple connections as needed and run sp_setapprole for each connection. You can also use a connection that you define in the VB Data Environment designer to activate an application role. (For more information about using application roles in the Data Environment, see the sidebar "VB Data Environments and Application Roles.")

One important caveat: Application roles aren't compatible with OLE DB connection resource pooling. (Microsoft documents this problem in the article "PRB: SQL Application Role Errors with OLE DB Resource Pooling." ) OLE DB automatically pools connections. When you close an OLE DB connection, the connection isn't immediately closed. Instead, OLE DB returns the connection to the pool and reuses it if OLE DB receives another connection request with the same characteristics. The connection is closed only if OLE DB doesn't receive such a request within the timeout period, which defaults to 60 seconds. The problem is that connections that have had application roles set on them cause runtime errors when OLE DB retrieves them from the pool, even if you explicitly closed the connection.

To use application roles with OLE DB, either through the MSDASQL ODBC provider or the SQLOLEDB native provider, you must turn off connection pooling. You can turn off connection pooling in your connection string by adding the phrase OLE DB Services = -2. In some scenarios, such as Web sites that use Active Server Pages (ASP) ADO code, application roles might not be a viable option because of the overhead required to open a fresh connection each time you need one. For this reason, application roles benefit primarily client/server applications, rather than n-tier architectures. Middle-tier objects usually work better by using the role-based security available in COM+ instead of application roles.

You could, of course, create your own pooling scheme by holding multiple connections open and allocating them as needed, but setting up a pooling scheme isn't a trivial task. In typical client/server applications, each client could reduce overhead by holding open one global Connection object for the duration of the application, provided that your licensing can support the requisite number of open connections.

The code in Listing 1 uses a Connection string (strConn) that specifies integrated security (Integrated Security=SSPI) and turns off resource pooling (OLE DB Services = -2). After the connection opens, the sp_setapprole stored procedure executes immediately, activating the application role, which remains in effect as long as the connection stays open.

The code in Listing 2 runs in a command button's Click event procedure and opens an ADO Recordset by using the Connection object that the code in Listing 1 created. The code in Listing 2 then assigns the ADO Recordset to an MSHFlexgrid control's DataSource property. After you activate the application role, you can open and close any number of ADO objects over the same connection; you need to activate the application role only once. The activation remains in effect until you explicitly close the Connection object, as the Unload event of the form that the code is running in shows in the following example:

Private Sub Form_Unload(Cancel As Integer)  mcnn.Close  Set mcnn = NothingEnd Sub 

Using Application Roles in Access Projects

Using an application role in a Microsoft Access project (.adp) is straightforward because an Access project requires only one OLE DB connection to SQL Server. Access's CurrentProject object has an OpenConnection method you can use to create the connection programmatically. (Make sure you turn off connection pooling in your connection string.) After you have connected the project to SQL Server, you use CurrentProject.Connection's Execute method to activate the application role. This example conditionally executes the sp_setapprole stored procedure based on CurrentProject's IsConnected property*if you aren't successful in connecting to SQL Server, you'll trigger a runtime error only if you try to activate the application role, as Listing 3 shows.

To disconnect from SQL Server and deactivate the application role, use the CurrentProject object's CloseConnection method:

If CurrentProject.IsConnected Then  CurrentProject.CloseConnectionEnd If

To create the connection in code, you must create a project with an empty connection by canceling the Data Link Properties box when creating the new Access project. Add your own startup login form that opens the connection to SQL Server and activates the application role.

When you use application roles with Access projects, you must consider how Access updates data in forms based on views or stored procedures. The Products database application role is based on a security schema that denies all direct access to tables and uses views and stored procedures instead. The problem you run into is that a form based on an updateable view will be read-only if you try to use the form to update data because Access constructs an UPDATE statement that references the base tables defined in the view, not the view itself. Because the application role doesn't have permissions on the tables, the update fails, which you can see in action if you open a trace in SQL Server Profiler. If you update data from a form based on an updateable view of the Suppliers table, the following code is the first part of the statement that SQL Server processes:

EXEC sp_executesql N'UPDATE "Products"."dbo"."Suppliers" SET"ContactName"=@P1 WHERE "SupplierID"=@P2 AND ...

To work around this read-only form problem, you have two options: Grant the application role the necessary permissions on tables, or define your view by using the VIEW_METADATA option. Using VIEW_METADATA in your view definition makes SQL Server return metadata information about the view to the client application. Access can then use this additional information to construct an UPDATE statement against the view, not the underlying table. The following code is an example of the syntax you use to create a view with the VIEW_METADATA option:

CREATE VIEW vwShippersAS  SELECT * FROM Shippers  VIEW_METADATA

Using Application Roles with Access Databases

You can't use application roles with a linked-table Access database (.mdb). The tables are linked through ODBC, not OLE DB, and you have no control over the opening of connections to service these links. Therefore, you have no way to execute sp_setapprole on these hidden connections. Access forms and reports can construct and execute many queries, potentially consuming multiple connections. But you don't have a way of telling ODBC to automatically activate the application role on all these connections.

The only way to use application roles with Access databases is to create an unbound application, using ADO Recordsets or pass-through queries to load all the data and modify it by posting changes through the same mechanisms. The Jet database engine neither parses nor executes pass-through queries in the same way that it parses and executes a regular Access query. The Jet engine passes the SQL statements that comprise the query through to the designated data source so that you can execute commands and run queries directly against SQL Server.

To work effectively with pass-through queries, you must understand several properties that determine their behavior. Aside from setting the pass-through query's SQL property, which contains the text that a pass-through query sends to SQL Server, you must set the Connection string in the query's ODBC Connect Str property. The following Connection string example uses integrated security to connect to the Products database on the local server:

ODBC;Driver={SQLServer};Server=(local);Database=Products;Trusted_Connection=Yes

Another option for pass-through queries is to create a Data Source Name (DSN) to use in the Connection string, but this option requires that you configure the DSN on each user's machine. Simply specifying the connection information above might be an easier option to maintain.

Access is unpredictable about the precise time when it opens a new ODBC connection. A pass-through query that provides data to an Access object might execute once or more than once, depending on the object and the user's behavior. Or, a pass-through query might open a new connection or use a connection that's already open.

If you run the sp_setapprole stored procedure and the application role is active, you'll get a runtime error when the pass-through query executes. To avoid this error, you can code into the pass-through query some logic that tests whether the T-SQL user_name() function returns the application role's name. This logic provides a reliable way to ensure that you won't execute sp_setapprole on a connection that already has an application role in effect. The following example conditionally executes the sp_setapprole stored procedure, then executes the procProductList stored procedure:

IF (SELECT USER_NAME()) <> 'ProductApprole'  EXEC sp_setapprole 'ProductApprole', 'password'EXEC procProductList

Figure 5 shows the completed pass-through query definition and the query's property settings, including the specification that the query returns some data. Note that Access pass-through queries can't handle multiple resultsets. If the stored procedure returns more than one resultset, Access processes only the first one and ignores any remaining resultsets.

You don't have to use the Access UI when you create and modify pass-through queries. You can manipulate pass-through queries in VBA code, using either the DAO or ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) object libraries. DAO is the older object library, but Microsoft created and optimized DAO specifically to work with the Jet engine. When you're working with Jet objects, DAO usually gives you the best feature selection and performance.

To use DAO in Access 2000, you must first set a reference to the Microsoft DAO 3.6 object library, if such a reference doesn't exist. If you're working with an earlier version of Access, use the DAO version appropriate to that release. To modify a pass-through query, create a DAO QueryDef object and modify the object's properties. Listing 4 shows the code you might use to modify a pass-through query's SQL property, then execute the query.

Instead of modifying an existing pass-through query or creating a new saved query, you can create a temporary pass-through query by giving the query a zero-length name, as the following command shows:

Set qdf = db.CreateQueryDef("")

Or, you could use ADOX to modify a pass-through query. The logic is the same as DAO, but the syntax differs. You can find examples of how to use DAO and ADOX to modify queries in the approlemdb.mdb example file.

To hide the design of your pass-through queries from your users, you could use Access security to keep Access from exposing the application role's name and password. Or, you could limit your use of pass-through queries to those in VBA code, which you can protect by converting your Access .mdb files to Access .mde files.

Powerful and Convenient

Application roles can provide a powerful and convenient way to administer security in client/server scenarios. Application roles eliminate the need to enable multiple database users, and the permissions granted to an application role are available to your users only when they run your application.

The sp_setapprole stored procedure activates an application role, and you can execute this stored procedure from ADO, DAO, or RDO code or from an Access pass-through query. You can also use application roles with bound VB applications that use the Data Environment designer. The most important limitation of application roles is that you can't currently use them with pooled OLE DB connections, making them unsuitable for use in middle-tier objects. With that limitation in mind, you should consider the convenience of using application roles when users are running client/server programs that directly access SQL Server.