Securing a Database in Access

[Rabbit]

INTRODUCTION
There has been considerable interest in the forum regarding Access and security. This is a primer on that topic.

First off, if you have a need for security, don't do it in Access. Security should always be implemented on the server side. The reason being that you should always keep the security files away from everyone except those that need them to implement the security. The problem with Access is that it is both the server and the client. This only holds true when using Access as the front end and the back end. You could use Access as a back end and a web server as a front end or vice versa, in which case you have separated the security from the clients.

Now, having said not to do it. Here's what you need to be aware of if you absolutely must use Access for both the front end and the back end.

Note About the Attachment
I have attached a sample implementation of some of the concepts in this primer. So that you can see how it's set up, some of the database had to remain unsecure.

The code is insecure so, ideally, you would want to create a compiled version of the database that the users will have access to.

Also, you should split out the stuff relating to account info into a separate, read-only database that a small group of people would have access to. Because of this, all password requests would have to go through a central point.

You will notice that I used a master password to encrypt the data. This is for flexibility. While it is more insecure to use a master password, it is necessary for functionality. If data was encrypted using the user password, you would lose data if the user forgets their password.

Despite any precautions you may take regarding the data, because Access is being used as the front end and the back end, a user could, in theory, decompile the compiled version of the database and find the master password. If this is not an acceptable risk, you must not use Access.

SECURING CODE
To secure code, you can do any number of the following:

• Lock the VBA project
• Create a compiled version of the database
• Encrypt the database

Each option has different weaknesses you will need to be aware of.

If you lock the VBA project, a user can not view the code unless they enter a password. However, they can view the database file using another editor, such as Notepad. In this state, the code is in a semi-compiled format and strings are still readable. They will not be able to edit the file though; If they try, Access will lock out the database. I suspect that Access uses a hash of the last modified time to keep track of whether someone tried to access it outside of the database.

With enough knowledge, a person could decompile the code, or find the string you're using as password. With even more knowledge about the file format, they could find the hash that Access uses to track the last modified date, modify the code, and reconstruct the hash so Access would never know it was modified.

If you use this method, keep a backup database somewhere secure because if someone tries to modify the database outside of Access, and you have only the one copy, everyone will get locked out.

If you create a compiled version of the database, the code will be editable, but unreadable. However, it is possible for a person to decompile the code. As with the prior method, you should keep a copy of the database in case you need to edit the code.

If you encrypt the database, the code and the data will be unreadable. However, the people using the database will need the password. And if they have the password, then they can read everything. And if they were so willing, could save an unencrypted version of the database.

Those are your three options to securing code. You can use one, two, or all of the options.

SECURING DATA
Usually, if there is a need to secure data, it is because there is some information that only some people need to see and others should never see.

In Access 2003, there was the option of creating a security workgroup file with user accounts and passwords. The problem with this is that the users needed to be able to read the workgroup file for the security to work. And if you knew enough about the file format, you could read, change, or delete passwords.

In Access 2007, they did away with user level security. So if you needed it, you had to develop it in Access 2003, or try to implement it yourself in code.

Since user level security is needed when some information should not be seen by other people, this poses a big problem for Access. Because no matter what you do, the users have direct access to the data. The only thing you can do is to encrypt all the data that needs to remain secure. That way, even if the user can see it, they can't read it unless they have the password to decrypt it.

User Level Security
When using Access as both the front end and the back end and you have a need to secure data, you need some sort of user level security. This consists of storing account logins and passwords, keeping this secure, and encrypting the data that is confidential.

Storing Account Info and Passwords
This is the most insecure part of trying to secure data in an Access database. First of all, you can't just store the password in the table since the user can read it. So what you need to do at a minimum is to run the password through a cryptographic hash algorithm.

But even if you stored the hash, the user can see which hash belongs to which account. They could just delete the hash or overwrite it. So you need a way to obscure the relationship between the hash and the account. I recommend having a second account table that stores the account ID as a hash or as encrypted data. This way, they would have to hash or encrypt the account ID they're trying to find before they would be able to modify password data.

There are a few ways of handling the obsfucation of the account ID, each with its own drawbacks. You could:

1. Hash the account ID
2. Encrypt the account ID with the user's password
3. Encrypt the account ID with a master password

If you choose 1, the user can hash the account ID they're trying to find so it's not much security. If you choose 2, you lose the ability to recover information if the user forgets their password. If you choose 3, you would have to code in the master password into the VBA coding.

However, the biggest security hole is that there's nothing to stop someone from deleting the table and recreating it with their own passwords. A way around this is to put the password data in a read-only file/database. But the drawback to this is that the user loses the ability to change their own password.

Here is a link to the SHA2 256-bit cryptographic hash algorithm.
http://bytes.com/topic/access/insigh...pt#post3643816

Encrypting Confidential Data
Since the user has direct access to the data, you must obfuscate the confidential data by encrypting it and decrypting it only when needed and the user is authorized.

There are two ways of handling the encryption of the data, each with its own drawbacks. You could:

1. Encrypt the data with the user's password
2. Encrypt the data with a master password

If you choose 1, you lose the ability to recover information if the user forgets their password. If you choose 2, you would have to code in the master password into the VBA coding.

There are many algorithms available that you can implement. Here are links to two algorithms that have VBA code. I recommend the second one because it is more secure and obscures the length of the data.
RC4 Stream Cipher
Advanced Encryption Standard

[Rabbit]

Attached a sample implementation to the original post.

Additional note about the attachment: I forgot to include the user account information I used.
User1 - password1
Admin2 - password2