Linked Table Security Issue

2 Oct 2006 11:26 PM

Jeff

We are using access as an ad-hoc query/report creation tool for HR
data. There is an ODBC installed on each user's system so they can
access the queries/reports which depend on linked tables.

The auditors don't want users to have complete backend access to the HR
data (via linked tables). The users should only be able view data for
their department only.

We could use Access database security and allow users to view only
specific queries/reports. But since they have the ODBC on their
computer, what can prevent them from opening up an entirely new Access
database and linking tables and viewing data they shouldn't? Can't
they just change workgroups?

The backend database is SQL and the authenticity is the NT
authentication using the network login ID. Is there anything we can do
on the SQL side that can prevent users from linking tables?

Thanks for any help or direction.

3 Oct 2006 12:37 AM

Rick Brandt

Jeff wrote:
Show quoteHide quote

> We are using access as an ad-hoc query/report creation tool for HR
> data. There is an ODBC installed on each user's system so they can
> access the queries/reports which depend on linked tables.
>
> The auditors don't want users to have complete backend access to the
> HR data (via linked tables). The users should only be able view data
> for their department only.
>
> We could use Access database security and allow users to view only
> specific queries/reports. But since they have the ODBC on their
> computer, what can prevent them from opening up an entirely new Access
> database and linking tables and viewing data they shouldn't? Can't
> they just change workgroups?
>
> The backend database is SQL and the authenticity is the NT
> authentication using the network login ID. Is there anything we can
> do on the SQL side that can prevent users from linking tables?
>
> Thanks for any help or direction.

Create a view for each department's data and grant permissions *on the server*
to each group of people only to the views you want them to see.

An alternative would be to create a Stored Procedure that uses the UserAccount
as a parameter and only returns data for the department that the user belongs
to. Then you would only need a single stored procedure, but might need
additional tables to match users to departments.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com