Creating a Row-Level Permissions User Interface

See Also

You can create a user interface where solution users can quickly and easily define row-level permissions that determine which roles can see items entered by a user. This is a helpful security feature when sharing information within a team.

To use row-level security in your team solution, you first must enable row-level permissions on your main tables. For details, see Enabling Row-Level Permissions.

Then, on your data access page, you must create the user interface to make it possible for users to interact with this feature.

Row-level security in a team solution is managed through the use of SQL Server stored procedures. The Access Workflow Designer creates three stored procedures used for enforcing row-level security in a team solution:

• modGrantRowPermissions
  
  
• modEnumRowPermissions
  
  
• modDropRowPermissions

For an example of the script you can use to invoke a stored procedures on a data access page, see Calling a Stored Procedure.

The Issue Tracking solution also provides an example of one way to create a user interface to manage row-level permissions. For more details about the design of Issue Tracking application, see Issue Tracking Team Solution.

If you create a team solution based on the Issue Tracking template, use FrontPage to open the Issue Tracking Web site. Look at the ItemPermissions.htm page to see how the Issue Tracking solution created the Web page for designating row-level permissions.

In the Issue Tracking solution, the ItemPermissions.htm page provides a means for the user to enable and set row-level permissions for each database role. The list of roles is generated based on the roles defined in that database. The list of roles on this page was created to provide convenient permissions combinations.

Once the user has selected an option, the program permissions are dropped using the modDropRowPermissions stored procedure and added using the modGrantRowPermissions stored procedure.

Important Issues for Implementing Row-Level Permissions

• By default, there are no row-level permissions defined for user tables, and all rows are available to all users with permissions to access the team solution.
  
  
• When row-level permissions are granted, the database owner continues to have access to all rows regardless of membership in specific roles.
  
  
• Users must have a minimum of Select permissions to see a record. It may be helpful to combine permissions for users, so they do not have to remember to select the appropriate combination of permissions.
  
  
• When assigning row-level permissions, users should specify at least one role with Select, Delete, Grant, and Update permissions. Otherwise, only the database owner is able to interact fully with the row.
  
  
• To see how to check the permissions for a specific row, examine the CheckWriteState function located in the Issue Tracking solution on the gridview.htm page. For details, see Workflow Support in the Issue Tracking Solution
  
  
• The modEnumRowPermissions stored procedure returns the role name and rights for a row. It can be used to validate users or to display different pages depending on the current user’s permissions.