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," page 50.)
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" at http://support.microsoft.com/support/kb/articles/q229/5/64.asp.) 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.
Prev. page
1
[2]
3
4
5
next page 
