Linked Table Security Issue

[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.

 

[Rick Brandt]

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.