MS Access: protecting tables from non-invasive access
Is there a way to protect tables in ms db access from unauthorized access? I would like my users to use forms in the db, but I don't want them to display the contents of the tables. I know I can hide a table, but anyone who knows a little about access can show hidden tables. i cna also changes the name to UsysTableName, but again - including system tables shows them all. Is there a safer way? Perhaps password protection?
source to share
There are also users and groups available, as well as the ability to grant rights. You can access this feature with tools / security / user and group rights.
To do this, you need to create a workgroup information file:
see: http://www.databasedev.co.uk/access_security.html
NOTE. Indeed, accessing Ms is a poor choice of db in most cases. But if you need to keep using it for one reason or another, learning its security model is essential in real-world applications.
source to share
For Access 2003 Understand the role of Workgroup Information Files in Access Security at http://support.microsoft.com/kb/305542/ .
For Access 2007 How to use the Workgroup Administrator Utility in Access 2007 at http://support.microsoft.com/kb/918583 .
In your case, I suggest moving the tables you want to secure to another database and then linking to the front end database. This allows you more control over security. Using a password does not remove all users from the database. Logged-in users can still see tables.
source to share
One solution is to create your forms on one side (client side) and your tables on the other side (server side). Each user only has a copy of the forms and the tables are somewhere on the net.
Another solution is to install the access version (free to use) on the user's computer. In this case, the database window does not appear.
Whichever you choose, you will need to completely control access to commands, create your own (and disable the display of command access tables).
If you tackle the first solution, you will be on your way to a real client / server structure and you will sooner or later be able to switch to SQL Server for your tables (your forms can stay in the Access client application).
source to share
With some restrictions, you can completely remove links to external tables in the frontend / application:
- While opening the application, just use
ChDir "\\someShare\someFolder"
in VBA - The source of your forms and reports using SQL SELECT instead of saved queries / tables using the syntax:
SELECT field1, field2 FROM [BackendName_be.mdb].myTable
. Please note that the path is NOT specified !
This way the forms / reports will work just fine without any table (not even hidden) in the application.
If you put BE in a hidden / share folder and you get MDE, it will be very difficult for users to find where the data is.
This method has the added benefit of being able to instantly switch background (e.g. between test / prod).
source to share
Access does a very poor job of securing data in tables. Your users NEED access to tables to work with data, but you don't want them to possibly see EVERYTHING. You can encrypt the data in your tables yourself on the fly. I'll cover this in my Access Data Encryption workshop .
source to share