Hi Les,
Lets see if we can go through this step by step so to speak. I will
attempt to answer each point in turn as we go, and expand on the
actual methodology I used.
1. Relevant tables in the data mdb are individually encrypted by
encrypting each relevant field.
What I did here was to build a function that wasa used for both
encryption and decryption of a field, based on the AES algorithm.
Effectively you can choose how many AES keys are used for securing the
data. I used one AES key per table.
I applied the function to encrypt / decrypt such that any data read
from the table was unintelligible without decrypting it through the
function, and in turn no data was written to the table without being
encrypted with the function. In this way each field was encrypted with
the same AES key. This was done through unbound forms and code. The
end user never actually saw the encryption taking place.
The AES key for each table was stored as a table property with DAO
code, encrypted Asymmetrically (public / private), for each user that
had access to the table. Each users public key was used to encrypt a
copy of the AES key for the table and store that as the value for the
property. Only the users private key would be able to decrypt the copy
of the AES key stored as a property specific to that user (eg/ I made
a table property with the same name as the username, and stored the
encrypted (users public key) form of the AES key as the value of that
property).
2. Decryption keys are stored, encrypted, in the front-end db (does it
have to be separate from the application front-end?)
I actually kept all the encryption keys stored in the back end, and
only the functionality to use them in the front end. This way the
front end never really needed changing once the encrypt / decrypt
functionality had been built in.
Everything stored in the backend db was in ciphertext (encrypted
form). This way it did not matter if someone stole a copy of the
backend db, it was effectively useless. If they connected via code
from a different application to try and read the data all they would
get is meaningless rubbish from each field.
I made a third application specific to the front end / back end
application that was specifically used to administer the cryptography.
In this third application I had the ability to connect to the back end
and add users (and the associated encrypted keys) to the tables. In
this Crypto Admin app I kept stored the AES keys for the tables, as
well as the public and private key pairs for each users asymmetric
keys. I also kept a 'master' public / private key pair, which I
associated the same as a user, to each table. This was a type of
failsafe in case I had to do some form of data recovery. The master
user had no login to the normal application though. In theory you dont
actually need it because you have the AES keys anyway, so you could
leave it out.
I also built a function into the Crypto Admin application to be able
to dump the data into a non encrypted database if necessary. I was
never truly comfortable with the function as I felt that it was
dangerous to have this potentially in the wrong hands, but the bosses
wanted it (sigh).
I also made sure that I had a log table built into the backend, and
into the crypto admin app, so that all user activity was recorded. I
dont know if you need to go this far or not, but it is a useful idea
if you are tracking attempted breaches. I kept the Crypto Admin
application completely separate from the network, it lived (lives) on
a secure (password to access) USB key, and a backup of the AES keys is
printed out and stored in a safe, along with a copy of all the code,
and a CD with empty versions of the finished apps.
Oh yeah, I almost forgot. I also built in to the Crypto Admin app the
ability to change the AES keys for each table in case they were felt
to be compromised. I did this by having the app simply create a new db
with the appropriate table structures, and then quite literally read
each row from the source, decrypt it with the old key, encrypt the
data with the new key, and store it in the new backend db. Needless to
say this was a time consuming process but a nice safety feature to
have.
3. Access to the decryption keys is controlled by some user entered
password.
The user access to each table was done via checking if the user had a
table property in their name, with a stored AES key value. This was
also able to be checked for validity (ie/ to see if someone had just
copied the property from another table).
The way the user asymmetric keys were used is as follows. Bare with me
it takes a little to go through it.
1/ A Private / Public key pair is generated for a user in the Crypto
Admin application.
2/ The user is (in the crypto admin app) 'assigned' the tables that
they are allowed to access
3/ For each table that the user is allowed access the users public key
is used to encrypt the appropriate tables AES key, which is then
stored as a table property using the users name as the property name.
4/ The users stored encrypted copy of a tables AES key can be checked
for validity by either using the decrypted AES key to decypher a known
value and see that it is true (such as a table property that holds a
copy in encrypted form of the tables name), or by placing a MD5 hash
value with the stored AES key that matches the users name or password
or some other known value. I went with the latter, but the former is
probably easier to do.
Actually if I were to do this again, I would make a table property and
store an MD5 hash of the tables name in it, encrypted with the tables
AES key. When a user tries to access the table the form (code) checks
to see if the user has an associated table property in their name,
then uses the users private key to decrypt the stored encrypted AES
key, then uses the AES key to decypt the stored MD5 hash (the known
value) and checks this against the MD5 hash generated at runtime for
the tables actual name. If they match then the user is valid for the
table, if the MD5 hashes dont match then something has either gone
wrong or someone has copied the username / stored value from another
table and is using it to try and break the table in question.
This way, with code, you can assign different users access to
different tables without fear that because they have access to one
area of data that they could access other areas that they may not be
allowed to.
The Crypto Admin part was to assign these users to the tables and
associate the keys properly. It made life a lot easier than trying to
do this through the front end, and allowed the private keys and AES
keys another layer of security by never having them directly available
to the 'public'.
4. It seems obvious that any field that has been encrypted can no
longer be directly bound to an Access control. It must be displayed via
a function and updated by code.
This is absolutely correct. I would recommend doing the encryption
control through a third application as I talked about above. Use code
for everything that needs encryption, and hard code the needed
functionality into the front end. Keep the admin separate from the
front end, and keep the data in the back end.
I hope this helps clear this up a little. As I said it was a pain to
do this. The weak point as I mentioned earlier was in the storage of
the usernames and passwords (with the private keys). I was giving this
a little thought since your first post, and *maybe* have a better way
to do it than the one I first used.
It occurs to me that it would be better to keep the usernames
completely obfuscated if possible so that it makes things very hard
for someone to be able to reverse engineer them. For this you could
use again MD5 hashing. for the users login, they would type their
username and password. Both the username and password are MD5 hashed.
The front end checks a table in the back end for a matching value for
the username. If this is found then the password MD5 hash is used as
an AES key to decrypt the users private key, and some known value
check for validity same as mentioned before.
As with all cryptographic applications, it is a complex task to get it
right. Even the best cryptographic ciphers can be undone by poor
system design (think Enigma in WW2). In this case the weakest point as
I see it is the username / password area used for the login to get the
users private key. If you are able to overcome that with a better
system design then go for it. I would recommend it if the data is
truly valuable. The best you could realistically go for here is tri-
factor security, something you have (a token), something you know
(username / password), and something you are (biometric). Might be
overkill, but keeping the users private key out of the system would
make this application really strong. If you can get to the dual factor
level that would be brilliant for most purposes.
Hope this helps
Cheers
The Frog
