473,695 Members | 2,914 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 5482
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_spa m." 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******* *************@g iganews.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.goog le.com...
| [..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_OBFUSCATIO N_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.c om 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.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #10

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

Similar topics

8
2254
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
959
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 fields. It would be nice to enforce a "true" state of these booleans with the existence of the security (key) tags mentioned first as security is not always required and it is not possible to systematically determine if the client has sent...
30
2840
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
4370
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
2256
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 help or something to read? Tnx Nico
2
5130
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 this kind of communication in a secure way (protected from sniffing)? It would be a very simple protocol. Question, and two possible answers 'yes' or 'no'.
33
2495
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 criteria: I have a list of about 100 numbers which correspond to the ID field and also another 40 say numbers corresponding to the numbers in the PRODUCT field. I want to show the rows that correspond to both these criteria.
9
2643
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 with this encrypted data this column contains normal data also.. But in DB2 while storing encrypted data column should be defined FOR BIT DATA. In my case how should I go as my column contains both encrypted and
2
7688
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 message digest. " and also explains how the length of the encrypted column can be derived.
0
8619
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8826
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8818
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7652
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6487
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
2996
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2261
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.