SQL Insider

Make Your Security Access-Friendly

Try this trick to give user the permission they need to update SQL Server records via Microsoft Access.

Savvy DBAs know that keeping users out of your tables is one of the key steps towards keeping your data secure. Instead, you should assign data permissions to users via the views in your database. 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. This gives you the flexibility to customize data access permissions or even change them on the fly by simply altering views, while avoiding giving users blanket permissions on tables. With views it's easy to enforce row- or column-level security in special situations.

But if you're using Access 2000 or Access 2002 data projects (ADPs) as a front-end to your SQL Server database, you'll find that there's a catch to this scheme. It turns out that Access won't make proper use of view-based permissions. Using 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!

Don't despair. Here's a little-known trick to make this work. What's happening is that Access uses ADO to get to SQL Server, and ADO (and the underlying OLE DB layer) tries 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, make a property setting that tells OLE DB to use the view itself to do the updates.

The trick: Add the WITH VIEW_METADATA clause to your view definition. You might define a view in the pubs database this way:

CREATE VIEW dbo.vwAllAuthors
WITH VIEW_METADATA
AS
SELECT *
FROM dbo.authors

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 (provided, of course, that I've got permission on the view). 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.

About the Author

Mike Gunderloy, MCSE, MCSD, MCDBA, is a former MCP columnist and the author of numerous development books.

comments powered by Disqus

Reader Comments:

Fri, Oct 31, 2003 Anonymous Anonymous

Cleary explained

Tue, Jul 8, 2003 Srikanth N Chennai

Very good. Just what i needed

Tue, May 27, 2003 Anonymous Anonymous

Thanks. Keep the tips coming.

Wed, May 21, 2003 Anonymous Anonymous

Good tip to enhance database security!

Fri, Apr 11, 2003 Anonymous Anonymous

Outstanding !!!

Fri, Mar 21, 2003 Anonymous Anonymous

wow! Saved me lots of time.

Mon, Feb 3, 2003 guoxiaomin china

SQL SERVER 2000,MCP,MCDBA,MCSE,MCSA

Tue, Dec 3, 2002 Mike Mtl

good tip, easy reading

Mon, Sep 2, 2002 Paul Finey Anonymous

Just what I needed at the right time as well.

Wed, Jun 12, 2002 Ray DFW

Good Catch -- but who'd want Access to be used as the client in the first place?

Sun, Apr 28, 2002 william China

Very helpful message! You are great!

Fri, Mar 22, 2002 Anonymous Anonymous

Very helpful indeed

Mon, Mar 4, 2002 RobH Canada

Straight to the point and clear information. Covers all the 5Ws, especially the why. Thanks, from a beginning beginner ...

Mon, Feb 11, 2002 peter nelson Anonymous

As always, great stuff from Mike!

Wed, Feb 6, 2002 Brett Torrance

Great tip... This kind of stuff is what makes us miracle workers! Thanks Mike!

Wed, Feb 6, 2002 Hunter B Atlanta, GA

notable article!

Thu, Jan 31, 2002 marks_01 st. louis rams, mo

great tip!

Thu, Jan 24, 2002 Howard Pincham Cleveland, OH

Very helpful article.

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.