Control Data Access Through Views in an ADP
By Mike Gunderloy
Long-time Access developers are familiar with the concept of RWOP queries. That stands for “Read With Owner’s Permissions,” and such queries are an excellent way to enforce highly-granular security in an Access database. The idea is that you have one user, say DBAdmin, who owns all the tables and queries in the database, and thus has full access to the data. Regular users only have permissions to the queries, and none to the underlying tables. When you set the Run Permissions property of the queries to “Owner’s,” regular users gain the owner’s permission to the underlying data -- but only when they’re using the query, not when they try to open the table itself. This scheme allows you to enforce row-level and column-level security if you need to do so.
You can use a very similar technique in Access 2000 and Access 2002 projects (the Access user interface hosted directly on the SQL Server database engine). With SQL Server, you can assign data permissions directly to views. For example, you can give a particular user permissions to update a view, and they’ll be able to change the data exposed by that view even without any permissions on the underlying tables. But at first glance, it seems like this is no help for Access projects. Using, for example, an Access 2002 ADP, a SQL Server 2000 database, integrated Windows security, and a user with update permissions on a view but not on the underlying table, you’ll find that the user can update the data just fine through SQL Query Analyzer, but not if they’re using Access!
It turns out that there’s a trick to make this work. What’s happening is that ADO and OLE DB try to optimize updates on views by sending the updates directly back via the underlying tables. This is fine (and fast) in most cases, but it totally defeats the security setup I’ve described. To make things work the right way, you need to make a property setting that tells OLE DB to use the view itself to do the updates.
In Access, open the view in design mode. Bring up the property sheet (View, Properties). On the View tab, check the box for "Update using view rules". Save the view.
Alternatively, you can specify this when you're building the view itself. The SQL will resemble this:
CREATE VIEW dbo.vwAllCustomers
WITH VIEW_METADATA
AS
SELECT *
FROM dbo.Customers
It's the WITH VIEW_METADATA clause that does the trick.
I tested this fix in both Access 2000 and Access 2002 ADPs, and in both cases I can update data via a view even without permission on the underlying tables after making this change. One caution, though: the WITH VIEW_METADATA clause is new to SQL Server 2000; you won’t be able to use this technique with older versions of SQL Server.