473,231 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

searching for encrypted fields in data columns

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

Jul 20 '05 #1
10 5439
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
--
Jul 20 '05 #2
> 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"
Jul 20 '05 #3
> 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
--
Jul 20 '05 #4
"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
Jul 20 '05 #5
<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
Jul 20 '05 #6

<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.
Jul 20 '05 #7
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.
Jul 20 '05 #8
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

Jul 20 '05 #9
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)

Jul 20 '05 #10
> 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
--
Jul 20 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Anita | last post by:
Hi All I have a table with one column : CREATE TABLE test2 ( a char(15) primary KEY CLUSTERED ) The column a is filled with encrypted data (contains control and extended characters).
0
by: raymondpendergraph | last post by:
I have a need to match security tags (signed keys) with certain other encrypted tags in the xml (certain keys to certain tags). There is also an attribute "Encrypted" which is a boolean in these...
30
by: zn | last post by:
If I place a form for product ordering on my website, what scripting technology or CGIs can I use to encrypt the e-mail sent to my e-mail account with the order information? Thanks.
5
by: Peter Guetens | last post by:
Hi, How can one search a value on all fields in a table is this possible ? thanks Peter
5
by: Nico | last post by:
My database have 20 tables and many users. I wish to store encrypted data in 3 tables and have only 3 users have access to them, walking into tables or using forms. Can someone point me a direct...
2
by: Leonardo D'Ippolito | last post by:
Hi! I have two .NET win apps that need to communicate on a TCP/IP network. 'App A' must ask 'app B' if it's allowed to do some task, and 'app B' must authorize or prohibit it. How can I do...
33
by: Geoff Jones | last post by:
Hiya I have a DataTable containing thousands of records. Each record has a primary key field called "ID" and another field called "PRODUCT" I want to retrieve the rows that satisy the following...
9
by: Suresh | last post by:
Hi All I have one column which has data type of varchar only. in this column i want to store some secured data (I want to store this data by encrpting using db2 encryption function).But along...
2
by: Bernard Dhooghe | last post by:
The information center writes: "Encryption Algorithm: The internal encryption algorithm used is RC2 block cipher with padding, the 128-bit secret key is derived from the password using a MD2...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.