RE: Overwriting/deleting blobs
Date: Tue, 26 Feb 2013 10:55:52 -0600
Message-ID: <OFA3211F72.64318C0B-ON86257B1E.005C555D-86257B1E.005D01F1_at_oshkoshcorp.com>
So, after more research and information (information was limited due to the security requirements - I can tell you but I'd have to kill you ;-) ). The column ended up being an Long Raw. Still difficult to deal with but not quite as difficult (yup, Long Raw on an 11g database). So here's what I did to "overwrite" the Long Raws:
1. App/IT - identify rows with bad data 2. DBA - delete rows 3. DBA - create script to recreate data, index and temp tablespaces 4. DBA - defer backups 5. DBA - export data 6. DBA - drop tablespaces without dropping datafiles 7. OS Admin - overwrite the files, then delete them 8. DBA - recreate the data, index and temp tablespaces 9. DBA - import the data
10. App/IT - validate the data
11. OS Admin/Storage(?) - overwrite all archivelogs and backups, then delete them
12. DBA - take new full backup of database
WGB From: "Mark W. Farnham" <mwf_at_rsiz.com> To: <jonathan_at_jlcomp.demon.co.uk>, <oracle-l_at_freelists.org> Date: 02/23/2013 07:42 AM
Subject: RE: Overwriting/deleting blobs Sent by: oracle-l-bounce_at_freelists.org
JL's on target as usual.
I have a slightly different perspective, but ONLY IF the following assumptions are true:
- You have to keep everything relevant from some point in time (accounting date, perhaps) until some statutory time limit past the year, for example, 7 full fiscal years for a lot of stuff, or 25 years after project closure for EPA projects.
- You are in a jurisdiction where legal discovery in a court case requires production of everything relevant you have, whether or not you are still legally required to still have it by the government.
IF that is the case, then we can view the problem as legitimate AND as a
time phased aggregate problem rather than individual blobs. That problem
is
tractable by using time based partitions in their own tablespace/file by
partition. Then, at the time you are required to obfuscate you can
partition
exchange or drop partition. With the tablespace thus empty, you can drop
the
partition and use OS level obfuscation software to eradicate the contents
of
the file on disk.
Of course this will still leave all your backups around. Depending on the
jurisdiction you *MAY* have to retrieve backups if you can. Chasing down
all
backup copies for destruction is a tedious problem, but it can be done as
well *IF* you know where they all are.
I really don't know if this was the intent of your security department, or whether they are trying to obscure drawings related to something that was originally planned to be a patent that is now intended to be kept a trade secret. Or something completely different. I can only hope they are not attempting to corrupt the audit trail. If you suspect they are, watch your six.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Saturday, February 23, 2013 6:02 AM
To: oracle-l_at_freelists.org
Subject: Re: Overwriting/deleting blobs
WGB, I think you need to get your security department to explain exactly what they would like the database to achieve, but I think the answer is
- when you update a blob the data for the old blob is still in the file until some unspecified future time - it's just a little harder to get at
- that's the way it works with all the other data in the database, though the specific mechanism are different, so why are they worrying about blobs in particular
Of course there's always the reverse question - why is your security department so keen to destroy the audit trail ? ;)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: <wblanchard_at_oshkoshcorp.com> To: <oracle-l_at_freelists.org> Sent: Saturday, February 23, 2013 4:36 AM Subject: Overwriting/deleting blobs
|
| Greetings,
|
| I've been asked by our security department if there's a way to overwrite
a
| blob in the database but I don't know of any way to do it. I asked my
| trusted coworker Google if there was a way to do it but he didn't have
any
| good ideas either. I would love to ask Oracle but this app didn't think
| purchasing support from Oracle was important. So, I come to the most
| knowledgable DBAs in the world to ask for help.
|
| Something tells me that it's not just as easy as inserting a new blob in
| the table as I would guess that it would have to be the exact same size
as
| the current blob. Also, could I guarantee that it would be placed in
the
| exact same blocks/segments that the current blob is in.
|
| So, is there a way to "securely" overwrite/delete the current blob? What
| are my options, if any.
|
|
| Thank you,
|
| WGB
|
|
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l Although this e-mail and any attachments are believed to be free of any virus or other defect which might affect any computer system, it is the responsibility of the recipient to check that it is virus-free and the sender accepts no responsibility or liability for any loss, injury, damage, cost or expense arising in any way from receipt or use thereof by the recipient. The information contained in this electronic mail message is confidential information and intended only for the use of the individual or entity named above, and may be privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this transmission in error, please contact the sender immediately, delete this material from your computer and destroy all related paper media. Please note that the documents transmitted are not intended to be binding until a hard copy has been manually signed by all parties. Thank you. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 26 2013 - 17:55:52 CET