I am working on a project which has a client requirement that data once deleted from the tables should not be recovered using any method from the hard disk. This is most important for him as the data is very critical.I am using Oracle 10g express edition. My question is that, is data recoverable by any means after deleting it from the table or not. If not then its good for me else can anyone suggest me the way through which it can be permanently deleted from the hard disk.
The requirement doesn't make a lot of sense.
The whole point of a relational database is recoverability. Data is available in backups. Data is available in UNDO. Data is available in REDO. Plus, the data is still almost certainly available in the data file until new data is inserted into the same place in the same data block that the data was deleted from.
In theory, I suppose, you could do something ridiculous like disable backups (so that you lose all your data if something goes wrong with the database). When you want to permanently remove data from a table, you'd have to create a new tablespace with a new set of data files, copy all the objects (and whatever data you want to retain) that you want to retain to the new tablespace, drop the old tablespace, and then use some sort of commercial file shredder to shred the old data files (which basically means writing a ton of random data to the file before deleting it). This would be quite time consuming and you'd realistically need system downtime for it. And you'd still have the deleted data in UNDO for a while.
When you have sensitive data, the proper approach is to encrypt it. That ensures that the data is unavailable to anyone that doesn't have the encryption key. Presumably, the data doesn't magically become sensitive only at the moment it is deleted. So it should have been encrypted all along. The best way to do this would be to use Transparent Data Encryption which would require the Enterprise Edition and the Advanced Security Option. If you're really stuck with the express edition, you could potentially use self-encrypting hard drives for your data files. Or you could use the DBMS_CRYPTO package to encrypt the data. But then you'd need to build the infrastructure to manage the keys which is almost always where people make mistakes.
you will be required to use external tools, for example check http://en.wikipedia.org/wiki/Data_erasure
you can learn from the DoD (an organization keen on keeping their secrets) also:
especially the method "m. Destroy - Disintegrate, incinerate, pulverize, shred, or melt." seems pretty secure.
Another approach is to use Transparent Data Encryption, since even if the data is lost, it will be unrecoverable without the wallet / encryption key.
Harm ten Napel
Edited by: hnapel on Mar 11, 2013 8:03 AM
Edited by: hnapel on Mar 11, 2013 8:05 AM