Share via

Defining Table Security

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Workflow application security is defined primarily using Microsoft® Windows® and Microsoft® SQL Server™. To add table and column permissions to your project, you can use the security features available in the SQL Server Enterprise Manager.

The Workflow Designer for SQL Server respects permissions on existing tables. However, when a main table has a primary key, select permissions are granted to Public on all the columns that make up the primary key. This is required for data access pages and any other clients using Microsoft® Data Access Components (MDAC) to be able to send updates into the base table.

After a table has been added to a workflow project, any subsequent permissions changes set by the user are preserved.

Table Permissions

Table permissions must be set through SQL Server Enterprise Manager or by using OSQL, a command line utility that is available with Microsoft® SQL Server™ 2000 Desktop Engine.

When a table is added to the table hierarchy, the permissions you want enforced on the selected table should be set on the view that is created on top of that table. The view will be named <*tablename>*View.

Existing table permissions are not replicated for the newly created view. If the table already has permissions, you must set them up again on the view.

For more information about setting table permissions, see the documentation for the application you are using to add the permissions.

Row-Level Permissions

Row-level permissions can be set only for main tables. When you enable row-level permissions for your application, a user can specify which roles have select, insert, update, and delete permissions on a per-row basis.

Detail tables automatically inherit permissions from the main table. Rows in detail tables automatically inherit permissions from the related row in the parent table. For example, in an order entry project, if only users in the "OrderClerk" role can enter and view a customer order, the same would hold true for the related OrderDetails table.

When row-level permissions are enabled for a user table, they are enforced in the view that is created for that table. Therefore, users should access the table only through this view.

To enforce access through the view, Workflow Designer for SQL Server modifies the permissions on the base table. The changes prevent users from retrieving data directly from the table. At the same time the changes enable data access pages to send updates, inserts, and deletes to the table.

The Workflow Designer for SQL Server only modifies the base table permissions if the table has a primary key when row-level permissions are enabled. The primary key is required by data access pages to update the table.

Modifications Made to Base Table Permissions

When you enable row-level permissions for a main table, the following changes are made to the base table and to its associated detail tables.

  • Revoke select permissions on the table.
  • Grant select permissions to Public on all the columns of the primary key. This is required to enable updating and deleting rows from data access pages.
  • Grant insert permissions to Public on the table. This is required to enable inserting rows from data access pages.
  • Grant update permissions to Public on the table. This is required to enable updating rows from data access pages. Row-level security is enforced by the Update trigger.
  • Grant delete permissions to Public on the table. This is required to enable deleting rows from data access pages. Row-level security is enforced by the Delete trigger.

Row-Level Permissions Issues

When implementing row-level permissions, consider the following issues.

  • When the "Enable row permissions" option is selected for a main table, it applies to all its detail tables as well. Therefore, row permissions are enforced in all views that the Workflow Designer creates for the detail tables. In addition, the changes listed earlier are applied to the detail table permissions.
  • Any changes made to base table permissions after the "Enable row permissions" option is selected are preserved.
  • When the "Enable row permissions" option is cleared, all row-level permissions associated with the table are deleted. No changes are made to table permissions. Therefore, when you disable row permissions, all roles that had access to some rows in the table can now access all the rows in the table.
  • Workflow Designer always grants select permissions to Public on the base view created for each table.

See Also

Setting up a Table Hierarchy | Defining Hierarchy Schema | Enabling Row-Level Permissions | Security Permissions Model