静态加密和/或 AES_ENCRYPT [英] Encryption at rest and/or AES_ENCRYPT
问题描述
我正在尝试提高包含敏感数据的 MySQL 数据库的安全性.我正在努力掌握一些术语.有人可以让我知道我是否正确理解了情况:
I am trying to improve the security a a MySQL database that will contain sensitive data. I am struggling to get to grips with some terminology. Can somebody let me know if I have understood the situation correctly:
静态加密 - 似乎我可以在表级别启用此功能.表中的所有数据都使用密钥加密.如果有人掌握了备份文件或获得了对服务器的物理访问权限，那么数据将受到保护.当然，这假设密钥存储在其他地方.
Encryption at rest - it seems like I can enable this on a table level. All data in the table in encrypted using a key. If somebody got hold of a backup file or gained physical access to the server then the data would be protected. This assumes, of course, that the key is stored elsewhere.
AES_ENCRYPT - 在我的表中插入/更新数据时，我可以使用 AES_ENCRYPT('data', 'password').通过 SELECT 查询数据时，我使用 AES_DECRYPT
AES_ENCRYPT - when inserting/updating data into my table I can use AES_ENCRYPT('data', 'password'). When querying the data via a SELECT I use AES_DECRYPT
假设我只是使用静态加密，那么我是否需要在我的 PHP 代码中做任何不同的事情来查询数据?我的 PHP 代码是否需要通过我的 PDO 请求将密钥发送到数据库?或者我可以使用我的普通代码查询数据库并自动处理解密吗?
Assuming I was just using encryption at rest then do I need to do anything different in my PHP code to query the data? Does my PHP code need to send the key to the database via my PDO request? Or can I use my normal code for querying the database and the decryption is handled automatically?
或者我误解了静态加密的作用，我需要使用 AES_ENCRYPT 代替/以及
Or have I misunderstood what encryption at rest does and I need to use AES_ENCRYPT instead/as well
推荐答案
静态加密
静态加密是数据库中未使用/访问或更新时的数据.移动中的加密类似于 TLS，其中数据(来自数据库)从服务器传输到服务器到浏览器，再到服务器，再到浏览器等.TLS 是完美的如果处理得当并且以一种您需要做的不仅仅是最低限度的事情来真正使其真正安全，那么在大多数情况下都是好的.
Encryption at rest
Encryption at rest is the data in the database when it is not being used/accessed or updated. Encryption on the move is things like TLS where the data (from the database) is transported from server to server to browser, to server, to browser, etc. TLS is perfectly good in most situations if it's handled carefully and approached with an attitude that you need to do more than the bare minimum to actually make it realisitically secure.
一个典型的例子是，人们将 LetsEncrypt 的 TLS 证书放在他们的域上，突然认为他们所有的东西都是安全的；但是他们不加密他们的会话或他们的 cookie，因此在他们的防御中留下了巨大的潜在漏洞.
A typical example is people put on a TLS certificate from LetsEncrypt on their domain and think that suddenly all their stuff is safe; but they don't encrypt their sessions or their cookies so leaving a massive potential hole in their defences.
我再怎么强调也不为过；MySQL 内置的加密系统不适合实际的安全数据保护.
I can not stress this enough; the built in encryption system in MySQL is not suitable for actual secure data protection.
请阅读我在这里对一个非常相似的问题的回答以了解详细信息(我不想要只需复制/粘贴).
Please read my answer to a very similar question here as to the details (I don't want to simply copy/paste).
好的，那么，因为你坚持......在这里:
Ok, then, because you insist.... here:
我一直理解不要使用 MySQL 的内置加密功能，因为静态数据加密(在 SQL 中)的要点是，如果服务器受到威胁，则数据不在 [一样多]风险.
I have always understood NOT TO USE MySQL's built in encryption fuctionality because the point of encryption of data at rest (in the SQL) is that if the server is compromised, the data is not at [as much] risk.
MySQL 内置功能的问题在于它不适用于数据传入和传出at rest"的情况.状态，因此任何数据的明文都可以记录在 MySQL 日志中(以及存储系统上的其他地方，例如查询查找未加密，因此您可以从众多查找及其 count 结果中推导出列值)之前/因为它被加密.您可以在此处阅读更多相关信息.
The problem with the MySQL built in functionality is that it doesn't apply to when the data is passed to and from the "at rest" state, so the plaintext of any data can be recorded in MySQL logs (and elsewhere on the storage system, such as query lookups are not encrypted so you can from numerous lookups and their count results deduce column values) before/as it is encrypted. You can read more about this here.
关于加密，您应该使用一些久经考验的库，例如 defuse/php-encryption.
Regarding encryption, you should use some tried and tested library like defuse/php-encryption.
根据我在自己对这个主题的研究中读到的内容，Magnus 提供的链接到 defuse/php-encryption 是防止 MySQL 导致您破坏数据的最佳方法之一，永远不要让 MySQL 程序/服务器看到您数据的明文值.
From what I've read in my own research on this topic, the link provided by Magnus to defuse/php-encryption is one of the best ways of preventing MySQL ever causing you to compromise your data, by never letting the MySQL program/server ever see the plaintext value of your data.
-- 2017 年 5 月 7 日发布的答案.
-- Answer as posted May 7th 2017.
此外，Bill Karwin 对同一问题的回答 提供了一些有价值的额外见解:
Also Bill Karwin's answer to the same question gives some valuable additional insights:
+1 对 Martin 的回答，但我会添加一些关于它的价值的信息.
+1 to Martin's answer, but I'll add some info for what it's worth.
MySQL 5.7 为 InnoDB 表空间实现了静态加密(https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html).
MySQL 5.7 has implemented encryption at rest for InnoDB tablespaces (https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html).
据报道，MySQL 8.0 还将为 InnoDB 重做日志和撤消日志文件实现静态加密(https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html).
MySQL 8.0 will reportedly also implement encryption at rest for InnoDB redo log and undo log files (https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html).
这仍然保留未加密的查询日志和二进制日志.为此，我们将不得不等待 MySQL 的某个未来版本.
This still leaves unencrypted the query logs and the binary log. We'll have to wait for some future version of MySQL for that.
为什么需要这么长时间?MySQL 的安全工程负责人在上个月 [2017 年 4 月] Percona Live 会议上的一次鸟类会议上表示，他们非常谨慎地实施加密权利.这意味着要实现加密功能，还要实现密钥安全和密钥轮换以及其他用途.要做到这一点非常复杂，而且他们不想实施一些会被弃用并使每个人的加密数据库无效的东西.
Why does it take so long? The head of the security engineering for MySQL said at a bird-of-feather session at the Percona Live conference last month [April 2017] that they are being very careful to implement encryption right. This means implementing features for encryption, but also key security and key rotation, and other usage. It's very complex to get this right, and they don't want to implement something that will become deprecated and make everyone's encrypted databases invalid.
-- 2017 年 5 月 7 日发布的答案.
-- Answer as posted May 7th 2017.
安全性很复杂.如果你想正确地做这件事并对你的洋葱皮有信心，那么你需要做很多事情(见下面的项目符号)；但您需要做的第一件事是:
Security is complex. If you want to do it properly and have a confidence in your protective onion skins then you need to do a lot of things (see bullets below); but the first thing you need to do is:
定义您要保护的对象
说真的.对于想要窃取您的明文名称和地址的人、想要接管您的服务器的人以及仅仅因为只是想破坏数据的人，您需要不同的策略.您可以一直保护自己免受所有人侵害，这是一个神话，从概念上讲，这是不可能的*；所以你需要定义最有可能的攻击者，然后找出如何最好地缓解他们的进步.
Seriously. You need different strategies against someone who wants to steal your plaintext names and addresses versus someone who wants to take over your server versus someone who simply wants to trash the data just because. It is a myth that you can protect against everyone all of the time, by concept this is impossible*; so you need to define the most likely agressors and then work out how best to mitigate their advances.
具体到 MySQL，一些明确的建议:
Sepcifically to MySQL, some clear recommendations:
将 SQL 和 PHP 保持在同一台服务器上.不要远程访问 MySQL 数据.
Keep the SQL and the PHP on the same server. Do not remote access to the MySQL data.
排除对 SQL 的外部访问(因此只有 localhost)
Exclude external access to the SQL (so it's localhost only)
混淆你的表名和列名；如果有人闯入您的数据并且您在 username 列下有 HDTBJ^BTUETHNUYT 那么他们知道这个乱码可能是一个用户名，因此他们有一个很好的开始尝试破解你的加密.
Obfuscate your table names and column names; if someone break into your data and you have HDTBJ^BTUETHNUYT under the column username then they know that this garble is probably a username so they have a very good start in trying to break your encryption.
重要:真正锁定您的表访问权限；设置大量 MySQL 用户，每个用户都只有最低限度的权限来做他们需要的事情；您希望用户阅读表格(仅)并且仅阅读某些表格；用户写入某些表但无权访问其他表.如果 MySQL 上的任何一个用户受到威胁，这是一个令人担忧的问题；您不会自动丢失其中的所有数据.
IMPORTANT: Really lock down your table access; set up lots of MySQL users, each with only the bare minimum privilieges to do what they need; you want a user to read the table (only) and only read certain tables; users to write to certain tables but have no access to other tables. It's seperation of concern so that if any one user on the MySQL is compromised; you've not automatically lost every piece of data in there.
使用 PHP 加密服务.将加密密钥存储在一个完全独立的地方；例如，有另一台仅用于备份的服务器，您可以访问该服务器仅用于获取加密密钥，因此，如果您的 PHP/MySQL 服务器受到威胁，您有一些空间可以切断并锁定密钥服务器，这样您就可以限制伤害.如果密钥服务器也有备份，那么您实际上并没有受到太大的损害(视情况而定).
Use PHP encrpytion services . Store Encryption keys in a completely separate place; for example have another server you use solely for backup that you can access solely for reaching out to grab the encryption keys, therefore if your PHP/MySQL server is compromised you have some room to cut off and lock down the Key server so thay you can limit the damage. If the key server also has backups then really you're not too badly compromised (situation dependant).
设置大量观察者和电子邮件通知者，以准确告诉您某些进程何时运行以及哪些服务器用户(不是人，而是程序)在做什么.因此，您可以看到为什么在凌晨 5 点开始运行一个意外的进程来尝试测量 MySQL 表的大小.跆拳道?
Set up lots of watchers and email informers to tell you exactly when certain processes are running and which server users (not people but programs) are doing what. So you can see why an unexpected process starts to run at 5am to try and measure the size of the MySQL tables. WTF?
嗅探"MySQL AES_ENCRYPT 数据的可能性很大；即使它不在数据库中，但如果网站受到威胁(或者更糟的是，PHP 代码不安全)，那么定时攻击可以通过定时查询查找和数据包返回来计算数据内容.
There is a lot of potential to have your MySQL AES_ENCRYPT'ed data "sniffed" even if it is not at rest in the DB, but if the website gets compromised (or worse, the PHP code is insecure) then timing attacks can work out data contents by timing query lookups and data packet returns.
安全是一个黑洞；在某些时候，您会认为该死的，我已经做的够多了".没有人拥有完全的安全性，一些非常敬业的组织拥有足够的安全性.在走完这段距离之前，您需要确定自己愿意走多远.
Security is a black hole; at some point or another you're going to think "Sod this, I've done enough". No one ever has total security, some very dedicated organisations have enough security. You need to work out how far you're willing to walk before you've gone the distance.
* 为什么不可能?因为为了保护您的数据免受所有威胁，所有时间都需要不可读、不可用，就像散列一样.哈希始终受到保护，不受任何人的影响.但是哈希永远不能被取消哈希.
这篇关于静态加密和/或 AES_ENCRYPT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
