Access 2003 Security

rashi · April 27th, 2009, 02:52 PM

Hi,

I have constructed a database in Access 2003 and it is placed on a network. I have split the database in FE and BE. Right now all users have read access to the database (no need for login). I use a bat file to copy the FE to the user's local drive linking to the BE on the network.

I now need to put security for the database such that -
1. I retain full admin persmissions for all aspects of the database.
2. just a few users have permission to insert/update/delete data (using a login screen)
3. all other users who have permission to access the network location have read access to the database (no need for login). I need to do this so that I don't have to manage permissions for all the read only users.

Can anyone guide me on how to do this?

Thanks in advance.

mmcdonal · May 7th, 2009, 10:55 AM

There are a couple ways to do this.

One way is to have different copies of the FE. Have one administrative FE, one for users who can insert, update, etc, and one for read only. Make the user copies mde's.

I also put the BE on SQL server. This is not a big chore if you have access to one. That way you don't have to worry about users just working in the BE tables and by-passing the FE interface. You will want to use ADO for this.

One thing I do with read only FE's, depending on the amount of data, is pass data from the BE to local tables when a report is opened, for example, and then dump the data on the On Close event of the report (run a delete query). This prevents the read only users from interacting with the data in the BE at all. (FE is not connected to BE).

You may want to have only one version of the front end, but based on the user, hide buttons etc. That can be problematic even with an mde since the user can just open a form from the object explorer. Even if you have them all hidden, if the user has Access configured to show hidden objects, they can see them.

Do you have any ideas on how you want to proceed?