The way to 100% secure your accdb/accde file ? (1 Viewer)

[dhlao]

Anyone know the way to 100% secure your accdb/accde file ?

What I mean secure is disable the ability to modify and see the design structure of the tables, forms, VBA code, etc. This is very important once you finish it and ready to distribute it to your users.

Below are the tasks I made to the accdb/accde file before distribution:
1. Disable shift bypass https://support.microsoft.com/en-us/kb/826765
2. Modify the ribbon (set all the button and tab to visible=false)
3. Use "DoCmd.ShowToolbar "Ribbon", acToolbarNo"
4. Make below changes in Access Options > Current Database
a. Uncheck "Display Status Bar"
b. Uncheck "Display Document Tabs"
c. Uncheck "Use Access Special Keys"
d. Uncheck "Display Navigation Pane"
e. Set "Ribbon Name" to the one defined in step 2
f. Uncheck "Allow Full Menus"
g. Uncheck "Allow Default Shortcut Menus"
h. Check "Never Cache"
5. Save the file as ACCDE

When user open the accdb/accde file. They can only see and perform action in the preconfigured display form. They've no way to see the tables and use any Access features since all the ribbon and tab were removed. Shoutcut keys also disabled.

 

[CJ_London]

They've no way to see the tables

You've missed out splitting the db and password protecting the backend, otherwise a user can simply create a new blank db and link to the tables. Also, you need to hide the password in vba code and ensure it is created by calculation (otherwise someone can open the .accde in notepad and find the password)

 

[dhlao]

Hello CJ_London.
Sorry that I forgot to mention that the accde file has no table inside (except the table that use to store the backend connection password). All the tables are at the backend.
Those tables will link when the user open the accde file (of course first undergo a series of authentication first)

I don't know notepad can show the password that use to connect the backend database.When use notepad to open the accde file. I found that the password showed like this

Access;PWD=thisIsThePassword;

The design of my frontend accde allow user to change the connection password. The password is store in a table in the frontend. And the Input Mask is set to "Password". I need to find a way to encrypt the user password, then store the encrpyted string to the table. Decrypt it when need to connect the backend. No luck. Already encrypt the password. But I still need to decrypt it first before place the password at the line "PWD=thisIsThePassword;". Which mean everyone can still see the clear text password by using notepad

 

[gemma-the-husky]

with regard to all this, I really do struggle to see why anyone would want to prevent a user seeing their own data.

There are very few commercial systems that do not expose the data to validated users.

eg. take Sage Line 50

Sage provide an ODBC driver that gives full access to all the tables and fields. Sage data can therefore be used/viewed/copied/updated etc via Access/Excel etc.

I have no problem with wanting to protect the front end against unauthorised use. to be honest a mde/accde may not do this. Read everythingaccess.com to see what wayne says about "compiler junk" - it's not really junk at all. An mde/accde is not as well protected as you would think.

 

[gemma-the-husky]

Hello CJ_London.
Sorry that I forgot to mention that the accde file has no table inside (except the table that use to store the backend connection password). All the tables are at the backend.
Those tables will link when the user open the accde file (of course first undergo a series of authentication first)

I don't know notepad can show the password that use to connect the backend database.When use notepad to open the accde file. I found that the password showed like this

Access;PWD=thisIsThePassword;

The design of my frontend accde allow user to change the connection password. The password is store in a table in the frontend. And the Input Mask is set to "Password". I need to find a way to encrypt the user password, then store the encrpyted string to the table. Decrypt it when need to connect the backend. No luck. Already encrypt the password. But I still need to decrypt it first before place the password at the line "PWD=thisIsThePassword;". Which mean everyone can still see the clear text password by using notepad

fwiw, when you connect to an access table the connect string looks like this

;database=path;password=plaintextpassword

There is no way to encrypt the password used to open the back end.
(I mean within the way the string is stored. The password is just stored as plaintext)

Even if you delete tables on exit, I expect a user could get around this by opening the database while in use to get at the password, or could just "crash" the database task manager with so that the tables remain linked.

 

[CJ_London]

Sage provide an ODBC driver that gives full access to all the tables and fields. Sage data can therefore be used/viewed/copied/updated etc via Access/Excel etc.

it is read only access so cannot be updated (or certainly was, I haven't used Sage for some time), other than going through the Sage import functionality.

I agree why hide the data, but I don't agree you should allow users to be able to change it other than by going through the designed forms etc. If the data is so low level that you don't mind them messing with it directly, then you don't really need to do all the other stuff to protect the system.

Unfortunately you can't provide read only rights to access, it's full rights or nothing at all (so has to be controlled through code) - but then if the back end was SQL Server/MySQL you can set these rights.

You can get round it for an access backend by not using linked tables and queries - always assign recordsets for forms/reports/combos etc, rather than recordsources - and as previously mentioned, have the password built in code rather than hardcoded. More work, but depends on what you are trying to protect, from who and why.

But I guess anyone who is determined and skilled enough will find a way.

 

[gemma-the-husky]

@CJ

Maybe Sage is read only. I thought it was read write.
I have used ODBC drivers to amend data directly in other systems. Maybe I confused those with Sage.

I certainly agree the point about not doing it recklessly, but if a user wants to develop their own reports, say, from their own data - then I don't want to try and make that hard for them to do.

 

[The_Doc_Man]

The problem is that if you really wanted that level of security, you should not be working with Access. You need something that uses smartcard-level access, such as Oracle Web and a back-end database that does its own password validation via an encrypted connection between front-end and back-end.

I am not in any way attempting to downplay the power, ease, and flexibility of Access - but don't try to make it do something it really isn't designed to do. It is designed to let you get to your data.

There is the possibility that you would encrypt the file that contains the database password for the back-end. But all it takes is one clear-text version of that password file on one system to undo all your hiding. And it has to be the same password in each case, because there is only one BE file and it is encrypted only once, so everyone has to use the same key. But... if someone grabs and decompiles the code, they would see the method you used to decrypt the file and could duplicate the process to obtain that clear-text file anyway. So... how many layers of encryption and decryption were you planning to use before you gave up on hiding the password?

You can put barriers in the way - but the way to get around those barriers is still available because the allowed barriers are documented as to what they do! All you can EVER do with security is make it harder for someone to "breach the barriers" - but there is this pesky little phrase: Where there's a will, there's a way!

 

[CJ_London]

I don't want to try and make that hard for them to do.

neither do I. I provide a report writing facility which covers 90% of requirements, the other 10% they need to come to me!

I also have a datasheet form which provides a table view of all relevant data tables with the ability to sort, filter, hide columns etc. (but not add/amend) which they can then copy/paste to excel or use an export facility I provide.