I had an interesting conversation with Alexander Kornbrust yesterday about cloning databases. Most DBAs I know copy database files from production to create staging, integration and test environments. Those environments contain a lot of sensitive information (PII, CC, etc.) which is usually either deleted, scrambled or truncated. The problem with these solutions is that most DBAs forget that the database performs logical deletes and not physical deletes. This can be easily demonstrated on Oracle by the following simple steps that create a table, populate it using dummy data, truncating it and showing the data from the dump file:

  • create table test(t varchar2(30));
  • insert into test select object_name from user_objects where rownum < 1000;
  • commit;
  • select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test where rownum < 2;
  • truncate table test;
  • For the following step, replace ‘x’ and ‘y’ with the results from the previous select
  • alter system dump datafile x block y;
  • show parameter user_dump_dest
  • Check out the new file in the user_dump_dest directory. The file will contain the truncated data in the block.

Of course, this is just an example but it is worth thinking about. It is also worth considering TDE to protect the data files from direct reading.

DBAs out there – what do you do to remove sensitive information from your non-production environments?