searching for encrypted fields in data columns

[sffan]

I am new to database programming and was curious how others solve the
problem of storing encrypted in data in db table columns and then
subsequently searching for these records.
The particular problem that I am facing is in dealing with (privacy)
critical information like credit-card #s and SSNs or business critical
information like sales opportunity size or revenue in the database. The
requirement is that this data be stored encrypted (and not in the
clear). Just limiting access to tables with this data isn't sufficient.
Does any database provide native facilities to store specific columns as
encrypted data ? The other option I have is to use something like RC4 to
encrypt the data before storing them in the database.

However, the subsequent problem is how do I search/sort on these columns
? Its not a big deal if I have a few hundred records; I could
potentially retrieve all the records, decrypt the specific fields and
then do in process searches/sorts. But what happens when I have (say) a
million records - I really don't want to suck in all that data and work
on it but instead use the native db search/sort capabilities.

Any suggestions and past experiences would be greatly appreciated.

much thanks,
~s

[David Portas]

Google in the Microsoft newsgroups and you'll find references to various
encryption products for SQLServer.

Why is encryption a requirement for your application? Encryption is for
authentication and for secure communication in an insecure environment.
Assuming the database server is located in a physically secure location and
assuming you can use an encrypted network protocol what extra security
benefit do you expect to gain from encryption in the database? Encrypted or
not the data will still (only) be secured by an access control mechanism of
some sort (a user name and password?). Why would access control be more
secure if the data is encrypted than if it isn't?

If you want to ensure privacy on an individual basis (each user has access
only to his/her own data) then encryption might make sense but in that case
you wouldn't expect to do global searches.

--
David Portas
SQL Server MVP
--

[Brian Peasland]

> Why is encryption a requirement for your application? Encryption is for

authentication and for secure communication in an insecure environment.
Not entirely so. There are many cases where encrypting the data in the
database has advantages.
Assuming the database server is located in a physically secure location and
assuming you can use an encrypted network protocol what extra security
benefit do you expect to gain from encryption in the database?
Two reasons...

Firstly, to keep the data contents safe should your system ever be
hacked. There have been cases where data fell into the wrong hands. If
that data is in the wrong hands, should it be easily used? Those of us
who work in some federal govt sites now have a requirement to encrypt
personal information. Should a hacker gain access to credit cards,
social security numbers, etc., there are no problems if the data is
encrypted, unless you left the decryption keys out in the open too. No
matter how secure your database is, there will always be holes and
exploits that can be used to gain unwanted access. Encryption is the
next line of defense after good security policies.

Secondly, you may want to encrypt data to secure that data even from
those who you have granted access to that data. For instance, a DBA has
basically free reign over the database and can see the data in that
database. As a DBA, do I really need to see someone's credit card number
or social security number? Of course not. The actual value of that data
is unimportant to my DBA tasks. So while I have access to the data, it
is a good idea that I can't see the real values. Therefore, encryption
is used.
Encrypted or
not the data will still (only) be secured by an access control mechanism of
some sort (a user name and password?). Why would access control be more
secure if the data is encrypted than if it isn't?

Nothing is saying that one should bypass good access control mechanisms.
Security has many layers. If username/password were sufficient in
keeping unwanted individuals away from a machine or a database, then why
do you need a firewall? You need a firewall because userid/passwords are
not enough. You also need other methods of access control. Encrypting
the data is another method of access control. If you need access to the
data, just getting past the firewall and providing a valid
userid/password is not enough. You also need the decryption keys.
Cheers,
Brian


--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.
"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[David Portas]

> Firstly, to keep the data contents safe should your system ever be

hacked. There have been cases where data fell into the wrong hands. If
that data is in the wrong hands, should it be easily used? Those of us
who work in some federal govt sites now have a requirement to encrypt
personal information. Should a hacker gain access to credit cards,
social security numbers, etc., there are no problems if the data is
encrypted, unless you left the decryption keys out in the open too. No
matter how secure your database is, there will always be holes and
exploits that can be used to gain unwanted access. Encryption is the
next line of defense after good security policies.

All that is valid if you have some form of access control to your encryption
keys which is more secure than the username/password security typically
available in the OS and database. Maybe my experience is limited but some
(many?) database encryption systems rely only on password-based encryption
with password policies no better than those offered by Windows. I realize
that better options are available but I wanted to understand whether the OP
had some particular requirement in mind or just made an assumption that an
encrypted database was inherently more secure than a non-encrypted one.

--
David Portas
SQL Server MVP
--

[sffan]

"David Portas" <RE****************************@acm.org> wrote in message news:<pa********************@giganews.com>...
[..snipped..]

All that is valid if you have some form of access control to your encryption
keys which is more secure than the username/password security typically
available in the OS and database. Maybe my experience is limited but some
(many?) database encryption systems rely only on password-based encryption
with password policies no better than those offered by Windows. I realize
that better options are available but I wanted to understand whether the OP
had some particular requirement in mind or just made an assumption that an
encrypted database was inherently more secure than a non-encrypted one.

To clarify, the system I am dealing with has a multilayer access
control mechanism. Username/password is simply for authentication; a
responsibility based engine controls what data is made visible to each
user. Further, the meta-data defines which fields store sensitive data
- these are encrypted with keys available only to a master user. The
data access layer uses a separate set of credentials and is the only
layer that can access the database directly. The raw column data get
mapped to business objects on which access control is applied.

I realize the importance of good key management and I think that in
itself is orthogonal to the original problem that I posed. I figured
(maybe incorrectly) that this was a common enough problem that atleast
a few databases provided some generic mechanism to encrypt column
level data.
Consider, the case of salary information stored in the database. For
one, we don't want nosy DBAs looking up exactly who gets paid what.
Currently, what I need to do is invoke my encryption service on each
datum individually. Subsequently, I also need to have a sort/search
service that applies constraints on queries. For example, if I want to
get a list of all employees getting paid between $100K - $200K, I need
retrieve all rows, decrypt the data and then apply my constraint. If
the data hadn't been encrypted, I could have trivially retrieved this
data using SQL.

Anyway, I hope this gives you all a better understanding of my
problem. If any of you have had to address this issue, I would like to
hear about your approaches.

Much Thanks,
~s

[Mark C. Stock]

<sf***@hotmail.com> wrote in message
news:7e*************************@posting.google.co m...
| [..snipped..]
|
| Anyway, I hope this gives you all a better understanding of my
| problem. If any of you have had to address this issue, I would like to
| hear about your approaches.
|
| Much Thanks,
| ~s

see my short paper at
http://www.doug-mi.org/June2000/Pres...query06200.PDF for an
overview of using Oracle's DBSM_OBFUSCATION_TOOLKIT (also see
http://www.enquery.com/presentations.html for a paper that includes
additional examples and issues regarding data encryption in oracle).

we have used similar techniques to retro-fit encryption to an existing
application without (immediately) changing any SQL code (specific changes
were made for tuning as needed)

contact me offline if you'd like more details

;-{ mcs

[Alec]

<sf***@hotmail.com> wrote in message news:40**************@hotmail.com...

Does any database provide native facilities to store specific columns as
encrypted data ? The other option I have is to use something like RC4 to
encrypt the data before storing them in the database.

However, the subsequent problem is how do I search/sort on these columns
? Its not a big deal if I have a few hundred records; I could
potentially retrieve all the records, decrypt the specific fields and
then do in process searches/sorts. But what happens when I have (say) a
million records - I really don't want to suck in all that data and work
on it but instead use the native db search/sort capabilities.

PointBase is a Java database that supports data encryption in a way that is
transparent to the user. It supports several encryption algorithm
implementations you can choose from. You just use a database parameter to
set the algorithm and the key and data encryption/decryption is handled with
no futher attention from you. If you use the Server version, you can also
encrypt the communication between the client and the server.

[Steve Jorgensen]

On Mon, 01 Mar 2004 00:22:55 -0800, sf***@hotmail.com wrote:

I am new to database programming and was curious how others solve the
problem of storing encrypted in data in db table columns and then
subsequently searching for these records.
The particular problem that I am facing is in dealing with (privacy)
critical information like credit-card #s and SSNs or business critical
information like sales opportunity size or revenue in the database. The
requirement is that this data be stored encrypted (and not in the
clear). Just limiting access to tables with this data isn't sufficient.
Does any database provide native facilities to store specific columns as
encrypted data ? The other option I have is to use something like RC4 to
encrypt the data before storing them in the database.

However, the subsequent problem is how do I search/sort on these columns
? Its not a big deal if I have a few hundred records; I could
potentially retrieve all the records, decrypt the specific fields and
then do in process searches/sorts. But what happens when I have (say) a
million records - I really don't want to suck in all that data and work
on it but instead use the native db search/sort capabilities.

Any suggestions and past experiences would be greatly appreciated.

much thanks,
~s

If you use PKI-style encryption, you can make the encryption key public, and
the decryption key private. This way, you can encrypt a card number, then do
a search for a match on that value. Since the card number will encrypt
identically each time, the search will match.

Of course, a fundamental problem with such a system is that, given the limited
number of valid card numbers relative to the number your database is likely to
contain, a constructing s brute force attack on such a system would not be
hard. This would be true of any system that allows searching for a card
number, no matter how it actually worked. This situation could be improved if
other key information was also encrypted so that even if a match were found,
the other necessary card owner information would remain unknown.

[nbnet]

I am by no means as experienced as the rest in the DB area being a
programmer in mostly web apps but.... I needed to store usernames and
passwords in our db in some kind of secure mannner. I understand that if you
just encrypt the password a hacker can use brute force to easily (not to me
of course) find out passwords. The solution was to use a combination of the
username and password, encrypt this using a key compiled into our binary and
store this in the db. Do this whenever the user logs in and send the binary
data to the db for comparison with the encrypted password in the db. I guess
the thinking is that the hacker would have a harder time finding the
password if it is made of a combination of values before encryption. One
advantage is that no-one knows the key in our binary, only encrypted
passwords are passed on the web, and access to the db doesn't display the
password in a usable format. You could use this thinking to possibly create
an encryption from the username, password and credit card number? I don't
know hacker methods and don't know if this is the best way, or if you have
the ability to encrypt using the username, password, card... but just food
for thought.
<sf***@hotmail.com> wrote in message news:40**************@hotmail.com...

I am new to database programming and was curious how others solve the
problem of storing encrypted in data in db table columns and then
subsequently searching for these records.
The particular problem that I am facing is in dealing with (privacy)
critical information like credit-card #s and SSNs or business critical
information like sales opportunity size or revenue in the database. The
requirement is that this data be stored encrypted (and not in the
clear). Just limiting access to tables with this data isn't sufficient.
Does any database provide native facilities to store specific columns as
encrypted data ? The other option I have is to use something like RC4 to
encrypt the data before storing them in the database.

However, the subsequent problem is how do I search/sort on these columns
? Its not a big deal if I have a few hundred records; I could
potentially retrieve all the records, decrypt the specific fields and
then do in process searches/sorts. But what happens when I have (say) a
million records - I really don't want to suck in all that data and work
on it but instead use the native db search/sort capabilities.

Any suggestions and past experiences would be greatly appreciated.

much thanks,
~s

[Daniel Morgan]

nbnet wrote:

I am by no means as experienced as the rest in the DB area being a
programmer in mostly web apps but.... I needed to store usernames and
passwords in our db in some kind of secure mannner. I understand that if you
just encrypt the password a hacker can use brute force to easily (not to me
of course) find out passwords.

Use Oracle's built-in obfuscation toolkit and the only ones breaking
it by brute force will be NSA, CIA, and FBI or their foreign equivs.

You can look it up at http://tahiti.oracle.com

But if you think it is easy to break even simple encryptions try
your luck on this one:

x := 'p78o 8o 0 o42i4p';
SELECT TRANSLATE(x,'?????', '?????')
FROM dual;

and I'm even giving you the code required to do it. Just replace
each of the five question marks with the correct number of the
correct characters.

Answer is available at:
http://www.psoug.org/reference/translate_replace.html

As simplistic as this example is ... do you really think any of
your users could break it?
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[David Portas]

> advantage is that no-one knows the key in our binary, only encrypted

Except someone who (legitimately or illegitimately) has the binary and can
therefore hack *any* users password... Or someone who hacks the users
machine... Or hostile code on the users machine... etc, etc.

Use a secure hash for this type of authentication security.

--
David Portas
SQL Server MVP
--