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?
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.
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.
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.
Follow these 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
specifying the “Update using view rules” option. 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.
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.