Re: Advice on securing a sensitive Access database

[Les Desser]

In article
<fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s5 0g2000hsb.googl egroups.com>, The
Frog <Mr.Frog.to.you @googlemail.com Mon, 14 Apr 2008 00:45:10 writes

[snip detail]

>So, crash course in cryptography aside,

Thank you for that. It was very clear and I actually understand it!

>here are some links that I have used for the different algorithms and
>components:

Thank you for all that. I will go through them in the next few days, but
it is your first post that I still need to study.

Thanks also for your kind offer of help.
--
Les Desser
(The Reply-to address IS valid)

[Mr.Frog.to.you@googlemail.com]

Re: Advice on securing a sensitive Access database

Anytime. Glad I can offer a little help.

Cheers

The Frog

[Mr.Frog.to.you@googlemail.com]

Re: Advice on securing a sensitive Access database

Hi Les,

I also found the code for the GUID creation, courtesy of Trigeminal. I
have used this in Excel and Access before and it seems to work
fine :-)

Enjoy

'------------------------------------------
' basGuid from http://www.trigeminal.com/code/guids.bas
' You may use this code in your applications, just make
' sure you keep the (c) notice and don't publish it anywhere
' as your own
' Copyright (c) 1999 Trigeminal Software, Inc. All Rights Reserved
'------------------------------------------


Option Compare Binary


' Note that although Variants now have
' a VT_GUID type, this type is unsupported in VBA,
' so we must define our own here that will have the same
' binary layout as all GUIDs are expected by COM to
' have.
Public Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type


Public Declare Function StringFromGUID2 Lib "ole32.dll" _
(rclsid As GUID, ByVal lpsz As Long, ByVal cbMax As Long) As Long
Public Declare Function CoCreateGuid Lib "ole32.dll" _
(rclsid As GUID) As Long


'------------------------------------------------------------
' StGuidGen
'
' Generates a new GUID, returning it in canonical
' (string) format
'------------------------------------------------------------
Public Function StGuidGen() As String
Dim rclsid As GUID


If CoCreateGuid(rc lsid) = 0 Then
StGuidGen = StGuidFromGuid( rclsid)
End If
End Function


'------------------------------------------------------------
' StGuidFromGuid
'
' Converts a binary GUID to a canonical (string) GUID.
'------------------------------------------------------------
Public Function StGuidFromGuid( rclsid As GUID) As String
Dim rc As Long
Dim stGuid As String


' 39 chars for the GUID plus room for the Null char
stGuid = String$(40, vbNullChar)
rc = StringFromGUID2 (rclsid, StrPtr(stGuid), Len(stGuid) - 1)
StGuidFromGuid = Left$(stGuid, rc - 1)
End Function


Cheers

The Frog

[Les Desser]

Re: Advice on securing a sensitive Access database

In article
<3220408f-857e-4fa1-9f80-4c6df8374740@a7 0g2000hsh.googl egroups.com>,
"Mr.Frog.to.you @googlemail.com " <Mr.Frog.to.you @googlemail.com Tue, 15
Apr 2008 00:23:40 writes

>I also found the code for the GUID creation, courtesy of Trigeminal. I
>have used this in Excel and Access before and it seems to work fine :-)

Thank you.

I have read trough your first post again and am having some difficulty
understanding it all.

It would be helpful to get an overview of what is being achieved.

For starters, is the following correct?

1. Relevant tables in the data mdb are individually encrypted by
encrypting each relevant field.

2. Decryption keys are stored, encrypted, in the front-end db (does it
have to be separate from the application front-end?)

3. Access to the decryption keys is controlled by some user entered
password.

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.

Point 4 is not a problem as it is only limited data that needs
encryption.


--
Les Desser
(The Reply-to address IS valid)

[Mr.Frog.to.you@googlemail.com]

Re: Advice on securing a sensitive Access database

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

[Les Desser]

Re: Advice on securing a sensitive Access database

In article
<44947f60-dbcc-4b17-979b-d70aa505dee3@2g 2000hsn.googleg roups.com>,
"Mr.Frog.to.you @googlemail.com " <Mr.Frog.to.you @googlemail.com Wed, 16
Apr 2008 01:24:04 writes

>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.

Why a table property rather than as a separate table? Just to make it
not so obvious?
--
Les Desser
(The Reply-to address IS valid)

[Les Desser]

Re: Advice on securing a sensitive Access database

In article
<44947f60-dbcc-4b17-979b-d70aa505dee3@2g 2000hsn.googleg roups.com>,
"Mr.Frog.to.you @googlemail.com " <Mr.Frog.to.you @googlemail.com Wed, 16
Apr 2008 01:24:04 writes

(As I worked through your notes, later parts answered some earlier
questions. I have removed some but may have left some others by
mistake)

>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,

The same function to do both?

>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.
>

A *different* key for each 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.
>

An Access question: Could controls not be bound to the decrypt
function?

>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).
>

So we have each user with their own encrypted copy of the key.

>
>

>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
>functionalit y had been built in.
>

Understood

>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.
>

[... balance of notes left for later digestion..]

>

>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)

That is OK to check one user's key being copied to an other table. What
about one user's key being copied to the same table under a different
user's name?

Storing an encrypted copy of the table name and the user name together
with the key should stop that.

>, 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.
>

Don't you also need to check in the same way in case the property has
been copied on the same table for a different user?

>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.
>

Makes a lot of sense

>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
>functionalit y 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.

Say that again!

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 wonder if some hardware would help. Fingerprint reader? (I have no
idea how secure they are)

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.

Don't understand "keeping the users private key out of the system"

If you can get to the dual factor
>level that would be brilliant for most purposes.
>
>Hope this helps

Very much so! I am at the stage that as I work through your notes I
think I understand each step but I cannot say I have a clear picture in
my head of all the steps. I need to re-read a few more times.
--
Les Desser
(The Reply-to address IS valid)

[Les Desser]

Re: Advice on securing a sensitive Access database

In article
<44947f60-dbcc-4b17-979b-d70aa505dee3@2g 2000hsn.googleg roups.com>,
"Mr.Frog.to.you @googlemail.com " <Mr.Frog.to.you @googlemail.com Wed, 16
Apr 2008 01:24:04 writes

Needless to say this was a time consuming process but a nice safety
>feature to have.

I know this is really an "impossible " question to answer accurately, but
....

What sort of effort - in man days - should this project take in Access
for an experienced Access developer (ignoring research on the cryptology
side).

--
Les Desser
(The Reply-to address IS valid)

[Les Desser]

Re: Advice on securing a sensitive Access database

In article
<44947f60-dbcc-4b17-979b-d70aa505dee3@2g 2000hsn.googleg roups.com>,
"Mr.Frog.to.you @googlemail.com " <Mr.Frog.to.you @googlemail.com Wed, 16
Apr 2008 01:24:04 writes

>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.

I have been going carefully through your posts and am progressing well.

Just one query at this point. Why cannot the AES key be encrypted with
a second symmetric key rather than Asymmetric?

In any event, you are not publishing the public key and the private key
has to be kept secret so why not just use AES a second time?
--
Les Desser
(The Reply-to address IS valid)

[The Frog]

Re: Advice on securing a sensitive Access database

Hi Les,

The use of the Public / Private key pairs is simply for the separation
of users from one another. By using the key pairs for each user you
have the ability to grant / revoke permissions on a table by table
basis, or alternatively remove a user from access completely without
interrupting any other users of the system.

In essence, if you were to use a single AES key as the access code for
all users, say for example they type in a username / password and get
the AES key back in return, you have the problem of how to secure that
AES key. Where would you store it? By using the public / private key
pairs you dont have to worry about that problem because the only
exposed information is able to be publicly exposed. In this way you
can store any AES key or keys or data with a public / private key
pair.

The question then becomes why not use the public / private key pairs
as the primary means of encrypting all the data. There are two reasons
for this, the first being speed. Public / Private key encryption is
much slower to operate than symmetric encryption such as AES or DES
etc... It is theoretically possible to operate this way, but the
'horsepower' needed to do it in a timely manner for any significant
amount of data becomes a serious problem, even with current hardware.

The second reason is that you need to keep a 'central' copy of the
data available to be worked with for each user. If you use Public /
Private to encrypt this data you would require that each user has a
unique copy of the data as only their key pair could properly would
with that encrypted copy.

In short then, the basis for using the two schemes together is simply
one of practicality. We use the public / private key pairs to secure a
unique (user specific) copy of the 'central' AES key(s) that are
needed to get at the data. Each user logs in, their private key
generated from their password and username, decrypts the AES keys as
needed to access the data they are allowed to access. When you need to
remove a user from the system you can remain confident that that the
data will remain secure and simply remove the users username /
password from the system and not disrupt any other user in the system.

In theory you could also use AES or another symmetric cipher as the
means of securing the other AES keys. The condition being that you
still maintain the separation between the users username / password
key, and the key(s) needed to get at the data. If you do this it can
work fine, but you will lose the ability to operate with things like
secure tokens and such which typically work with certificates and
asymmetric encryption schemes. If you dont need / want the option to
employ tri-factor security then you can go with the more simple
username and password only. You would need to make some sort of check
in the authentication system / login so that the app knows if the user
has entered the correct data of course, same as before, then just make
sure that a copy of the tables AES key is encrypted for each user with
the users AES key. I think this should pretty much work the same for
you if you dont need the extra authentication of a smart card / token.

Is this what you were asking? I hope I understood you correctly. If
you need more info just let me know :-)

Cheers

The Frog

[Les Desser]

Re: Advice on securing a sensitive Access database

In article
<b803c470-c9c3-41d6-b02c-e29e8fbeefa1@b1 g2000hsg.google groups.com>, The
Frog <Mr.Frog.to.you @googlemail.com Thu, 8 May 2008 02:15:05 writes

[...]

>Is this what you were asking? I hope I understood you correctly.

Yes - just perfect.

>If you need more info just let me know :-)

I'm sure I will be back :)

Thanks again.
--
Les Desser
(The Reply-to address IS valid)