Db2 LUW 11.1.5 deleting sensitive data

Daniel Luksetich

Db2 LUW 11.1.5 deleting sensitive data
Hi Folks,

I have a question about purging sensitive data on Db2 for Windows. I want to
make sure that this data is gone for good and physically erased from the
DASD. It is NOT encrypted, and most is not compressed.

Thanks in advance!

Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Joe Geller

RE: Db2 LUW 11.1.5 deleting sensitive data
(in response to Daniel Luksetich)

Dan,

Not an answer, but some considerations.  There are actually several different situations (places where data may be purged (or not) from).

1) Deleting a row - the page (and index pages too) are in pages owned by Db2, so you get whatever Db2 does with the bytes of the deleted row and index entries.  I don't remember off hand exactly what that is in each case, but it should be easy to find out.

2) Dropping a table - or for a purge, detaching an old partition and then maybe dropping the detached table. In this case, it is whatever Db2 does within the tablespace to handle a Drop.

3) The log - a Delete of a row will have the old data logged.  The original insert and any updates will also be logged. I doubt if any purge process has taken that into consideration.

4) backups - same thing.

Joe

In Reply to Daniel Luksetich:

Hi Folks,

I have a question about purging sensitive data on Db2 for Windows. I want to
make sure that this data is gone for good and physically erased from the
DASD. It is NOT encrypted, and most is not compressed.

Thanks in advance!

Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





Roland Schock

RE: Db2 LUW 11.1.5 deleting sensitive data
(in response to Joe Geller)

Good question and great answer. From my perspective I'd like to add, that Db2 for LUW will keep the row data on the datapage (accessible by db2dart dump options) until a reorg of that table.

If you delete more rows or a full table, the data pages might still exist in the tablespace, even if maybe less accessible by db2dart then. Alter tablespace reduce max could help to return those data pages to the OS. They still might be untouched on disk then (accessible by NSA or disk imaging tools).

Data could be still in indexes, but would also be gone after a reorg.

Data in logs is a bit more difficult to retrieve, although not impossible. Different to Db2 for z, Db2 LUW has no official way to format log pages from the transaction log. All the APIs and docs are kept pretty private. But on the other hand, you could use the trial edition of Db2 Recovery Expert in AESE to generate undi scripts from the logs.

I guess, overwriting multiple times, incinerating the disk with napalm and cutting the disk drives in halves are not an option, but would be rather sufficient. This might be a nice read in case: https://www.theguardian.com/world/video/2014/jan/31/snowden-files-computer-destroyed-guardian-gchq-basement-video

Regards

Roland

Daniel Luksetich

Db2 LUW 11.1.5 deleting sensitive data
(in response to Roland Schock)
Wow! OK, I’ll try to be as filthy as possible when deleting the data. A data murderer.



Reminds me of when I “decommission” a hard drive at home. It eventually involves a log splitter and/or sledge hammer.



Thanks a lot!

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Roland Schock <[login to unmask email]>
Sent: Monday, May 18, 2020 1:27 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 LUW 11.1.5 deleting sensitive data



Good question and great answer. From my perspective I'd like to add, that Db2 for LUW will keep the row data on the datapage (accessible by db2dart dump options) until a reorg of that table.

If you delete more rows or a full table, the data pages might still exist in the tablespace, even if maybe less accessible by db2dart then. Alter tablespace reduce max could help to return those data pages to the OS. They still might be untouched on disk then (accessible by NSA or disk imaging tools).

Data could be still in indexes, but would also be gone after a reorg.

Data in logs is a bit more difficult to retrieve, although not impossible. Different to Db2 for z, Db2 LUW has no official way to format log pages from the transaction log. All the APIs and docs are kept pretty private. But on the other hand, you could use the trial edition of Db2 Recovery Expert in AESE to generate undi scripts from the logs.

I guess, overwriting multiple times, incinerating the disk with napalm and cutting the disk drives in halves are not an option, but would be rather sufficient. This might be a nice read in case: https://www.theguardian.com/world/video/2014/jan/31/snowden-files-computer-destroyed-guardian-gchq-basement-video

Regards

Roland



-----End Original Message-----

Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Isaac Yassin

Db2 LUW 11.1.5 deleting sensitive data
(in response to Daniel Luksetich)
We incinerate the drives

Isaac Yassin
Sent from my Note X

בתאריך יום ב׳, 18 במאי 2020, 21:33, מאת Daniel L Luksetich ‏<
[login to unmask email]>:

> Wow! OK, I’ll try to be as filthy as possible when deleting the data. A
> data murderer.
>
>
>
> Reminds me of when I “decommission” a hard drive at home. It eventually
> involves a log splitter and/or sledge hammer.
>
>
>
> Thanks a lot!
>
> Dan
>
>
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
> | Daniel L Luksetich | IBM Certified Advanced Database
> Administrator – |
>
> | IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
> Windows |
>
> | IDUG Content Committee Past-Chairman | IBM Certified Database
> Adminstrator – Db2 12 for z/OS |
>
> | IDUG DB2-L Administrator | IBM Certified System
> Administrator – Db2 11 for z/OS |
>
> | URL: https://db2expert.com | IBM Certified Application
> Developer – Db2 11 for z/OS |
>
>
> +--------------------------------------+-----------------------------------------------------------+
>
>
>
>
>
> *From:* Roland Schock <[login to unmask email]>
> *Sent:* Monday, May 18, 2020 1:27 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Db2 LUW 11.1.5 deleting sensitive data
>
>
>
> Good question and great answer. From my perspective I'd like to add, that
> Db2 for LUW will keep the row data on the datapage (accessible by db2dart
> dump options) until a reorg of that table.
>
> If you delete more rows or a full table, the data pages might still exist
> in the tablespace, even if maybe less accessible by db2dart then. Alter
> tablespace reduce max could help to return those data pages to the OS. They
> still might be untouched on disk then (accessible by NSA or disk imaging
> tools).
>
> Data could be still in indexes, but would also be gone after a reorg.
>
> Data in logs is a bit more difficult to retrieve, although not impossible.
> Different to Db2 for z, Db2 LUW has no official way to format log pages
> from the transaction log. All the APIs and docs are kept pretty private.
> But on the other hand, you could use the trial edition of Db2 Recovery
> Expert in AESE to generate undi scripts from the logs.
>
> I guess, overwriting multiple times, incinerating the disk with napalm and
> cutting the disk drives in halves are not an option, but would be rather
> sufficient. This might be a nice read in case:
> https://www.theguardian.com/world/video/2014/jan/31/snowden-files-computer-destroyed-guardian-gchq-basement-video
>
> Regards
>
> Roland
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)
  • image006.png (13.1k)

Roland Schock

RE: Db2 LUW 11.1.5 deleting sensitive data
(in response to Daniel Luksetich)

I apologize to have turned your honest question in such a battlefield now.

A very subversive method would be, to overwrite the data in such a way, that it still contains data but completely false information. Then drop them in the normal rubbish bin, lean back, have a beer and wait.

Roundabout 30+ years ago on my first computer (Sharp MZ-80B with Z80 CPU, 64KB RAM) I got a copy of CP/M and some text processor. While debugging some code I found a prefilled buffer area with the text "Nosey, aren't you".

 

So we are back on topic: Overwriting data with useless information but with the same length, so the update will be inplace and not generating overflows. Don't forget to run an RUNSTATS afterwards, as data is also in the table/index statistics, or maybe in a db2look file with exported statistics.

 

And if napalm is not at hand, thermite will also be a good option. ;-)

Ruediger Kurtz

AW: Db2 LUW 11.1.5 deleting sensitive data
(in response to Isaac Yassin)
Water’s said to do more damage than fire – haven’t tried either, so dunno if it’s true or not.


Rüdiger Kurtz
Abteilung Informatik - Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer, Sarah Rössler, Daniel Thomas.
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Isaac Yassin <[login to unmask email]>
Gesendet: Montag, 18. Mai 2020 20:35
An: [login to unmask email]
Betreff: [DB2-L] - RE: Db2 LUW 11.1.5 deleting sensitive data

We incinerate the drives
Isaac Yassin
Sent from my Note X

בתאריך יום ב׳, 18 במאי 2020, 21:33, מאת Daniel L Luksetich ‏<[login to unmask email]<mailto:[login to unmask email]>>:
Wow! OK, I’ll try to be as filthy as possible when deleting the data. A data murderer.

Reminds me of when I “decommission” a hard drive at home. It eventually involves a log splitter and/or sledge hammer.

Thanks a lot!
Dan

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email] [cid:[login to unmask email] [cid:[login to unmask email]

From: Roland Schock <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, May 18, 2020 1:27 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 LUW 11.1.5 deleting sensitive data


Good question and great answer. From my perspective I'd like to add, that Db2 for LUW will keep the row data on the datapage (accessible by db2dart dump options) until a reorg of that table.

If you delete more rows or a full table, the data pages might still exist in the tablespace, even if maybe less accessible by db2dart then. Alter tablespace reduce max could help to return those data pages to the OS. They still might be untouched on disk then (accessible by NSA or disk imaging tools).

Data could be still in indexes, but would also be gone after a reorg.

Data in logs is a bit more difficult to retrieve, although not impossible. Different to Db2 for z, Db2 LUW has no official way to format log pages from the transaction log. All the APIs and docs are kept pretty private. But on the other hand, you could use the trial edition of Db2 Recovery Expert in AESE to generate undi scripts from the logs.

I guess, overwriting multiple times, incinerating the disk with napalm and cutting the disk drives in halves are not an option, but would be rather sufficient. This might be a nice read in case: https://www.theguardian.com/world/video/2014/jan/31/snowden-files-computer-destroyed-guardian-gchq-basement-video

Regards

Roland

-----End Original Message-----

-----End Original Message-----

David Williams

Db2 LUW 11.1.5 deleting sensitive data
(in response to Roland Schock)
Hi,

Do not forget that if the column is in an index that the update would move the place in the index and so the value
could be left on index pages!

https://www.sqlservercentral.com/forums/topic/is-a-delete-really-100-gone/page/3

From 2004 this mentions "Informix-OnLine/Secure, Trusted Oracle7, & Sybase's Secure SQL Server".

I suspect that these Dod Secure Versions of the products would overwrite on delete with the associated performance penalty.

Possibly even synchronous write after commit and before returning success to the client commit request!

Good questions how to you handle commit?

If you overwrite before commit you break the semantics of commit as rollback is not possible.

If you overwrite after commit then how does the client know the old data was overwritten?

I also wonder if the overwrite fails would the database instance abort?

PS I have not see a 'Secure' Version of DB2!!

Regards,
David.

> On 19 May 2020 at 06:42 Roland Schock <[login to unmask email]> wrote:
>
>
> I apologize to have turned your honest question in such a battlefield now.
> A very subversive method would be, to overwrite the data in such a way, that it still contains data but completely false information. Then drop them in the normal rubbish bin, lean back, have a beer and wait.
> Roundabout 30+ years ago on my first computer (Sharp MZ-80B with Z80 CPU, 64KB RAM) I got a copy of CP/M and some text processor. While debugging some code I found a prefilled buffer area with the text "Nosey, aren't you".
>  
> So we are back on topic: Overwriting data with useless information but with the same length, so the update will be inplace and not generating overflows. Don't forget to run an RUNSTATS afterwards, as data is also in the table/index statistics, or maybe in a db2look file with exported statistics.
>  
> And if napalm is not at hand, thermite will also be a good option. ;-)
>
>
> -----End Original Message-----

James Campbell

Db2 LUW 11.1.5 deleting sensitive data
(in response to David Williams)
The way that defense and like agencies perform a secure wipe is by destruction.

"Hard disk drive shredders do not sanitize unless a 2 millimeter particle size or smaller is accomplished ..."
after
"The hard disk drive destruction devices contained in this document are only authorized in conjunction with a magnetic degausser for routine magnetic hard disk drive sanitization, or by themselves only in emergencies"
https://www.nsa.gov/Portals/70/documents/resources/everyone/media-destruction/NSAEPLHardDiskDriveDestructionDevicesMarch2020.pdf?ver=2020-03-17-094745-757

which happens if the physical objects have to be moved from a secure environment.

No napalm required.

Of course that does leave the problem of the memory of those who have seen the classified
information.

James Campbell


On 19 May 2020 at 12:07, David Williams wrote:

> Hi,
>
> Do not forget that if the column is in an index that the update would move the place in the index and so the value
> could be left on index pages!
>
> https://www.sqlservercentral.com/forums/topic/is-a-delete-really-100-gone/page/3
>
> From 2004 this mentions "Informix-OnLine/Secure, Trusted Oracle7, & Sybase's Secure SQL Server".
>
> I suspect that these Dod Secure Versions of the products would overwrite on delete with the associated performance penalty.
>
> Possibly even synchronous write after commit and before returning success to the client commit request!
>
> Good questions how to you handle commit?
>
> If you overwrite before commit you break the semantics of commit as rollback is not possible.
>
> If you overwrite after commit then how does the client know the old data was overwritten?
>
> I also wonder if the overwrite fails would the database instance abort?
>
> PS I have not see a 'Secure' Version of DB2!!
>
> Regards,
> David.
>
> > On 19 May 2020 at 06:42 Roland Schock <[login to unmask email]> wrote:
> >
> >
> > I apologize to have turned your honest question in such a battlefield now.
> > A very subversive method would be, to overwrite the data in such a way, that it still contains data but completely false information. Then drop them in the normal rubbish bin, lean back, have a beer and wait.
> > Roundabout 30+ years ago on my first computer (Sharp MZ-80B with Z80 CPU, 64KB RAM) I got a copy of CP/M and some text processor. While debugging some code I found a prefilled buffer area with the text "Nosey, aren't you".
> >  
> > So we are back on topic: Overwriting data with useless information but with the same length, so the update will be inplace and not generating overflows. Don't forget to run an RUNSTATS afterwards, as data is also in the table/index statistics, or maybe in a db2look file with exported statistics.
> >  
> > And if napalm is not at hand, thermite will also be a good option. ;-)
> >
> >
> > -----End Original Message-----
>


--
This email has been checked for viruses by AVG.
https://www.avg.com

Joe Geller

RE: Db2 LUW 11.1.5 deleting sensitive data
(in response to James Campbell)

Now we are getting into the area of collateral damage.  Dan never specified that he was purging the entire database.  If he was talking about purging one or more tables or a subset of rows from a table, then destroying the disks will also wipe out the data that was not intended to be wiped out.

Joe

In Reply to James Campbell:

The way that defense and like agencies perform a secure wipe is by destruction.

"Hard disk drive shredders do not sanitize unless a 2 millimeter particle size or smaller is accomplished ..."
after
"The hard disk drive destruction devices contained in this document are only authorized in conjunction with a magnetic degausser for routine magnetic hard disk drive sanitization, or by themselves only in emergencies"
https://www.nsa.gov/Portals/70/documents/resources/everyone/media-destruction/NSAEPLHardDiskDriveDestructionDevicesMarch2020.pdf?ver=2020-03-17-094745-757

which happens if the physical objects have to be moved from a secure environment.

No napalm required.

Of course that does leave the problem of the memory of those who have seen the classified
information.

James Campbell


On 19 May 2020 at 12:07, David Williams wrote:

> Hi,
>
> Do not forget that if the column is in an index that the update would move the place in the index and so the value
> could be left on index pages!
>
> https://www.sqlservercentral.com/forums/topic/is-a-delete-really-100-gone/page/3
>
> From 2004 this mentions "Informix-OnLine/Secure, Trusted Oracle7, & Sybase's Secure SQL Server".
>
> I suspect that these Dod Secure Versions of the products would overwrite on delete with the associated performance penalty.
>
> Possibly even synchronous write after commit and before returning success to the client commit request!
>
> Good questions how to you handle commit?
>
> If you overwrite before commit you break the semantics of commit as rollback is not possible.
>
> If you overwrite after commit then how does the client know the old data was overwritten?
>
> I also wonder if the overwrite fails would the database instance abort?
>
> PS I have not see a 'Secure' Version of DB2!!
>
> Regards,
> David.
>
> > On 19 May 2020 at 06:42 Roland Schock <[login to unmask email]> wrote:
> >
> >
> > I apologize to have turned your honest question in such a battlefield now.
> > A very subversive method would be, to overwrite the data in such a way, that it still contains data but completely false information. Then drop them in the normal rubbish bin, lean back, have a beer and wait.
> > Roundabout 30+ years ago on my first computer (Sharp MZ-80B with Z80 CPU, 64KB RAM) I got a copy of CP/M and some text processor. While debugging some code I found a prefilled buffer area with the text "Nosey, aren't you".
> >  
> > So we are back on topic: Overwriting data with useless information but with the same length, so the update will be inplace and not generating overflows. Don't forget to run an RUNSTATS afterwards, as data is also in the table/index statistics, or maybe in a db2look file with exported statistics.
> >  
> > And if napalm is not at hand, thermite will also be a good option. ;-)
> >
> >
> > -----End Original Message-----
>


--
This email has been checked for viruses by AVG.
https://www.avg.com

Jeff Goss

RE: Db2 LUW 11.1.5 deleting sensitive data
(in response to Joe Geller)



In Reply to Joe Geller:

Now we are getting into the area of collateral damage.  Dan never specified that he was purging the entire database.  If he was talking about purging one or more tables or a subset of rows from a table, then destroying the disks will also wipe out the data that was not intended to be wiped out.

Joe

In LUW moving the data you want to keep (online table move, load from cursor) to a new tablespace and then dropping the old one plus using a disk overwrite utility of that old disk space once no longer part of the database would be the only true way to be assured you have completely removed any way to find some of that data on disk.  And do the same to old backups.

Deleting a row can leave pseudo-deleted keys. I believe if you lock the table X then we won't do that, but depending on whether the index page was split/merged etc. there still may be some data in the unused portion of the index page.  Likewise for the data page deleting rows just makes their space free until a page reorg (meaning a later insert or update can't find contiguous space on the page but there is enough for the operation). Reorging a table can overwrite the original object if you use a temp, but if the reorged object is smaller then those pages at the end of the table are freed back to the tablespace for re-use.  These will not be overwritten until re-use by another object.  Similar for index recreation at the end of reorg, etc. etc. You can probably come up with other cases for operations like rebalance, reclaim extents, and more.

Roland Schock

RE: Db2 LUW 11.1.5 deleting sensitive data
(in response to Joe Geller)

Just out of band: Congratulations to Joe for his 1k blog post!

And regarding water or fire doing the most damage: https://www.securedatarecovery.com/blog/top-5-incredible-recovery-stories

I'd say the degausser and shredder are best.

Attachments

  • Joe.jpg (7.6k)