Since ACC2010 (and starting in ACC2007) the user level security profile was depreciated in Access... not that it really worked against the better than average user...
So now we're left to "role our own" user restrictions in access.
I must re-stress that none of the Access databases are really secure by design. One must look at one of the server based database systems in order to really establish a user-level security model.
This can get very complicated.
One option is multiple front-ends distributed to the proper users... I find this to have several problems the least of which is making sure that only the people that should have elevated privileges have proper frontend the other being... well... managing which version of which frontend is the one to use... arrgghh.
The simplest that I have setup uses two back-ends and OS level primary authentication.
+ OS Level: my IT is kind enough to allow me Administrative privileges over a subdirectory and has setup several user groups. I have directories that have group privileges and I add and remove users from these groups as needed.
+ Single front end, has all of the forms, queries, static data, etc...
++ The only linked tables that have the password stored in them are to the first backend. I figure that if the user can login to the server, then they should be able to get to the user database.
++ I use the AutoKeys macro (it's the only macro I routinely use) to trap keys such as [F11] and I hide the access object navigation pane from the start.
++ I store the ACCE version of the frontend in a read-only subdirectory on the server with a batch file in the parent directory. The batch file copies the frontend from the server to the user's pc (provided they can login to the network!).
+ First backend one has just the hashed user id, an encrypted field with the password to the second database stored, and an encrypted field that contains a random number. Both encrypted fields use the passphrase entered by the user to decrypt.
+ Second backend has the data, user privilege profile and a table that tracks user login/out dates.
Both backends are encrypted and password protected.
privilege profile...
Hashed user name an entry for every form allowed.
The form name is hashed using that random number stored in the second field. Makes it harder to add privilege... not impossible, just more work.
Keeping the second backend open...
I have global variable set:
- Public z_DataBackend As DAO.Database
When the user logs in, the password for the backend is then used in code to set the z_DataBackend connection. Now that it's opened, the linked tables to the second backend should work...
So, user opens the frontend
Enter's their user id
Enters their passphrase
The Hash of the user id is stored global variable (well now I'm using the tempvars collection)
The connection to the second backend is established
The random number is pulled from the field and stored in a global variable (or tempvar)
User navigates to a form.
All controls on the form are disabled by default.
Onload event, using that random number, appends the form name to the random number and MD5 digest. Then a query against the privilege table, no entry then the form has code to handle that... if it's a read only, then the form loads without enabled controls... if sensitive, then the form may redirect back to the main form etc...
Initial user setup,
I have a form that I enter the user ID and initial passphrase.
These are stored in the first backend along with a large random number.
I have a list of form names that builds on launch
Double click, the selected entry is passed to the code, which appends it to the random number and the digest calculated. The User Digest and the Form digest are then stored in the second backend. If the record already exists then the entry is removed.
>>oops, forgot... That large random number is also stored encrypted using the back end password... Need a way to alter user privileges :-) <<<
Because there will not be an entry in the table for recent logins, the user will be prompted to change the passphrase. The two encrypted fields are decrypted and then re-encrypted using the old and new passphrases (yes I have double entry confirmation before the changes are made... :) ) The user had to know the initial passphrase to get to this point and at no time do I store the passphrase.
+++ There's a lot of code here... a lot I've borrowed from other sites and other people and I've simplified a lot; however, I don't have the time to much deeper into this.
Microsoft Access / VBA Insights Sitemap has examples of RC4, AES, SHA2, MD5 encryption and digests and one can easily find VBA via Google, DuckDuckGO, etc... for these as well.
TwinnyFo also has an article at the above link:
How To Create User Permissions and Customized Menus in MS Access
That should prove useful.