ms access user level security 2010 accdb files

Various changes to the Windows Operating System, also for the Intranet

Moderator: sjj1805

Post Reply
User avatar
sjj1805
Posts: 1122
Joined: Fri Oct 27, 2006 12:45 am
operating_system: Windows 10 Pro
motherboard: Hewlett Packard 2AF7
system_drive: C
32bit or 64bit: 64 Bit
processor: 2-90 gigahertz Intel Core i5 4460S
ram: 8 GB
video card: NVIDIA GeForce GT 705
sound card: P40D100-4 NVIDIA High Definition Audio
Hard_Drive_Capacity: 8 TB
Location: Birmingham UK
Contact:

ms access user level security 2010 accdb files

Post by sjj1805 » Tue Oct 31, 2017 1:56 pm

Yes this CAN be done with an accdb file extension.
It just needs a bit more work.
Using the methods described below you can invoke 3 levels of "User Level Security" dependant upon your needs and security requirements.

Level 1 - Mild. Use this simple method if all you want is for users to log in using their own username/password. Here however your database is not protected and anyone can simply click on the database and it will open.
Level 2. Semi Protected. Use this method to force people to log in with a username/password. The backend database is not fully protected but I have included some extra steps to make it reasonably secure.
Level 3. Fully protected. The drawback here is that the database will be slower than normal. For most users I would think that level 2 would be a good compromise between security and performance.

To implement username/password security you will need a "MDW" file. You will need an earlier version of Microsoft Access to create and maintain this file. I would suggest you create it on another computer or within a Virtual Machine such as Virtual Box

To implement level 1 security - simply create a shortcut to your database with the necessary switches in place to invoke the MDW file.
for example

Code: Select all

C:\Accounts\Build.accdb /wrkgrp B:\acc.mdw
This will invoke the username/password log in box when someone tries to log into your database and their credentials are stored accordingly in CurrentUser()
However if anyone bypasses your shortcut and simply clicks on the actual database

Code: Select all

C:\Accounts\Build.accdb
then they are logged in as Admin - without having to supply a username/password.

Level 2 security prevents users from being able to simply click on the database to open it. Here you invoke "Database Security" by following These Steps - but ONLY implement these steps to the front end database not the backend. When you secure the backend database everything slows down to a crawl - however we can still protect the backend database using two further steps which I shall mention in a moment.
After implementing database security on the front end, once again create the same shortcut as before

Code: Select all

C:\Accounts\Build.accdb /wrkgrp B:\acc.mdw
What a user will now see is the same username/password login screen. After successful completion of that step they then see a further screen requesting the database password. Once again you will find that their details are available in Currentuser()

Using this method we now have to use some imagination to protect the backend database.
Firstly use windows security to only allow individuals or members of a windows usergroup access to the individual file.
Next create an Autoexec macro that will check to see who is logging into the database. If that user is named "Admin" then the macro will immediately quit the access database.

Level 3 security.
To implement this you must firstly add database security to the back end database BEFORE you add it to the front end.
When you add database security to the front end you have to re-link all of the tables that are in the back end database.
While this is extremely secure it also extremely slows down your database because all of the encrypting/decrypting is done on the fly.

User avatar
sjj1805
Posts: 1122
Joined: Fri Oct 27, 2006 12:45 am
operating_system: Windows 10 Pro
motherboard: Hewlett Packard 2AF7
system_drive: C
32bit or 64bit: 64 Bit
processor: 2-90 gigahertz Intel Core i5 4460S
ram: 8 GB
video card: NVIDIA GeForce GT 705
sound card: P40D100-4 NVIDIA High Definition Audio
Hard_Drive_Capacity: 8 TB
Location: Birmingham UK
Contact:

ms access user level security 2010 accdb files

Post by sjj1805 » Sun Nov 19, 2017 2:22 am

I have now found an even better way.
On the Front end implement "Level 2" as above.
On the backend - keep this as an MDB file. User level security still works on an MDB file.
Your front end will still link to the MDB backend.
Because the Back end only contains tables there are no compatibility issues.

Post Reply