Row-Level Permissions and the Workflow User Interface

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

To use row-level security in your workflow application, first enable row-level permissions on your main table view. Then, on your data access page, you can create the user interface to make it possible for users to interact with this feature.

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

• modGrantRowPermissions
• modEnumRowPermissions
• modDropRowPermissions

The Issue Tracking sample provides an example of one way to create a user interface to manage row-level permissions. To see how it is done in the sample, use Microsoft® FrontPage® to open the Issue Tracking Web site and look at the ItemPermissions.htm page. 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.

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

Consider the following when 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 workflow application.
• When row-level permissions are granted, the database owner continues to have access to all rows regardless of membership in specific roles.
• Users must at least have select permissions to see a record.
• 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 an example of how to check the permissions for a specific row, examine the CheckWriteState function located on the gridview.htm page in the Issue Tracking sample.
• 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.

See Also

Developing a User Interface for SQL Server | User Interface Considerations | Creating User Interfaces | Help Files in Workflow Applications | User Information for Workflow Applications | Using Alternative User Interfaces | Enabling Row-Level Permissions