Version 5.46 First Published 30 Jun 2018 Last Updated 22 July 2021 Difficulty level : Advanced
Section Links:
How the application works
Further Info
RC4 Encryption Code
RC4 Encryption Examples
Downloads
Acknowledgements
Passwords
Further Reading
Feedback
How the application works
Return To Top
This is a significantly updated version of an example application written in response to numerous forum questions about methods of preventing data being stolen from Access databases. For example, this thread at Access World Forums: Prevent Importing ODBC tables from ACCDE
It is intended to show how the data in an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.
To demonstrate its use, I am setting a simple security challenge: export the backend data to an external application such as Excel.
The attached example application should behave exactly as any split database ...
. . . BUT there are no linked tables and therefore no connection strings visible in the navigation pane or MSysObjects system table etc.
In addition, the data is protected using a RC4 encryption cipher.
The application consists of an ACCDE frontend (FE) and an ACCDB backend (BE) with about 2000 records.
For the purposes of this example both FE and BE must be saved in the same folder. Make sure it is a trusted location.
The ACCDE front-end (FE) has been locked down with the Access application interface hidden, the navigation pane and ribbon removed.
The FE is also encrypted with a very simple password isladogs
The ACCDB BE file is also encrypted with a different and much stronger password – this is not supplied nor required to use the application.
The backend datafile contains 1 'deep hidden' table (though it would work equally well with a standard table)
The frontend database contains several forms and a report. None of the forms or the report have a saved record source.
As the forms/reports have no record source, there is no link to the BE table. Therefore, there is no connection string accessible to that table (other than in the code).
Instead, disconnected ADO recordsets (AKA in-memory recordsets) are created using code when the object is loaded and destroyed when it is closed. This approach works perfectly for forms but cannot be used with reports. For that reason, the report data is actually a form with a disconnected ADO recordset used as a subreport.
All the fields in the BE table except the autonumber primary key field PersonID have been encrypted using RC4 encryption (the cipher keys have not been supplied ... nor are they needed to use this).
The application also contains several additional security measures to deter hacking using automation. For example:
a) It cannot be opened from a non-trusted location
b) It cannot be run using automation from an external application. Any attempts to do so will cause the application to close after a warning message.
c) Right click context menus have been disabled as have various keyboard shortcuts such as Ctrl-C, Ctrl-V, F12 etc.
Taken together, these measures should prevent anyone being able to export the data to an external application such as Excel (hence the challenge!).
NOTE:
It is IMPOSSIBLE to prevent anyone taking a screenshot of the data. For that reason, the forms/reports should not allow users to view the entire dataset if your data is so sensitive that this approach is used. For that reason, the use of screenshots is NOT considered an acceptable solution to the security challenge!
Further Information
Return To Top
1. In this example, the entire application interface has been hidden so all forms are shown 'floating on the desktop'. The report is shown with just the
Access title bar - no ribbon or quick Access toolbar (QAT). However, doing each of these certainly isn’t essential to the idea behind the application.
2. I have deliberately left the encrypted data table so it can be viewed but NOT directly edited.
As it contains encrypted data, editing those fields would lead to partly encrypted data being visible in the form.
3. Although the table contains encrypted data, it is displayed unencrypted in the main form and the report
The main form is fully editable and new records can be added. Any changes will automatically be encrypted.
4. Be aware that creating the editable form will take much longer than usual as unbound controls must be used.
The form contains 2 sets of each control in order to allow editing of the encrypted data. Unbound controls are used to display the decrypted data.
Doing this also means code needs to be added to each unbound control to encrypt the entered data
5. If anyone does manage to directly access the data tables, all they will see is encrypted data. However, it is of course still possible for anyone with authorised
access to the FE to print the decrypted data using a report (if allowed) or just take screenshots of the data.
6. This approach is only worth considering if your data is highly sensitive.
If you do want to use this approach with highly sensitive data of your own, ensure that:
a) The BE database is given a different and very strong password to the FE (see below for password security info)
b) Both the ribbon and navigation pane are removed from the FE. All interaction via forms ONLY
c) End users should NEVER be told the BE password
d) All data is stored in the BE. There should be no data tables in the FE
e) The FE is distributed as an ACCDE so the code is not accessible
f) The Access BE file is stored securely on the server to which end users have no access
Much better still - use SQL Server or similar for the BE database as that is several orders of magnitude more secure if properly configured
g) A strong 128-bit encryption method is used such as RC4 or any other secure cipher
XOR encoding is NOT recommended as it is too easy to decode
You can use different encryption keys for each table and/or use a different cipher key for each field if it seems worth the additional coding effort needed
h) OPTIONAL - for additional security, the BE tables can be 'deep hidden' as in this DEMO.
However, it isn't essential to do so provided you ensure users have no means of accessing the BE
7. You need to be aware that encrypting date or number fields is problematic as the encryption cipher converts data into 'random' text strings.
Hence, I have used a text field for the date of birth (DOB) field above
There is little point encrypting fields with limited values such as Gender (M/F) or Title (Mr/Mrs/Ms/Miss etc). However, I have done so here for completeness.
8. As mentioned earlier, disconnected ADO recordsets work well for forms but CANNOT be used with reports. There are various alternative solutions including:
Method 3 was used in this version of the example application.
9. Encryption and decryption are done using the same RC4 cipher and key. In other words, the encryption is reversible.
This feature makes coding much easier to manage but it is also a weakness of RC4.
However, unless the encryption key is known, it is almost impossible to decrypt the data
Other ciphers exist such as AES where the encryption is 'one-way', but coding will therefore be more complex still.
RC4 Encryption Code
Return To Top
The RC4 encryption code used in this application is as follows. It should be placed in a standard module e.g. modEncryption
'##############################################################
'# RC4 encryption function
'# Author: Andreas J”nsson http://www.freevbcode.com/ShowCode.asp?ID=4398
'# RC4 is a stream cipher designed by Rivest for RSA Security.
'#
'# Amended by Colin Riddington / Chris Arnold 14/03/2019 to fix error 9
'##############################################################
Public Function RC4(ByVal Expression As String, ByVal Password As String) As String
On Error Resume Next
Dim rb(0 To 255) As Integer, x As Long, Y As Long, z As Long, Key() As Byte, ByteArray() As Byte, temp As Byte
If Len(Password) = 0 Then
Exit Function
End If
If Len(Expression) = 0 Then
Exit Function
End If
If Len(Password) > 256 Then
Key() = StrConv(Left$(Password, 256), vbFromUnicode)
Else
Key() = StrConv(Password, vbFromUnicode)
End If
For x = 0 To 255
rb(x) = x
Next x
x = 0
Y = 0
z = 0
For x = 0 To 255
Y = (Y + rb(x) + Key(x Mod Len(Password))) Mod 256
temp = rb(x)
rb(x) = rb(Y)
rb(Y) = temp
Next x
x = 0
Y = 0
z = 0
ByteArray() = StrConv(Expression, vbFromUnicode)
'Next line changed after discussion with Chris Arnold as it causes error 9 - subscript out of range
'Arrays start at 0 so this needs to end with Len(Expression)-1
' For x = 0 To Len(Expression)
For x = 0 To Len(Expression) - 1 'Colin Riddington - 14/03/2019
Y = (Y + 1) Mod 256
z = (z + rb(Y)) Mod 256
temp = rb(Y)
rb(Y) = rb(z)
rb(z) = temp
ByteArray(x) = ByteArray(x) Xor (rb((rb(Y) + rb(z)) Mod 256))
Next x
RC4 = StrConv(ByteArray, vbUnicode)
End Function
RC4 Encryption examples
Return To Top
Using the same string (gothic19) with 5 different keys:
Org: gothic19 Key: abc Enc: ªòA1¼ÜJ
Org: gothic19 Key: conundrum Enc: oÞÈN\jBú
Org: gothic19 Key: a53frt23 Enc: í9 ¿tRõ
Org: gothic19 Key: nempnett67thrubwell Enc: ˆ´¡¦!“
Org: gothic19 Key: abracadabra Enc: cïöTcn‰¯
Effect of altering the original string:
Org: Xgothic19 Key: abracadabra Enc: \çíHbdÛ§°
Org: Xgothic819 Key: abracadabra Enc: \çíHbdÛ®¸w
Finally, I will repeat some comments I have written many times previously in relation to security in Access:
a) Access databases can NEVER be made 100% secure
b) A capable and determined hacker can break any Access database given sufficient time and motivation.
c) However, by erecting various barriers, it is certainly possible to make the process so difficult and time consuming that it isn't normally
worth attempting.
d) Access apps (or any applications) are only as secure as the weakest part of the security used
I hope this idea will be interesting for others to use / adapt / improve.
I have used both encryption and the 'no strings' approach for particularly sensitive data in both Access and SQL Server but have never felt it necessary to do so for a whole database. I'll leave others to decide how practical this would be for an entire application!
Downloads Return To Top
a) The example application used in this article (Approx 1.2 MB - zipped)
EncryptNoStrings32_365
32-bit ACCDE frontend FEX32.accde & BEX.accdb (Access 365 format)
EncryptNoStrings64_365
64-bit ACCDE frontend FEX64.accde & BEX.accdb (Access 365 format)
b)
Encrypted Split No Strings Database
This article as a PDF file (approx 0.3 MB - zipped)
Acknowledgements Return To Top
I would like to thank various Access developers who have suggested ideas and improvements for this application. Particular thanks are due to:
Philipp Stiefel - for informing me of weaknesses in earlier versions which he used (with my permission) in presentations on Access security to Access developers.
One of these presentations can be found on YouTube at How (In)Secure is Access Today?- Live at Virtual Access Cascade Conference 2020.
The section starts at around 16 minutes into the presentation. Philipp explains in detail how he was able to use automation to circumvent the security in the earlier version.
The tests done by Philipp provided the incentive for me to further improve the security in this application!
Since then many further changes have been made. As a result, the methods used by Philipp no longer work in the latest version.
Depending on how users attempt to gain access from an external program, one of the following messages will be seen and the application will then close automatically.
Andy Thieu (rabbit) - for helping me to better understand some of the different approaches used by potential hackers to crack applications such as this
Chris Arnold - for providing valuable assistance with code used with disconnected ADO recordsets
Leo (theDBGuy) - for testing several earlier versions of this application
Password Encryption Return To Top
ACCDB/ACCDE passwords also encrypt the entire file using up to 128-bit encryption.
For that reason, these passwords can only be broken by a brute force attack.
If the password is reasonably strong this can be a very lengthy process.
Chart taken from www.linkedin.com/posts/cbtech-support_ever-wonder-how-long-it-would-theoretically-activity-6715288518240845824-Aiyx
According to https://www.security.org/how-secure-is-my-password the strong BE password used in this application would take about 41 trillion years to crack using a brute force attack....in fact slighlty less as I've given you some hints to get you started!!
By comparison, the supplied FE password (isladogs) is very weak and would only take a few seconds to crack. In a real-life application, the FE password would need to be strengthened using a longer password containing a mixture of numbers, upper and lower case letters and possibly special characters.
Further Reading Return To Top
This article is a companion to the following items at this website:
Access File Security
Improve Security
Purpose of System Tables
Security Challenges
Feedback Return To Top
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Do let me know if you find any bugs in the application.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 22 July 2021
|
Return to Security Challenges Page
|
Return to Top
|