MORE INFORMATION
In SQL Server, you can create database roles for easier administration of permissions in a database. Instead of granting individual permissions to each user separately, you can group users with the same permission needs by making them members of the same regular database role, and then assigning permissions to the database role itself. Unless a specific permission is explicitly denied elsewhere, member users will acquire the permissions granted to that database role.
Although regular database roles are useful for situations where you want users to be able to perform their own queries or updates in a database, they are not always appropriate. Sometimes, you may want users to have only certain permissions when they use a specific application, and you do not want them to be able to view or modify data outside the application.
One method that is often used for working around this is to only give the necessary permissions to one SQL Server user account. The actual users might have permissions to connect to a database but not to view or modify any data. After a user connects to the database by using the user's individual account, the ADP could then programmatically reconnect by using the credentials of the user account that does have permissions. Although this can be effective, it does not allow you to distinguish between users in the database or to determine which user performed a particular action.
Application roles are designed to work around this limitation. Application roles, unlike regular database roles, do not have members themselves. Instead, users log on to a SQL Server and connect to a database by using their own credentials. At that point, the security context of an application role can be applied programmatically to an existing connection by using the
sp_setapprole stored procedure. In SQL Server, individual users are still differentiated, but the permissions that are available within a particular connection are limited to the permissions of the application role. The user's individual permissions, whether lesser or greater, are no longer considered.
Creating an Application role
Access 2000 has a wizard for creating application roles from the Access user interface. To use this wizard, you must have MSDE 2000 or the SQL Server 7.0 Client Tools installed on the local computer. However, if you do not have MSDE 2000 or the Client Tools installed, you can still create an application role and grant it the necessary permissions programmatically by using Transact-SQL (T-SQL) from an ADP. Although a full discussion of SQL Server Security is outside the scope of this article, additional information can be found in
SQL Server Books Online or in the following article on the Microsoft MSDN Web site:
The following steps show you how to create an application role and how to grant the new role Select permissions on a table:
- Start Access, and then open the sample Access project, NorthwindCS.adp.
- Create a copy of the Customers table, and then save it as tNewTable.
- Right-click the Customers table in the Database window, and then click Save As on the shortcut menu.
- In the Save As dialog box, type tNewTable in the Save Table 'Customers' To box, and then click OK to finish creating the new table.
- From the Database window, open the tNewTable table in Design view, and then designate the CustomerID field as the primary key.
- Save and close the table.
- On the Tools menu, point to Security, and then click Database Security.
- Click the Database Roles tab.
- Click Add to create a new role.
- In the Name text box, type AppRoleName.
- Under Database role type, click Application Role.
- In the Password box, type Password.
- Click OK.
- Select AppRoleName from the list of database roles.
- Click Edit, and then click Permissions.
- In the list of database objects, find the entry for the tNewTable table, and then click to select the Select check box.
- Click OK three times to finish the Security Wizard.
- Open the tNewTable table and note that you can insert new records and edit existing ones. Close the tNewTable table.
Implementing the Application role
The main complication when you are using application roles in Access projects is that Access uses three main connections to SQL Server to handle various tasks. Ideally, to apply an application role to the whole project, you would have to execute
sp_setapprole in the context of all three main connections and any other connections that are used by Access. The objects handled by each main connection are as follows:
- Used for determining which objects appear in the Database window and for miscellaneous database administrative tasks.
Used for opening tables, views, stored procedures, functions, and the record sources for forms and subreports (but not for the main report itself).
Used for obtaining the record sources for combo boxes, list boxes, and reports.
- Used for opening tables, views, stored procedures, functions, and the record sources for forms and subreports (but not for the main report itself).
Used for obtaining the record sources for combo boxes, list boxes, and reports.
- Used for obtaining the record sources for combo boxes, list boxes, and reports.
Although connections #2 and #3 can be accessed fairly easily, there is no method available for executing the stored procedure in the context of connection #1. Fortunately, this connection is the least important of the three. You can easily work around it by constructing your own user interface for handling database objects instead of relying on the built-in Database window. For example, you could construct a switchboard-type form.
The following steps use the NorthwindCS sample project to demonstrate how to apply an application role against connections #2 and #3:
- In the Database window, click Forms under Objects, click New, and then click OK to open a new form in Design view.
- Add a list box to the newly created form, and then set the Name property of the list box to lst_AppRole.
- Add a command button to the form.
- Set the OnClick property of the new command button to the following event procedure:
On Error GoTo EH
'This avoids a message that no records were returned.
DoCmd.SetWarnings False
Dim TSQL
TSQL = "EXEC sp_setapprole 'AppRoleName', {Encrypt N 'Password'}, 'odbc'"
'This sets the app role on Connection #2.
Application.CurrentProject.Connection.Execute TSQL
'This sets the app role on Connection #3.
lst_approle.RowSource = TSQL
lst_approle.Requery
DoCmd.SetWarnings True
MsgBox "The application Role is now in effect.", vbInformation
Exit Sub
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical
- Close the Visual Basic Environment to return to the form.
- Save the form, and then switch the form to Form view.
- Click the command button to run the underlying code. Note that you receive a message box that indicates success.
- In the Database window, click Tables under Objects, and then open the tNewTable table.
- Modify a record, and then try to save the changes.
Note that as you try to commit your changes, you receive an error message that states that you do not have enough permissions. This occurs because you gave the new application role Select permission on the table, but not Update permission.
By design, Access shows only objects in the Database window for which the user has at least Select or Execute permissions. Access uses connection #1 to determine which objects a user has permissions for. After applying the application role to connections #2 and #3, the Database window still shows the same objects that it did before. It does so, even though the user may no longer have permissions to all the objects. Or, the user may have permissions to more objects that are not shown. This can result in unexpected behavior when you use the Database window.
For example, when you opened the tNewTable table, it "appears" that the user does have permissions to edit and insert records. The insert new record icon at the bottom of the table is enabled, and the user is able to put a record in edit mode. You do not see any visual clue to indicate otherwise until you try to commit the edit or insert. This results in an error message. Access believes you have permissions when you actually do not.
The most effective workaround is to provide a custom interface for the user and not to rely on the Database window. By using a switchboard-type user interface, you can control exactly which objects the user has access to.
Other limitations and security considerations
Subforms not working
Unlike with other database objects, Access does not always use the same connection to retrieve the data source of a subform. Access frequently (but not always) creates a new connection to SQL Server just to handle the subform recordset, or to retrieve the linking field data that connects the subform to the main form. Because this new connection does not have the application role applied, the user may receive a permissions error if the user does not have explicit permissions to the database object. Unfortunately, this means that there is no reliable way to use bound subforms when application roles are applied. The only effective workaround is to have completely unbound subforms, with the data manipulation handled programmatically. This is the most serious limitation when you use application roles in Access.
Reports not working
When you have an object such as a table or view name listed as the record source for a report or subreport, Access checks to see whether the object is listed in the Database window before it retrieves any data from SQL Server. Because the database window uses a connection that does not have the application role applied, the user receives an error if the user does not have explicit permissions to the underlying data source.
To work around this problem, always use Transact-SQL statements as the record source for forms and reports. For example, use "Select * from ViewName" instead of just "ViewName." This way, Access passes the T-SQL statements directly to SQL Server and retrieves the data based on the permissions of the application role. However, stored procedures will not work as the record source for reports when you use application roles. To use stored procedures with reports, you must upgrade to Access 2002.
The Public Database role
An application role acquires the permissions of the Public database role. By default in NorthwindCS, the Public role has full permissions to most objects. Therefore, an application role is generally ineffective. When you created the tNewTable table in the "Creating an Application Role" section, the Public role was not granted permission to the table. Later you saw the effects of the application role security context on that table. However, other tables may not show any difference under the application role because the Public role has permissions to those objects.
VBA security
Because the password for the Application role is embedded in the application from which it is called, a knowledgeable user would be able to read the application role name and password from the source code, and then use that information to gain access to SQL Server from another application. Therefore, it is a good idea to compile the ADP into an ADE file so that the source code is not viewable. At minimum, enforce a password on the VBA project.