MySQL加密列 [英] MySQL encrypted columns
问题描述
如何使用InnoDB对一列数据进行加密，以便除了用户的数据之外，还可以读取数据？我正在考虑像使用一个MySQL加密功能（比如说AES）和基于从用户密码计算的哈希值的键。
有没有任何一个指向我该怎么做？我在正确的轨道上吗？
以下答案之一
修改用户密码的问题涉及通过新密码对用户密钥进行重新加密，这比重新加密可能任意大的用户数据的数量要简单得多。用户密钥在系统中的用户数据的整个使用寿命内保持不变。
这有什么帮助？说密码是pass1。并且有一堆用这个生成的密钥加密的记录。如果用户现在将密码重置为pass2，我无法解密使用pass1加密的数据。在用户完全忘记密码的情况下，他的所有加密数据将丢失。
我不知道如果您将哈希本身保留在数据库中，则用户密码哈希数据加密，尤其是。在这种情况下，任何可以访问加密数据的人也可以访问密码散列并解密数据。
另一种方法是使用应用程序特定的密钥加密数据用一些用户特定的数据进行盐化。但是，您面临另一个问题：如何安全地存储应用程序密钥。对于这个问题，我不知道一个简单的答案，但如果您担心数据库数据可能会受到损害，而不是源代码本身，则将其保留在源代码中可能已经足够好了。如果您的数据库存储在异地存储（请参阅Amazon S3）。
如果您只在数据库中保留密码的哈希值，则可以使用用户密码清除应用密钥，但可以引入另一个安全漏洞：您必须在应用程序会话中保持明文的用户密码。
至于技术解决方案，这很简单，示例代码可用。您可以按照以下方式对其进行修改，使用密码哈希密码的应用密码加密数据：
INSERT INTO secure_table VALUES（
1，
AES_ENCRYPT（
'纯文本数据'，
CONCAT（@application_password，@user_password））
）;
无论如何，您必须将应用密码存储在某个地方，所以我不认为有一种简单的方法可以提供完美的安全性。
我可以想到的另一种方法是询问用户可以用作加密密钥的短PIN。 PIN不会存储在数据库中，但是您每次访问数据时都需要向用户询问。
当然你必须考虑的是加密的可行性。您无法索引或搜索它没有解密。可能需要一些有限的数据（例如信用卡号），但是我不会去那么远。
Say each row in a table has data pertaining to one particular user. The user has a password to access the system.
How do I encrypt a column of data using InnoDB so that no one other than the user who's data it is can read the data ? I was thinking of something like using one of the MySQL encryption functions (say AES) with a key based on a hash calculated from the user's password.
Does any one have any pointers to how I could do this ? Am I on the right track ?
One of the answers below
The issue of modifying user's password involves re-encrypting the user key by means of the new password which is much more straight forward than re-encrypting the whole bunch of user's data that can be arbitrarily large. The user key remains the same accross the life of the user data in the system.
How does this help ? Say the password is pass1. And there are a bunch of records encrypted with a key generated from this. If the user now resets the password to pass2, I have no way of decrypting the data that was encrypted using pass1. In the case of a user forgetting the password entirely, all his encrypted data will be lost.
I don't know if there is much sense in encrypting data with user's password hash, especially if you keep hash itself in the database. In that case anyone who can access the encrypted data can also access the password hash and decrypt the data.
Another approach would be to encrypt the data with the application-specific key salted with some user-specific data. However, then you face another problem: how to securely store the application key. To that question I do not know an easy answer, but keeping it in your source code is probably good enough if you fear that your database data can be compromised, but not the source code itself, e.g. if your database is stored off-site (think Amazon S3).
Salting the app key with the user's password helps if you keep only password's hash in the database, but can introduce another security flaw: you have to keep user's password in clear text in the applications session.
As for technical solution, it is quite simple and sample code is available. You could modify it as follows to encrypt the data with the application password salted with password hash:
INSERT INTO secure_table VALUES (
1,
AES_ENCRYPT(
'plain text data',
CONCAT(@application_password, @user_password))
);
In any case you would have to store your application password somewhere so I don't think that there is an easy approach that provides perfect security.
Another approach I can think of is to ask user for a short PIN which you could use as an encryption key. The PIN would not be stored in the database, but you would need to ask user for it every time you access their data.
And of course your have to think of is the feasibility of the encryption. You won't be able to index or to search it without decryption. It is probably required for a limited set of data (e.g. credit card number), but I wouldn't go to far with it.
这篇关于MySQL加密列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
