Database form driven only - Secure tables and queries

[kayek]

Our Access Database is totally form driven. The users should never have to open a table or run a query manually. All the users need to (or suppose to) do are in the forms.

Is there some way that I can lock the tables so my users do not have access to open and make changes directly in a table? They should only have access to update tables from a form.

Also, is there any way can lock the users from running or creating queries manually? I want to be sure no one tries to run the query DELETE * FROM table1.

The users are using a .mde file, I hide the Database window and the Database opens to a main form/switchboard. Even though I hide the database window the users know they can unhide the window and get at the tables. We are using SQL Server linked tables.

Any suggestions for keeping my data safe?

Kaye

 

[jebry]

Hi!

What you need to do depends on how they are making the Db window display. You can create your own toolbars to display istead of the standard toolbars, which will get rid of that path. You can go to Tools - Startup and look at your options there. For instance you can stop their use of the F11 key by unchecking Use Access Special Keys.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[psemianonymous]

Your best bet is backups and accountability. Integrate with SQL Server security in some fashion (i.e. don't use one "database-level" user/password combo; give everyone their own or integrate with their Windows account automatically), and then you can log everything they do via SQL Server's built-in logging.

The same goes for backups; use SQL Server's built-in backups (which I assume you do regularly, etc etc) to guarantee you'll have reasonably fresh data.

If you need to limit their access to tables to view only a subset of all records, then make a VIEW or a stored procedure or somesuch so that each user is limited to the subset that way, and link to these views via passthrough queries.


If this isn't the case, then you just need to yell at your users. If they have to have their hands held on the level that you're describing, i.e. can't be trusted at all, then you might as well build a full-on multi-tier application or very heavy usage of stored procedures, i.e. ditch Access completely and totally lock everything down.

Access isn't suited for everything, and locked-down security is one of those things it's not suited for.


Have a nice day. --Peter