One of my clients wants me to perform a periodic "real" destruction of some of his old data, and I'm evaluating the best way to do it.
我的一位客户希望我定期“真正”破坏他的一些旧数据，我正在评估最好的方法。
The data is in a table, and I want to destroy some of the rows contained in it.
数据在表中，我想销毁其中包含的一些行。
I could do it manually by deleting/exporting the database on another computer/degaussing the hard drive/reimporting the saved data, but I need an automatic solution.
我可以手动删除/导出另一台计算机上的数据库/消磁硬盘/重新导入保存的数据，但我需要一个自动解决方案。
Is there an equivalent to the delete (as in delete * from foo) command which would perform a secure destruction of the data (using DoD secure wipe, or something like that?)
是否有一个等效的删除（如在delete * from foo中）命令可以执行数据的安全销毁（使用DoD安全擦除，或类似的东西？）
Do you see other ways to perform this automatic deletion?
您是否看到了执行此自动删除的其他方法？
Btw, I know the odds of someone retrieving some of the data I've destroyed using the sql delete command are very small, but some of my clients require it. So please don't turn this question into a global debate on the topic of data disposal procedures !
顺便说一句，我知道有人检索我使用sql delete命令销毁的一些数据的可能性非常小，但我的一些客户需要它。所以请不要把这个问题变成关于数据处理程序主题的全球辩论！
Edit : the problem I want to address is not "How should I destroy the data so it cannot be recovered" but rather "How can I convince my clients that their data cannot be recovered".
编辑：我要解决的问题不是“我应该如何销毁数据以使其无法恢复”，而是“如何说服我的客户说他们的数据无法恢复”。
7 个解决方案
#1
7
Use some form of encryption to store the data fields in the table.
使用某种形式的加密来存储表中的数据字段。
When you decide to "delete", re-encrypt the data you will continue to use with a new key. Discard the old key, and delete the rows encrypted with the old key. Shrink.
当您决定“删除”时，重新加密您将继续使用新密钥的数据。丢弃旧密钥，并删除使用旧密钥加密的行。收缩。
Even if someone recovers the rows, w/o the old key no one will be able to restore the data. Just make sure the old key is really discarded - you can have it on a single usb stick only, and destroy the stick, etc.
即使有人恢复了行，没有旧密钥，也没有人能够恢复数据。只要确保旧钥匙真的丢弃了 - 你只能把它放在一根usb棒上，然后摧毁它等等。
#2
7
From Books Online:
来自联机丛书：
Delete operations from a table or update operations that cause a row to move can immediately free up space on a page by removing references to the row. However, under certain circumstances, the row can physically remain on the data page as a ghost record. Ghost records are periodically removed by a background process. This residual data is not returned by the Database Engine in response to queries. However, in environments in which the physical security of the data or backup files is at risk, you can use
sp_clean_db_free_spaceto clean these ghost records.从表中删除操作或导致行移动的更新操作可以通过删除对该行的引用来立即释放页面上的空间。但是，在某些情况下，该行可以作为虚假记录物理地保留在数据页面上。后台进程会定期删除Ghost记录。数据库引擎不会返回此残差数据以响应查询。但是，在数据或备份文件的物理安全性存在风险的环境中，可以使用sp_clean_db_free_space清除这些ghost记录。
This should zero-out your "free" data pages. It can also be used if Instant Initialization was used, but you decided you want to zero-out pages instead.
这应该将您的“免费”数据页面清零。如果使用了即时初始化，也可以使用它，但您决定要将页面清零。
To answer your updated question, "How can I convince my clients that their data cannot be recovered", that BOL entry states it clearly, "Ghost records are periodically removed by a background process."
为了回答您更新的问题，“我如何说服我的客户说他们的数据无法恢复”，BOL条目清楚地说明了“Ghost记录会被后台进程定期删除。”
#3
3
Basically, no. The standard operation won't do it, and if it did the data could still be reconstructed from transaction logs etc. Probably the closest you can come is to do it externally, copying and purging the database to another device, then doing a high-quality scrub delete on the old device, but as a security guy I'm not sure I'd even want to say that was a sssured delette.
基本上没有。标准操作不会这样做，如果确实如此，数据仍然可以从事务日志等重建。可能最接近的是外部操作，复制和清除数据库到另一个设备，然后做一个高 - 旧设备上的质量擦除删除，但作为一个安全人员我不确定我甚至想说这是一个sssured delette。
Secure delete is a difficult problem. You might do better with a cryptographic approach, like Radia Perlman's "ephemerizer".
安全删除是一个难题。使用加密方法可能会做得更好，比如Radia Perlman的“ephemerizer”。
#4
1
I am not sure if this meets the requirments of the DOD, but at a minimum I would be going through the following.
我不确定这是否符合国防部的要求，但至少我将通过以下方式。
- Delete the records the standard way
- 以标准方式删除记录
- Take a new backup of the database (for future use)
- 对数据库进行新备份（以备将来使用）
- Delete all existing backups (As they have the data), using a standard file deletion process that meets the standards
- 使用符合标准的标准文件删除过程删除所有现有备份（因为它们具有数据）
- Shrink the database to free-up the unused space from the deleted records.
- 缩小数据库以从已删除的记录中释放未使用的空间。
I think this will get you pretty close, the key though is the management of the shrink operation, which I am not 100% sure how that clears/handles data. Secondly, removing the old backups would be the "biggest risk" if you were looking at risk points in my opinion.
我认为这会让你非常接近，关键是收缩操作的管理，我不是100％确定如何清除/处理数据。其次，如果你在我看来考虑风险点，删除旧备份将是“最大的风险”。
#5
1
Actually, chances of retrieving the data destroyed with DELETE are quite big, close to 100% :)
实际上，检索使用DELETE销毁的数据的可能性非常大，接近100％:)
Data that you delete are kept in the transaction log, it's a part of how the transactions work. In other case, you either would not be able to ROLLBACK a transaction, or a COMMIT would take forever (like in old versions of PostgreSQL).
您删除的数据保存在事务日志中，它是事务工作方式的一部分。在其他情况下，您要么无法ROLLBACK事务，要么COMMIT将永远（如在旧版本的PostgreSQL中）。
Best you can do without messing with the datafiles is:
在不弄乱数据文件的情况下，您可以做的最好的事情是：
- Delete your data.
- Perform multiple
UPDATEs on the table to destroy old data. - 在表上执行多个UPDATE以销毁旧数据。
- Perform several large transactions and commit them for the trasaction log to be truncated. How many exactly depends on your log size.
- 执行几个大型事务并提交它们以截断trasaction日志。多少完全取决于您的日志大小。
-
CleanSweepspace on disk occupied by old transaction logs. - CleanSweep由旧事务日志占用的磁盘空间。
- Perform multiple
- 删除您的数据。在表上执行多个UPDATE以销毁旧数据。执行几个大型事务并提交它们以截断trasaction日志。多少完全取决于您的日志大小。 CleanSweep由旧事务日志占用的磁盘空间。
#6
1
Delete the data. Do a simple backup and restore on a new hard drive and burn the old drive.
删除数据。在新硬盘驱动器上执行简单备份和还原并刻录旧驱动器。
Destroying objects is the only way to really convince people that 'things' are really gone.
摧毁物体是真正让人们相信'事物'真的消失的唯一方法。
#7
0
Well, I'm just playing here but you try this, it will be reasonably secure.
好吧，我只是在这里玩，但你试试这个，它会相当安全。
Don't use a typical backup.
不要使用典型的备份。
Script out the schema, if you haven't already.
如果您还没有，请编写模式。
Script out all the data so that all the current can be inserted with an script with many INSERT statements. The deleted data won't show up in this file, obviously. Of course, you will want to use Bulk Insert and all that to get the data back in there.
编写所有数据的脚本，以便可以使用包含许多INSERT语句的脚本插入所有当前数据。显然，删除的数据不会显示在此文件中。当然，您将需要使用批量插入和所有这些来获取数据。
Now use sdelete to delete all the data files and logs associated with the database. Now, restore from the insert script. :)
现在使用sdelete删除与数据库关联的所有数据文件和日志。现在，从插入脚本恢复。 :)
By the way, your question and the edit you made, saying you don't want a solution but a reason why not to contradicts your whole question. Anyway, a good reason no to do it is that no one is doing it. If you want to do something in computing (other than creating some brand new sort of application or something like that) that no one else is doing, it is probably a bad idea. There are no academic or DoD papers to my knowledge that describe a method to do this.
顺便说一句，你的问题和你做的编辑，说你不想要一个解决方案，但有理由不与你的整个问题相矛盾。无论如何，不这样做的一个很好的理由是没有人这样做。如果你想在计算中做一些事情（除了创建一些全新的应用程序或类似的东西），这是其他人没有做的，这可能是一个坏主意。据我所知，没有学术论文或国防部论文描述了这样做的方法。
The bigger problem is what information will be "leaked" from the records you deleted to records that were not deleted. Note, here I mean "leak" in the sense of information flow.
更大的问题是什么信息将从您删除的记录“泄露”到未删除的记录。注意，这里我指的是信息流意义上的“泄漏”。
Although, to be honest, the method I outlined above would essentially accomplish your goal.
虽然，老实说，我上面概述的方法基本上可以实现你的目标。
#1
7
Use some form of encryption to store the data fields in the table.
使用某种形式的加密来存储表中的数据字段。
When you decide to "delete", re-encrypt the data you will continue to use with a new key. Discard the old key, and delete the rows encrypted with the old key. Shrink.
当您决定“删除”时，重新加密您将继续使用新密钥的数据。丢弃旧密钥，并删除使用旧密钥加密的行。收缩。
Even if someone recovers the rows, w/o the old key no one will be able to restore the data. Just make sure the old key is really discarded - you can have it on a single usb stick only, and destroy the stick, etc.
即使有人恢复了行，没有旧密钥，也没有人能够恢复数据。只要确保旧钥匙真的丢弃了 - 你只能把它放在一根usb棒上，然后摧毁它等等。
#2
7
From Books Online:
来自联机丛书：
Delete operations from a table or update operations that cause a row to move can immediately free up space on a page by removing references to the row. However, under certain circumstances, the row can physically remain on the data page as a ghost record. Ghost records are periodically removed by a background process. This residual data is not returned by the Database Engine in response to queries. However, in environments in which the physical security of the data or backup files is at risk, you can use
sp_clean_db_free_spaceto clean these ghost records.从表中删除操作或导致行移动的更新操作可以通过删除对该行的引用来立即释放页面上的空间。但是，在某些情况下，该行可以作为虚假记录物理地保留在数据页面上。后台进程会定期删除Ghost记录。数据库引擎不会返回此残差数据以响应查询。但是，在数据或备份文件的物理安全性存在风险的环境中，可以使用sp_clean_db_free_space清除这些ghost记录。
This should zero-out your "free" data pages. It can also be used if Instant Initialization was used, but you decided you want to zero-out pages instead.
这应该将您的“免费”数据页面清零。如果使用了即时初始化，也可以使用它，但您决定要将页面清零。
To answer your updated question, "How can I convince my clients that their data cannot be recovered", that BOL entry states it clearly, "Ghost records are periodically removed by a background process."
为了回答您更新的问题，“我如何说服我的客户说他们的数据无法恢复”，BOL条目清楚地说明了“Ghost记录会被后台进程定期删除。”
#3
3
Basically, no. The standard operation won't do it, and if it did the data could still be reconstructed from transaction logs etc. Probably the closest you can come is to do it externally, copying and purging the database to another device, then doing a high-quality scrub delete on the old device, but as a security guy I'm not sure I'd even want to say that was a sssured delette.
基本上没有。标准操作不会这样做，如果确实如此，数据仍然可以从事务日志等重建。可能最接近的是外部操作，复制和清除数据库到另一个设备，然后做一个高 - 旧设备上的质量擦除删除，但作为一个安全人员我不确定我甚至想说这是一个sssured delette。
Secure delete is a difficult problem. You might do better with a cryptographic approach, like Radia Perlman's "ephemerizer".
安全删除是一个难题。使用加密方法可能会做得更好，比如Radia Perlman的“ephemerizer”。
#4
1
I am not sure if this meets the requirments of the DOD, but at a minimum I would be going through the following.
我不确定这是否符合国防部的要求，但至少我将通过以下方式。
- Delete the records the standard way
- 以标准方式删除记录
- Take a new backup of the database (for future use)
- 对数据库进行新备份（以备将来使用）
- Delete all existing backups (As they have the data), using a standard file deletion process that meets the standards
- 使用符合标准的标准文件删除过程删除所有现有备份（因为它们具有数据）
- Shrink the database to free-up the unused space from the deleted records.
- 缩小数据库以从已删除的记录中释放未使用的空间。
I think this will get you pretty close, the key though is the management of the shrink operation, which I am not 100% sure how that clears/handles data. Secondly, removing the old backups would be the "biggest risk" if you were looking at risk points in my opinion.
我认为这会让你非常接近，关键是收缩操作的管理，我不是100％确定如何清除/处理数据。其次，如果你在我看来考虑风险点，删除旧备份将是“最大的风险”。
#5
1
Actually, chances of retrieving the data destroyed with DELETE are quite big, close to 100% :)
实际上，检索使用DELETE销毁的数据的可能性非常大，接近100％:)
Data that you delete are kept in the transaction log, it's a part of how the transactions work. In other case, you either would not be able to ROLLBACK a transaction, or a COMMIT would take forever (like in old versions of PostgreSQL).
您删除的数据保存在事务日志中，它是事务工作方式的一部分。在其他情况下，您要么无法ROLLBACK事务，要么COMMIT将永远（如在旧版本的PostgreSQL中）。
Best you can do without messing with the datafiles is:
在不弄乱数据文件的情况下，您可以做的最好的事情是：
- Delete your data.
- Perform multiple
UPDATEs on the table to destroy old data. - 在表上执行多个UPDATE以销毁旧数据。
- Perform several large transactions and commit them for the trasaction log to be truncated. How many exactly depends on your log size.
- 执行几个大型事务并提交它们以截断trasaction日志。多少完全取决于您的日志大小。
-
CleanSweepspace on disk occupied by old transaction logs. - CleanSweep由旧事务日志占用的磁盘空间。
- Perform multiple
- 删除您的数据。在表上执行多个UPDATE以销毁旧数据。执行几个大型事务并提交它们以截断trasaction日志。多少完全取决于您的日志大小。 CleanSweep由旧事务日志占用的磁盘空间。
#6
1
Delete the data. Do a simple backup and restore on a new hard drive and burn the old drive.
删除数据。在新硬盘驱动器上执行简单备份和还原并刻录旧驱动器。
Destroying objects is the only way to really convince people that 'things' are really gone.
摧毁物体是真正让人们相信'事物'真的消失的唯一方法。
#7
0
Well, I'm just playing here but you try this, it will be reasonably secure.
好吧，我只是在这里玩，但你试试这个，它会相当安全。
Don't use a typical backup.
不要使用典型的备份。
Script out the schema, if you haven't already.
如果您还没有，请编写模式。
Script out all the data so that all the current can be inserted with an script with many INSERT statements. The deleted data won't show up in this file, obviously. Of course, you will want to use Bulk Insert and all that to get the data back in there.
编写所有数据的脚本，以便可以使用包含许多INSERT语句的脚本插入所有当前数据。显然，删除的数据不会显示在此文件中。当然，您将需要使用批量插入和所有这些来获取数据。
Now use sdelete to delete all the data files and logs associated with the database. Now, restore from the insert script. :)
现在使用sdelete删除与数据库关联的所有数据文件和日志。现在，从插入脚本恢复。 :)
By the way, your question and the edit you made, saying you don't want a solution but a reason why not to contradicts your whole question. Anyway, a good reason no to do it is that no one is doing it. If you want to do something in computing (other than creating some brand new sort of application or something like that) that no one else is doing, it is probably a bad idea. There are no academic or DoD papers to my knowledge that describe a method to do this.
顺便说一句，你的问题和你做的编辑，说你不想要一个解决方案，但有理由不与你的整个问题相矛盾。无论如何，不这样做的一个很好的理由是没有人这样做。如果你想在计算中做一些事情（除了创建一些全新的应用程序或类似的东西），这是其他人没有做的，这可能是一个坏主意。据我所知，没有学术论文或国防部论文描述了这样做的方法。
The bigger problem is what information will be "leaked" from the records you deleted to records that were not deleted. Note, here I mean "leak" in the sense of information flow.
更大的问题是什么信息将从您删除的记录“泄露”到未删除的记录。注意，这里我指的是信息流意义上的“泄漏”。
Although, to be honest, the method I outlined above would essentially accomplish your goal.
虽然，老实说，我上面概述的方法基本上可以实现你的目标。