MS Access User Level Security for Form to be Read but Table to be Hidden

Question

Can user level security enable someone to access a form, but not access a table? I have a form that is connected to a table using a"SELECT" command. It does not "UPDATE" or "DELETE". I'd like for the user to access my form, but I don't want them to be able to view the details in the underlying table. I've tried all sorts of combinations in the user level security wizard, but for some reason, my form comes up blank, unless I give complete access to the user to do anything. Any ideas? By the way, I'm on Access 2003.

Answer 1

You could make an MDE and give it to your users. If you do this, the users won't be able to see anything but the user interface you have provided.

If your tables are in the same Access database as the forms and queries, you first need to "split the database" into a frontend and backend. See:

http://databases.about.com/od/tutorials/ss/splitaccessdb.htm

for information on how to do this. The frontend contains everything but the tables. The backend contains the tables. The frontend is linked to the backend tables using "linked table" objects in the frontend.

Once the database is split, open the frontend in Access, and create an MDE by selecting Tools/Database Utilities/Make MDE File from the menu bar. The MDE is what you provide to the users. They open it the same way they would any other database, except that they can't design any of the objects.

Make sure that you have an AUTOEXEC macro defined in the frontend, so that when the MDE is opened by the user, it has an entry point. The user cannot see any of the objects in the MDE, so if you don't have an AUTOEXEC macro to open the first form, nothing will happen when they try to open the MDE.

If you need the ability to manage the linked tables in the MDE by allowing the user to connect to the backend database with a File/Open dialog, see the following article:

http://www.mvps.org/access/tables/tbl0009.htm

OF course, none of this precludes your users from opening the backend database. If you need stronger security, you can upsize the backend database to SQL Server Express. More info here:

http://office.microsoft.com/en-us/access/HA102755371033.aspx

Answer 2

What you're looking for is a RWOP query. That means "run with owner permission."

To make it work, assuming you've already run the security wizard on your front-end and back-end databases and have removed permissions for the users to even read the tables, you then create a saved query as the recordsource of your form. In the properties of the query, it is set by default to RUN PERMISSIONS as "user's." Change that to "owner's" and save the query. The query will run with whatever permissions on the base table the owner of the query has. If you are running as an administrator with full permissions, you'll want to set the permissions of the saved query to read-only (or create the query under a user logon that has read-only access to the tables -- it's much easier to do the former).

Some caveats:

1. user-level security is tricky. If you miss even one step outlined in the ULS security white paper, your app won't be secured. The way to test this is to open Access with a default workgroup file and then see if you can access the objects in your database logged on as the default admin user. If you can, then you made a mistake somewhere along the line.

2. user-level security is crackable by anyone who wants to. This doesn't mean it's not useful, it just means that it's not bullet-proof. I don't worry about that so much, as I consider security as much a people problem as a technical problem -- you have to give employees sufficient privileges to do their work, and you have trust them that they won't abuse the privileges you've given them.

3. if you're running Access 2007 and using the ACCDB format, RWOP queries are not available to you, as Jet user-level security is not supported for the ACCDB format. If you're using MDB format, though, it will work just fine in A2007.