Ms Access DB Users

[bryte yorke]

just begun working with ms access. I am trying to develop a school management system. I am done with tables, query, forms etc. Now the database system will have three users basically. i.e, administrator, and two other guest users. Please how do i go about it. I NEED HELP.
THANK YOU

[zmbd]

Can you be more specific?
I am guessing that you are wanting to setup user level restrictions and that is not one of Access' strengths, especially since ACC2007/2010.
There are kludge approaches; however, anything done in ACC can be bypassed by someone like myself with very little effort.

[jimatqsi]

Bryte Yorke, I think you need to know how to split a database into a front-end and a back-end. What you'll do is make a copy of your Access file. One copy will be the front-end, one the the back-end.

In the back-end you can delete all the forms, reports and queries. In the front-end, delete all the tables. Place your back-end somewhere on the network that is accessible to all users.

Then, link your front-end to all of the tables in the back-end. How to do that depends on your version of Access. I believe this link will help you with all versions from 2007 and later. https://support.microsoft.com/en-us/kb/304932

Give each user a copy of the front-end. When you make program updates you'll have to be sure to give every user a new copy of the front-end.

Jim

[zmbd]

that's certainly one approach; however, I was waiting for something more concrete regarding Byrte's requirements before making suggestions.

Splitting the front- and back- ends is usually my first step in anything other than very simple databases intended for a single person access at any given point in time.

For sensitive information one will need to look at some form of encryption. This can be very simple scheme such as a single password and the built in encryption offered by access or "record-field by record-field" using something such as AES or RC4 (refer to the articles located at Microsoft Access / VBA Insights Sitemap for a very good example of each.

Access isn't the most secure work environment by nature so be aware of the limitations and the ability of the better than average user to workaround most things setup in Access for security.

[bryte yorke]

thanks for your responses. Specifically what i want to do is to make the admin user have full access to the database but the other user should be restricted. can macros do this? and how do i go about it? User should only access the database with a log in username and password

[zmbd]

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.

[NeoPa]

AES Encryption Algorithm for VBA and VBScript & RC4 Encryption Algorithm for VBA and VBScript are both excellent articles for such stuff. I've used them myself ;-)