Access Front End and SQL Server 2000 Record Level Access Control

[Noloader]

Hello,

We are using Access ADP as a front end, SQL Server 2000 as a back end.
We have a customer contact database. We would like to limit certain
users to only receive certain records based on Windows NT group
membership.

For example, Eastern Sales Group can see clients located in their
region, but they cannot see clients located in the Northern Region.

Is there an elegant way to do this? Below a two solutions which have
been proposed, but none seem to fit. Access is required as a Front End
for its ease of use.

*********
** 1 **
*********
Add an additional attribute (bit mask value) to tblCustomers, and
query appropriately based on the user's group membership.

Problem:
Access allows users direct access to the underlying table.

*********
** 2 **
*********
Create a separe table for each group (effectively splitting
tblCustomers into smaller, separate tables based on group access).
Then, apply SQL Server security on the objects to enforce the business
rules.

Problem:
Does this break Normal Form??? I've never seen a solution like this.

I've googled and found similar questions, but not a good solution.

Any suggestions would be appreciated.

Thanks,
Jeff
Jeffrey Walton
noloader.at.yahoo.com

[Simon Hayes]

"Noloader" <no******@yahoo.com> wrote in message
news:6b**************************@posting.google.c om...

Hello,

We are using Access ADP as a front end, SQL Server 2000 as a back end.
We have a customer contact database. We would like to limit certain
users to only receive certain records based on Windows NT group
membership.

For example, Eastern Sales Group can see clients located in their
region, but they cannot see clients located in the Northern Region.

Is there an elegant way to do this? Below a two solutions which have
been proposed, but none seem to fit. Access is required as a Front End
for its ease of use.

*********
** 1 **
*********
Add an additional attribute (bit mask value) to tblCustomers, and
query appropriately based on the user's group membership.

Problem:
Access allows users direct access to the underlying table.

*********
** 2 **
*********
Create a separe table for each group (effectively splitting
tblCustomers into smaller, separate tables based on group access).
Then, apply SQL Server security on the objects to enforce the business
rules.

Problem:
Does this break Normal Form??? I've never seen a solution like this.

I've googled and found similar questions, but not a good solution.

Any suggestions would be appreciated.

Thanks,
Jeff
Jeffrey Walton
noloader.at.yahoo.com

In general, there is no reason why your users should need to access tables
directly. If you manage data access through stored procedures, then the
procedures can check role membership and only return or update those rows
which are permitted to the user. You could also use views, based on role
membership, or use application roles and handle everything in the client
application.

I would avoid solution 2, because it duplicates data - whatever information
you use to partition the data could also be put in a column. But if you have
a large amount of data, and if users only access their own data, then this
might also be an option.

Simon

[(Pete Cresswell)]

RE/

We have a customer contact database. We would like to limit certain
users to only receive certain records based on Windows NT group
membership.

Based on what little experience I've had doing an Access front end against SQL
Server, I'd set SQL Server security so that nobody and nothing can get directly
to the tables in question and then develop stored procedures and/or views to
move the data back-and-forth between tables and app.
--
PeteCresswell