6 Replies Latest reply on Aug 27, 2014 6:31 AM by Hemant K Chitale

    Selective Shred of database deletes

    46e74b07-511d-4647-a368-82b9fc70d4ca

      The Problem:

      North Carolina has a public records law that makes just about anything a government agency produces a public record and subject to public records requests. The State Archives of North Carolina is tasked by the legislature to write records retention schedules for agencies so that they may destroy records once they have aged sufficiently, based on the value of the record.

       

      Any record that has not been destroyed is still subject to the law even if the retention period has been met. While it is easy enough to determine whether a paper record has been destroyed or not, the nature of database records makes the issue a little more difficult to pin down.

       

      The law in North Carolina calls for the following standard to be met.

      (b) When used in an approved records retention and disposition schedule, the provision that electronic records are to be

      destroyed means that the data and metadata are to be overwritten, deleted, and unlinked so the data and metadata may

      not be practicably reconstructed.

       

      The Question(s):

      Is a DELETE or DROP TABLE command sufficient to make the data “not practicably” reconstructed, or do we need to go further? 

                  How easy is it to retrieve a series of records removed from a database table using a DELETE command?

        • 1. Re: Selective Shred of database deletes
          Mike Kutz

          46e74b07-511d-4647-a368-82b9fc70d4ca wrote:

           

          The Question(s):

          Is a DELETE or DROP TABLE command sufficient to make the data “not practicably” reconstructed, or do we need to go further? 

                      How easy is it to retrieve a series of records removed from a database table using a DELETE command?

          What do you consider "practical"?

          Is a "Point In Time" restore of the entire database "practical"?  (personally, no)

          I believe the key thing is to make sure you document your definition of "practical".

           

          Read this:  Using Oracle Flashback Technology

          In short, for DELETE's, there exists a window where "recovery" is easy... but that window can be very small. (eg 1hr)

          Oracle technology exists that can extend that window (Flashback Data Archive)... but, the size of the window must be defined.

           

          For DROP TABLE, there is a RECYCLING BIN that you'll want to empty.

          The "recovery window" of this data is defined by the backup strategy of the database.

           

          MK

          • 2. Re: Selective Shred of database deletes
            Dan Jankowski

            Even after dropping a table and purging from the recyclebin, the data may remain on disk. As with OS file deletion, the data on disk is not overwritten, merely deallocated. A determined individual could read the blocks from disk and reconstruct the table data.

             

            Also bear in mind that backups, archived redo logs, audit trail, and trace files may also contain copies of your data.

             

            If I were being really paranoid, I would insist on physically deleting all database files, and performing disk-level secure deletion to ensure no fragments remain on disk. But I'm sure that's not practical for regular purging of records as it requires the destruction of the entire database.

            • 3. Re: Selective Shred of database deletes
              jgarry

              Tablespace restores are so simple, you should probably ask your chain of command what really needs to be done.

              • 4. Re: Selective Shred of database deletes
                Mark D Powell

                Dan has a point however I do not consider the data still being in unused blocks within the database files as being much of a risk.  In order to try to extract the data you need access to the server or directly to the SAN.  In any environment that uses dedicated database servers that is going to limit access.  You can further protect the data by using the extra-cost Tablespace transparent data encryption feature.  So even if someone gets access to the disk the data is encrypted.  Backups and exports can be encrypted through various means.

                - -

                OLS, Oracle label security may have a overwrite data on delete feature.

                - -

                The definition of practical should come from the legal department but generally any honest effort to remove qualifying data would probably be considered good enough.

                - -

                IMHO -- Mark D Powell --

                • 5. Re: Selective Shred of database deletes
                  Jonathan Lewis

                  I think the law needs to state what it means by "practicably". For a few thousand dollars you can get a utility that will scan a table and extract any deleted data that has not yet been over-written - knowing which tables to scan to extract and piece together the bits of "undeleted" data needed could be the harder bit, but that's just human time; the only question then is how long ago the data has been deleted and whether that delete would have led to block re-use.

                   

                  "Shredding" should allow for over-writing to cater for the requirements - and then the backups have to be destroyed after a time-limit.

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: Selective Shred of database deletes
                    Hemant K Chitale

                    The requirement, as I read it,is :

                    1.  FIRST  overwrite the data  (e.g. with junk strings / numbers / dates)  {Thus an SQL UPDATE of all the columns would overwrite the data ondisk}

                    2.  NEXT delete the rows

                    3.  THEN drop the "container" (e.g. the table or partition) that holds such rows

                     

                    However, neither of this prevents a point-in-time restore and recovery from backups.  So any restoration from a backup must be an "exception".

                     

                     

                    Hemant K Chitale