Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 14.9 Use Views to Update Data in an ADP When Users Don't Have Permissions on Tables

14.9.1 Problem

You have secured your SQL Server 2000 database and removed all permissions for users to directly interact with tables. You have created views and granted users permissions to update data through the views instead. However, users normally are not allowed to update data through views when they don't have access to the underlying tables. How can you allow them to update tables through views?

14.9.2 Solution

Whenever you secure your database in SQL Server, you have the option of denying permissions on tables and granting permissions for users to work with the data only through secondary objects such as views, stored procedures, or user-defined inline functions. As long as both the underlying table and the secondary object have the same owner, SQL Server does not check permissions on the underlying table, and simply executes the action based on user permissions granted on the secondary object. For example, you can deny permissions for users to select data from a table, and then create a view that selects data from the table. Then grant users permissions to use the view, as shown in Figure 14-19.

Figure 14-19. Using SQL Server Enterprise Manager to grant permissions for views

You can then update data through the view instead of the table, as long as you have permissions granted on the view. This allows you to control which rows and columns in the table your users can access.

However, in an ADP, Access doesn't use the view to update the data even if the view is the record source of a form. A peek at a Profiler session in SQL Server shows that when you update the form, Access creates an update statement directly against the base tables.

You can solve this problem by adding an option to the view that will force Access to run its updates against the view rather than against the base tables. If you use the WITH VIEW_METADATA option when you create (or alter) your view, SQL Server will send Access metadata (column names and data types) from the view rather than from the underlying tables, and Access will use the view to update data. If you use Access to create the view, you can set this option by setting the "Update using view rules" property, as shown in Figure 14-20.

Figure 14-20. The "Update using view rules" property

If you are using Access 2000, you need to type in the WITH VIEW_METADATA option manually, since it doesn't show up in the Properties dialog. This option wasn't supported in SQL Server 7.0.

Use the following steps to allow your users to update data through views when they don't have permissions on the underlying tables:

Revoke or deny all permissions to the public role for the table (or tables) on which the view will be based.
Create a view that selects data from the table by using the VIEW_METADATA option or selecting the "Update using view rules" checkbox. This example selects data from the Shippers table:
```
CREATE VIEW vwShipperList
WITH VIEW_METADATA
AS
SELECT ShipperID, CompanyName, Phone
FROM Shippers
```
Grant INSERT and UPDATE permissions on the view.
To test the view, use it as the record source of a form. Make sure to fully qualify your references with the ownername.objectname syntax.
Log on as another user who does not have permission on the underlying tables. You should now be able to update data or insert data, but not delete an existing shipper.

14.9.3 Discussion

When Access requests data for a view in browse mode, it also retrieves metadata that it uses to construct update, insert, and delete statements. The VIEW_METADATA option specifies that SQL Server returns enough metadata information about the view for Access to implement updateable client-side cursors that work with the view instead of the base tables.

This technique is not available in SQL Server 7.0 or earlier because the VIEW_METADATA option did not exist prior to the release of SQL Server 2000. This new feature makes it possible to take advantage of bound Access forms without having to sacrifice security. Few SQL Server database administrators are willing to give users unrestricted permissions to update tables. Views offer more control, but the most control comes from using stored procedures, and, unfortunately, there is nothing like the VIEW_METADATA option for stored procedures.

[ Team LiB ]