在数据列中搜索加密字段 [英] searching for encrypted fields in data columns

问题描述

我是数据库编程的新手，并且很好奇其他人如何解决在db表列中存储加密数据的问题，然后随后搜索这些记录。
随后搜索这些记录。

我面临的特殊问题是处理（隐私）

重要信息，如信用卡＃和SSN或关键业务

数据库中的销售机会规模或收入等信息。

要求是这些数据是加密存储的（而不是

清除）。仅使用此数据限制对表的访问是不够的。

是否有任何数据库提供本地工具来存储特定列作为加密数据？
加密数据？我有的另一个选择就是使用类似RC4的东西来对数据进行加密，然后将数据存储到数据库中。


然而，后续问题是怎么做的我在这些栏目上搜索/排序

？如果我有几百条记录，这不是什么大问题;我可以

潜在地检索所有记录，解密特定字段和

然后进行搜索/排序。但是当我有（比方说）一两百万美元的记录时会发生什么 - 我真的不想吮吸所有这些数据并且在它上面工作

而是使用本地数据库搜索/排序功能。


任何建议和过去的经验都将不胜感激。


非常感谢，

~s

解决方案

谷歌在微软新闻组中，你会发现各种各样的参考

SQLServer的加密产品。


为什么加密是您的应用程序的要求？加密用于

身份验证以及在不安全的环境中进行安全通信。

假设数据库服务器位于物理安全的位置并且假设
您可以使用加密的网络协议，您希望从数据库中加密获得额外的安全性吗？加密或

不是数据仍然（仅）通过访问控制机制来保护

某种类型（用户名和密码？）。如果数据是加密的，为什么访问控制会更加安全？b / b
如果你想确保个人隐私的话（每个用户只能访问他/她自己的数据
）然后加密可能有意义但在这种情况下

你不会期望进行全局搜索。


-

David Portas

SQL Server MVP

-

>为什么加密是您的应用程序的要求？加密用于

身份验证以及在不安全的环境中进行安全通信。


不完全如此。在许多情况下，加密

数据库中的数据具有优势。

假设数据库服务器位于物理安全的位置并且
假设您可以使用加密的网络协议您期望从数据库中的加密中获得多大的安全性？


两个原因......


首先，如果你的系统是保证数据内容安全

黑客攻击。有些情况下数据落入坏人之手。如果

数据落入坏人手中，是否应该很容易使用？我们这些在某些联邦政府网站工作的
现在要求加密

个人信息。如果黑客获得信用卡，社交安全号码等等，如果数据是加密的话，没有问题，除非您将解密密钥留在开放的。否

无论数据库的安全性如何，总会有漏洞和

漏洞，可用于获取不必要的访问权限。在良好的安全策略之后，加密是

的下一道防线。


其次，您可能希望加密数据以保护数据，甚至来自

您已授予访问该数据的权限。例如，DBA基本上可以免费统治数据库，并可以查看该数据库中的数据。作为一名DBA，我真的需要看到某人的信用卡号码

或社会安全号码吗？当然不是。该数据的实际价值

对我的DBA任务并不重要。因此，虽然我可以访问数据，但它是一个好主意，我看不到真正的价值观。因此，使用加密

。

加密或
不是数据仍然（仅）通过某种访问控制机制来保护（用户名和密码？）。如果数据是加密的，为什么访问控制更安全呢？

没有什么可以绕过良好的访问控制机制。

安全性有很多层次。如果用户名/密码足以让不受欢迎的个人远离机器或数据库，那么为什么

你需要防火墙吗？你需要一个防火墙，因为用户名/密码是不足够的。您还需要其他访问控制方法。加密

数据是另一种访问控制方法。如果您需要访问

数据，只需通过防火墙并提供有效的

用户ID /密码是不够的。你还需要解密密钥。

干杯，

Brian


-

================================================ == =================

Brian Peasland
dba@remove_spam.peasland.com


删除remove_spam。从电子邮件地址给我发电子邮件。

我可以便宜，快捷，优秀地给你。现在选择两个

三个

>首先，如果你的系统曾被

黑客攻击，那么要保证数据内容的安全。有些情况下数据落入坏人之手。如果数据落入坏人之手，是否应该易于使用？我们这些在某些联邦政府网站工作的人现在需要加密个人信息。如果黑客获得信用卡，社会安全号码等，如果数据被加密则没有问题，除非您将解密密钥保留在公开状态。不管数据库的安全性如何，总会有漏洞和漏洞可用于获取不必要的访问权限。良好的安全策略之后，加密是下一道防线。

如果您对加密有某种形式的访问控制，那么所有这些都是有效的。 >
密钥比操作系统和数据库中可用的用户名/密码安全性更安全

。也许我的经验是有限的但是一些

（很多？）数据库加密系统只依赖于基于密码的加密

，密码策略并不比Windows提供的更好。我意识到

有更好的选择，但我想知道OP

是否有一些特殊要求或只是假设一个

加密数据库本质上比非加密数据库更安全。


-

David Portas

SQL Server MVP

-

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

解决方案

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

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

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

这篇关于在数据列中搜索加密字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！