Encryption to access file (1 Viewer)

poh99

Member
Local time
Today, 22:20
Joined
Sep 16, 2021
Messages
33
Hi, I need some guidance on access file encryption. My database will only be stored in one PC and all user can only access the file solely from that PC. I have created a virtual hard disk (VHD) on the PC and save the database in inside the VHD. I have split my database table and save the file in ACCDE format. The accde file was copied to my desktop so user will just access the database from desktop without going to the VHD.

I tried to encrypt the accdb file and when user access the accde file, they also need to key in the encrypted password. When user enter the password, it also means that user can access the accdb file to see the form, queries etc. How can I go about doing it such that user will not be able to view the forms etc? Similarly, for the backend (split database), how to encrypt so user unable to see the table data? And any ways to prevent user from copying out the accdb file to another PC?

I have read up on some of the forum thread and I am still very confused about it. Or did I do it wrongly?

There are only 3 files in the VHD (image attached).
 

Attachments

  • image.PNG
    image.PNG
    7.8 KB · Views: 262

theDBguy

I’m here to help
Staff member
Local time
Today, 07:20
Joined
Oct 29, 2018
Messages
21,357
Hi. Not sure what you are trying to protect, but if your data is highly sensitive, Access may not be the correct tool for you. Just saying...
 

poh99

Member
Local time
Today, 22:20
Joined
Sep 16, 2021
Messages
33
My data consists of customer personal details, product details etc. So some department does not need to have access to those data. My original database already segregate to the staff department so they can only access to those form that they need. Just want to ensure that my staff don't access the main table to view all table data.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:20
Joined
Oct 29, 2018
Messages
21,357
My data consists of customer personal details, product details etc. So some department does not need to have access to those data. My original database already segregate to the staff department so they can only access to those form that they need. Just want to ensure that my staff don't access the main table to view all table data.
Unfortunately, Access does not have the facility to provide granular security like the enterprise RDBMS do. With Access, it's all or nothing. You can try to use forms to limit user access to the data. But if they manage to find a way to get to the table, you won't be able to stop them from seeing everything. So, if your main concern is data security, you might consider using SQL Server or MySQL or Oracle to store your data.
 

poh99

Member
Local time
Today, 22:20
Joined
Sep 16, 2021
Messages
33
Ok thanks. Will see if my company want explore to SQL
 

isladogs

MVP / VIP
Local time
Today, 14:20
Joined
Jan 14, 2017
Messages
18,186
I would also recommend moving the BE data to e.g. SQL Server.
However, there are many things you can do within Access to protect the FE as well. See my article Improve Security 2.
You can also encrypt the data itself and use disconnected ADO recordsets to prevent users accessing the BE at all.
See my Encrypted Spilt No Strings example database
 

poh99

Member
Local time
Today, 22:20
Joined
Sep 16, 2021
Messages
33
Thanks. It is very informative and I am still digesting with the info.
 

isladogs

MVP / VIP
Local time
Today, 14:20
Joined
Jan 14, 2017
Messages
18,186
You're welcome. Feel free to ask if you have any questions.
 

LarryE

Active member
Local time
Today, 07:20
Joined
Aug 18, 2021
Messages
562
Have you tried simply saving your .accdb file as a .accde file? That protects VBA, macros and the navigation bar. No one can open any form or report in design mode. Just make sure each user has their own copy of the .accde frontend file on their computer and you keep a copy of the original .accdb file for development purposes. Once you convert a .accdb file to .accde, you cannot go back, so keep a copy of the original .accdb file in a safe place.
 

isladogs

MVP / VIP
Local time
Today, 14:20
Joined
Jan 14, 2017
Messages
18,186
Have you tried simply saving your .accdb file as a .accde file? That protects VBA, macros and the navigation bar. No one can open any form or report in design mode. Just make sure each user has their own copy of the .accde frontend file on their computer and you keep a copy of the original .accdb file for development purposes. Once you convert a .accdb file to .accde, you cannot go back, so keep a copy of the original .accdb file in a safe place.
Sorry but although there is some good advice above, your comments are not all true.
Macros can still be edited in an ACCDE as can tables and queries.
The navigation pane is still available and any object can be renamed or deleted.

If you change the file suffix from .ACCDE to .ACCDR it will be opened in runtime mode.
The navigation pane will then be inaccessible and the Access menus will be almost completely removed.
However changing the file name back to .ACCDE will reverse all of that so runtime mode should not be relied on for security on its own
 
Last edited:

poh99

Member
Local time
Today, 22:20
Joined
Sep 16, 2021
Messages
33
I would also recommend moving the BE data to e.g. SQL Server.
However, there are many things you can do within Access to protect the FE as well. See my article Improve Security 2.
You can also encrypt the data itself and use disconnected ADO recordsets to prevent users accessing the BE at all.
See my Encrypted Spilt No Strings example database
Hi, can correct me if I am wrong, I used the steps from "Remove ribbon menu items". If I used this, does that means I don't have to set password to my VBA code and encrypt my file since there is no way to view the vba code etc? Though I know that it is still advisable to do.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:20
Joined
May 7, 2009
Messages
19,169
VBA code and encrypt my file since there is no way to view the vba code etc
it can be viewed alright by third party tools.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:20
Joined
May 7, 2009
Messages
19,169
However, there are many things you can do within Access to protect the FE as well. See my article Improve Security 2.
You can also encrypt the data itself and use disconnected ADO recordsets to prevent users accessing the BE at all.
See my Encrypted Spilt No Strings example database
this was proven wrong on one of the conference.
where it was cracked without using any third party tools.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:20
Joined
Oct 29, 2018
Messages
21,357
Hi, can correct me if I am wrong, I used the steps from "Remove ribbon menu items". If I used this, does that means I don't have to set password to my VBA code and encrypt my file since there is no way to view the vba code etc? Though I know that it is still advisable to do.
Hi. If I understand your question correctly, doing ut that way only affects you. If I am not mistaken, that setting only applies to your copy of Access. Other users probably wouldn't be affected by your ribbon changes.
 

poh99

Member
Local time
Today, 22:20
Joined
Sep 16, 2021
Messages
33
Thanks expert. I will try to work around with this and try using third party tools to try as well. Main thing is we have a dedicated PC for this Access file so all of my staff will use this PC to access the data. To make any changes, I will also made from that PC.
 

isladogs

MVP / VIP
Local time
Today, 14:20
Joined
Jan 14, 2017
Messages
18,186
@poh99
The amount of security you need will be determined by a number of factors including the nature of the data itself, whether your authorised users are likely to tamper with the app intentionally or otherwise and whether it is likely to be hacked by professionals.

Standard approaches including removing the ribbon and navigation pane, disabling special keys and the shift bypass, using an ACCDE FE and encrypting the BE with a password are always worth doing. Unfortunately a VBA password is easy to circumvent and I wouldn't rely on it at all.

However doing all the above won't stop determined hackers. Third party tools make hacking easier but they aren't required.
For example, I have written an Access utility for my own use which allows me to view the contents of all tables (including deep hidden tables) in any Access file with a single button click.

Further encrypting the BE data itself will add a further layer of complexity to the process both for you as the developer and for any hackers.
A very early version of my encrypt no strings app was indeed cracked at a conference in 2019 by Philipp Stiefel (with my prior agreement) but it has since been made much more secure. The methods he used then no longer work and AFAIK the latest version can no longer be hacked using Access itself.

Nevertheless using a SQL Server BE (or similar) would be even more secure if setup correctly and would be strongly recommended if security is that important. Even so, you need to be aware that there is no such thing as 100% security no matter which program is used to store data...and that includes SQL Server

Hope that helps

@theDBguy
Removing the ribbon from your FE is an application setting. It applies to ALL computers running that FE...NOT just your own.
 
Last edited:

poh99

Member
Local time
Today, 22:20
Joined
Sep 16, 2021
Messages
33
Thanks for the clarification. Appreciate it much
 

Users who are viewing this thread

Top Bottom