Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Revoke or deny all permissions to the public role for the table (or
   tables) on which the view will be based.

2. 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

3. Grant INSERT and UPDATE permissions on the view.

4. 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.

5. 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.