By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Re: Advice on securing a sensitive Access database

P: n/a
In article
<fc**********************************@s50g2000hsb.>, The
Frog <Mr************@googlemail.comMon, 14 Apr 2008 00:45:10 writes
>So how do we solve the problem of your DB encryption? We use Asymmetric
to encrypt the Symmetric keys. The 'heavy lifting' of encryption /
decryption of the data is actually handled by the AES cipher which is
relatively fast, and only the decryption of the AES keys is done with
the slower Asymmetric cipher.
Not sure if I quite follow that.

1. Data encrypted by AES key

2. AES key encrypted with Asymmetric public key (?)

3. AES key decrypted with Asymmetric private key (?)

4. Data decrypted by AES key

What have we achieved? The Asymmetric private key still has to be made

I'm sure your previous post has the answer to this, but I can't quite
see it.
Les Desser
(The Reply-to address IS valid)
Jun 27 '08 #1
Share this Question
Share on Google+
10 Replies

P: n/a
Hi Les,

We have a lot of points here to cover, so bare with me as I attempt to
work through them for you one by one:........
>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
not so obvious?
***Answer: The reason for this was more of a matter of design
philosophy. You could also do this as a table. I preferred to make the
system as difficult to duplicate as possible. That being said an
experienced Access Programmer would still be able to locate the extra
table properties. It just helped to rule out the 'middle' level users
who know enough to be a bother but not enough to be a serious threat.
The security of the data ultimately does not depend on the obfuscation
of the key storage, but rather on the strength of the ciphers 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,
The same function to do both?
*******Answer: With symmetric encryption the same function is used for
both encryption and decryption purposes. You really only need one
function here, but if you wish to make the code more understandable
you could always create two functions, or use an object (as done in
one of the implementations that yields a class module) so that you
have three properties of the class, one for the AES key, and one for
each the encrypted and decrypted data (plaintext and ciphertext). You
could build in functionality so that if the value stored in the
encrypted property was changed by placing a new value from the app to
the object that the decrypted would automatically reflect the change.
I chose to ditch the class module in favour of a function (If I
remember correctly......).

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

****Answer: Yep, exactly that, a different key for each table :-)
>>An Access question: Could controls not be bound to the decrypt

****Answer: It depends on your design philosophy. I chose to use code
to control every aspect of each form and report. This made for a
little more work in the construction of the forms, particularly with
regards to list boxes and the like, but in the end the functions
needed to populate the controls with appropriately decrypted data only
had to be written once, and then called from the form (again by code)
to actually fill the control with the decrypted data. There are a
myriad of ways you could approach this, so it really is just a matter
of working out one that suits the design of your app and reverse
engineering it into the existing forms, reports, etc...

>>>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
with the key should stop that.

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?
****Answer: Because of the private key / public key way of enciphering
the tables AES key, only the correct private key will decrypt a users
enciphered AES table key. This means that if a user duplicates another
users table key and renames it for themselves (lets say), then it
still wont help them unless they possess the specific private key for
the table key they copied. Their own private key is unique to them and
so wont work with anyone elses public key encrypted data. It only
works for data that is (in this case the table key) that is encrypted
with their matching public key. The key pairs are unique, and as such
the protection of the private key is really important, hence also my
comments on keeping the private keys out of the system if possible.

>>I wonder if some hardware would help. Fingerprint reader? (I have no
idea how secure they are)
****Answer: With regards to using hardware for storing the private
keys and also for biometric authentication you need to look around at
this stuff. There are a lot fo rubbish components on the market and
very few that are actually reliable AND secure. One of the best that I
have seen for securely storing keys was from Rainbow Technologies
(this is not a product endorsement, but rather an endorsement of the
type of approach used by this technology) - they had / have a product
called the iKey that acted as a storage container, a very secure one,
for keys / certificates, and if I remember correctly they also had a
model that could do cryptographic calculations. It was basically a USB
key, so no special hardware was needed on the computer with the
application, just some drivers. I saw this about ten years ago now, so
I would expect that today there are many versions of this sort of
thing available. If you can find one that has a simple to administer
system for creating / removing users and keys, and can be integrated
into your app without too much hassle then I would certainly entertain
the idea. It was what I wanted to do with the app I built. I was
hoping to find a USB key, with the ability to store the Public key for
a user as well as have it password protected on the key, and with an
integrated fingerprint/thimbprint reader. This would have been a nice
tri-factor authentication system. Cost is also a factor here too, some
of these things can be pretty expensive from memory.

I have also seen keyboards that have integrated smart card readers
(credit card tpe cards with smart chips on them) and finger /
thumbprint readers. I have also heard of software that can tell who
you are by just the way you type on the keyboard. Voice is another
possibility, but of course it can be recorded.

A passing thought on security here too. If your data is **REALLY**
sensitive, and the possibility exists that a user may be co-erced by
force into accessing the system / information, you may want to think
about placing a dummy table with false data that LOOKS real enough.
What you do is to have the user enter their password backwards or
something like that when under duress, and on login check it, and if
the password is entered backwards then only show the rubbish fake data
- but make the application look like it is working perfectly. At the
same time send a message to someone to let them know of the intrusion
and duress situation. Pretty extreme and certainly not an everyday
thing, but I have seen situations where this is necessary.

>>Don't understand "keeping the users private key out of the system"
****Answer: I think we covered this above, but again briefly it comes
down to a matter of adequately securing the system. The private /
public key pairs are the core of getting access to the tables AES
keys. What we need to do to really make it safe is to keep the private
key as secure as possible. Because the private keys, in the model I
ended up deploying, are stored in the database itself (although
encrypted), they represent a risk to the security of the system. Even
though the encryption is strong (AES 256), the password that the user
chooses becomes the weak point - it could be guessed or forced from
the user - and hence the private key becomes available - hence the
data becomes available. By keeping the private key separate
completely, it does not matter if the user password is guessable or
not - you are just eliminating risk from the system design.

If you use longer passwords as a minimum then users tend to write them
down or use easily rememberable phrases that can be guessed. This goes
back to the point about tri-factor authentication. That is considered
by many to be very strong, but even dual factor would provide a
massive increase in the level of security for the application - in
this case username/password (something you know) and a separate device/
storage for the private key (something you have). The 'something you
are' part would put the icing on the cake so to speak.

So in short, storing the private keys is the weakest point in my
existing app, due to the fact that a users password may be obtainable.
Keeping the private keys out of the system goes a long way to
eliminating this deficiency, and having a biometric pretty well
completes it. Another possibility would be to require two users to
authenticate themselves before the system was functional, and there
are cryptographic methods that can achieve this. Its all a matter of
approach. How far do you want to go to protect the data? What is the
cost of having unauthorised access? Basically you need a risk analysis
to figure out how far to go. There is always another level of security
you can add, the trick is knowing how little is too little and how
much is too much.
>>What have we achieved? The Asymmetric private key still has to be made
available. (from the third posting)

****Answer: That is exactly correct, the private key for the user
needs to be made available to the user when they perform a successful
login. Only then should the users private key be available to them and
the application. The public key can be seen by anyone so it doesnt
really matter.

The users private key is NEEDED to obtain and decrypt the tables AES
key. Thats why we have a copy of the AES key encrypted with table for
each user, using each users unique public key.

I think what you missed here was that each user gets a unique public /
private assymmetric key pair. Only the AES key is common, and only
then on a per table basis. Its kind of like having a lock box for each
user, and each lock box has a unique key that only that user has.
Inside each lock box is another key, lets say to the beer fridge :-).
If I take my lock box, using my key and open it, then I can get access
to the beer fridge key and hence the beer. If however I take my key,
and I try and use it to open another users lock box it wont work
because it needs that users key and not mine - so I cant get the berr
fridge key and hence no beer :-(

What we have in this design is the same beer fridge thinking for each
table, in effect a series of independantly locked beer fridges - the
key from one beer fridge wont open another fridge. We have for each
fridge a set of lock boxes, one for each user, each secured with the
users lock (same lock for one user across all of their lock boxes).
The user can take their key, open their lock box if they have one for
a specific fridge, then take the key to the fridge and get some beer.
The user cant open another users lock box, and they cant take a key
from one fridge and use it in another fridge. BUT, because the user
has only a single key for all of their lock boxes (and hence all the
fridges that they can get beer from because they can get the fridge
key), it is very important to protect the users private key. That
private key in this analogy is the unique user private key they obtain
when they log in, and in my app is stored in the back end database -
and hence also my strong desire to keep the key away from the database
and potentially weak passwords.

I hope this helps a bit. I know this can be a tricky area to deal
with. As I said before, the application of the cryptography is the key
to success here. Its worth taking a little extra time to get the
details worked out for the implementation. The algorithms are actually
only useful if they are applied in a secure system design. The most
common cryptographic mistake I have seen is people using really
capable algorithms and really poor system design - effectivley putting
a steel door on the front of the home and a fly wire screen on the
back. If you can get the private keys out of the application /
database and store them separately and securely then do it!


The Frog
Jun 27 '08 #2

P: n/a
In article
<ab**********************************@a70g2000hsh.>, The
Frog <Mr************@googlemail.comThu, 17 Apr 2008 01:59:10 writes

>I hope this helps a bit.
More than a bit - I think I now do understand the issues and techniques

I am very grateful for you long and comprehensive reply. I am now going
off to have a quick read of Cryptography for Dummies.

I'm sure I'll be back :)

Many thanks again.

Les Desser
(The Reply-to address IS valid)
Jun 27 '08 #3

P: n/a
Anytime. Come back with any questions you may have and I will do my
best to answer them. Another good read for a budding cryptologist is a
book by Simon Singh called 'The Code Book'. It gives you an
understanding of what the different types of cryptography are as well
as how they have and are used. Looking back through history with this
book shows quite clearly where systems have failed not because the
cryptographic standard was insufficient, but rather that the
implementation was insuffucient and made an otherwise strong cipher

Its all in the way you do it :-)


The Frog
Jun 27 '08 #4

P: n/a
We have progressed a long way and we are testing the encryption
functions and have seen some odd behaviour and wonder if there is a

We have a query that shows the original text, the encrypted string and
then the decrypted string - it is working fine.

However, there is a certain pattern of results that we do not understand
where we have identical string values in different records (user name
and event log text).

In the case of user name (8 chars), we have records with identical
strings and the encrypted values (32 chars) are totally different each

So question one is if the initial values are the same why are the
encrypted values not the same (we are not using an initialization

In the case of the log text (20 chars) the first 32 characters of the
encrypted string is always the same while the remaining 32 are always

So question two is why are we seeing this odd behaviour.

We intend using an initialisation vector to stop this happening but
would still like to understand what is going on.
Les Desser
(The Reply-to address IS valid)
Jun 27 '08 #5

P: n/a
Hi Les,

I just want to make sure I understand this correctly so that I can
answer properly. What I think you are saying is that when using the
same (I am assuming AES) key to encrypt some text / data, that you are
seeing different outputs in different parts of the program.

If this is the case I suspect that the source of this lies in the
string / plaintext to byte conversion (and possibly the reverse also).
Theoretically the operation to encrypt (without padding) should
produce the same result for the same text / data each time it is done
if using the same key.

Things to check for are these:
1/ Are the keys being used actually the same
2/ Is the data decrypting properly
3/ If you are operating the code "By Hand" in the editor and encrypt
something does the result match the same when using the application
4/ Are the sections of code that are used to encrypt and decrypt the
same? (ie/ is the process and encryption / decryption methods being
used the same)
5/ Does the data decrypt properly?
6/ Is the source (plaintext) exactly the same when doing the testing.

The most common issue I have come across was the conversion of the
plaintext to a bytearray was producing different bytearrays. As it
turnded out the code that was feeding the plaintext to the encryption
algorithm was actually producing different plaintexts that looked the
same on screen (so to speak) but were in fact different. This came in
my case from some simple data type conversions that I wasnt handling
properly. The root cause was the variant data type being used to house
the plaintext instead of a string. When dealing with numbers in
particular it caused issues. Using strict data types eliminated the

Aside from that, the only other thing that I could think of is that
there may be an issue with the algorithm implementation itself. If you
like we can try and beat the source code to death to see if there are
any uncooperative lines of code :-) Please understand I am not asking
to post your proprietary code here or data, just the algorithm
implementation code so that if you think we need to dissect it we can.

Please keep me posted, and try the "By Hand" testing with the same
data multiple times to see if you get the same strange behaviour.


The Frog
Jun 27 '08 #6

P: n/a
In article
<4e**********************************@z72g2000hsb.>, The
Frog <Mr************@googlemail.comMon, 19 May 2008 01:37:51 writes

>Please keep me posted,
Thank you for your previous post. We have introduced an IV so the
problem has gone away. I still want to understand what was going on so
will do some more test when I can.

One side affect of the encryption has surfaced. Obvious really, but did
not think of it.

If fields are encrypted then basically all indexes go out of the window.

So to fill a pull-down list from a table, the whole table needs to be
decrypted (we are using a query calling the decrypt function) and then
Access has to build a temporary index to sort.

For 26K records the decrypt is instant (query without sort) but with the
sort it is about 16 seconds.

Two solutions we can think of. Have a separate field containing the
first character in clear text or equivalent ASCI code. Or introduce our
own sequence field. Both solutions require these fields to be
maintained every time the text is changed or a record created.
Les Desser
(The Reply-to address IS valid)
Jun 27 '08 #7

P: n/a
Hi Les,

Yes, indexing can be slightly problematic. One thing I did to help was
to populate a recordset with the results of the 'decryption query',
and then did my sorting / filtering from there. It is much faster to
operate in memory than off the HD. I dont know if this will suit your
situation though. I found working with ADO disconnected recordsets
quite efficient for this purpose, and you can keep the data secure
even across the 'wire' this way by having a two stage process:
1/ first query simply gets the data 'raw' from the encrypted recordset
still in encrypted form
2/ Make a function that takes the 'raw' recordset and returns a
decrypted one.

Let me know if you want to bounce this around some more.


The Frog
Jun 27 '08 #8

P: n/a
In article
<e2**********************************@m3g2000hsc.g>, The
Frog <Mr************@googlemail.comFri, 23 May 2008 02:05:50 writes
>Let me know if you want to bounce this around some more.
I would just like to tender my sincere thanks for all your help.

The project is coming to an end - based primarily on your advice and

Many thanks once again.
Les Desser
(The Reply-to address IS valid)
Jun 27 '08 #9

P: n/a
Hi Les,

You are most welcome. I am glad that I have had the opportunity to
help you out and hopefully add a small contribution to this group as
well. I have enjoyed helping with this. I also wish you all success
with the project. Drop the group a message sometime and let us know
how it has all worked out. My guess is that with the standards
employed here the security on the DB should be pretty damn solid!

Congratulations, Well done, and thankyou too for your feedback - I
really appreciate it :-)


The Frog
Jun 27 '08 #10

P: n/a
In article
<3e**********************************@k13g2000hse.>, The
Frog <Mr************@googlemail.comWed, 18 Jun 2008 01:08:35 writes
>Drop the group a message sometime and let us know how it has all worked
Once it goes live I will try and remember to let you know how it went.
Les Desser
(The Reply-to address IS valid)
Jun 27 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.