Thinking Out Loud

October 13, 2012

Data Destruction

Filed under: 11g,oracle — mdinh @ 2:47 am

One method to ensure data privacy is to destroy it.

The quick approach is to TRUNCATE tables and remove columns.

Dropping columns from tables with million of rows can take a very long time while SET UNUSED is much faster.

In order not to break the application, the same columns are added back.

This approach will not work if existing DML in code/application does not specify column names and values pair.

Update:

The update to fk_parent3_id, and pk_parent3_id columns are necessary because supposedly natural keys are being used.

This is where artificial key can benefit since the update can be make to the alternate key on the parent table only and still maintain referential integrity.

The process is outlined below:

set serverout on lines 130 echo off pages 1000
– DO NOT EXECUTE IN PRODUCTION
WHENEVER SQLERROR EXIT 1
begin
if (substr(SYS_CONTEXT(‘USERENV’,’SERVER_HOST’),1,4) = ‘prod’) then
RAISE_APPLICATION_ERROR (-20222, ‘!!!!! NOT ALLOWED IN PRODUCTION !!!!!’);
end if;
end;
/
– DYNAMICALLY CREATE AND NAME LOGFILE
WHENEVER SQLERROR CONTINUE
define _logfile=””
col logfile new_value _logfile
col table_name for a20
col column_name for a30
col data_type for a10
col fmt_data_type for a20
col tn for a20
col cn for a80
set echo on
select lower(user)||’_dm.log’ logfile from dual;
spool &_logfile
timing start dm_DESTROY
– DOMAIN INDEX MUST BE DROPPED TO SET UNUSED COLUMNS
timing start dm_DROPINDEX
EXEC EXECUTE IMMEDIATE ‘drop index DOMAIN_IDX force’; EXCEPTION WHEN OTHERS THEN NULL;
timing stop dm_DROPINDEX
– TRUNCATE TABLES WHERE APPLICABLE
timing start dm_TRUNCATE
truncate table T1;
truncate table T2;
alter table PARENT1 DROP PRIMARY KEY CASCADE;
truncate table PARENT1_CHILD1;
truncate table PARENT1_CHILD2;
timing stop dm_TRUNCATE
– CREATE TMP TABLE TO STORE COLUNM ATTRIBUTES.
set echo off
timing start dm_TMP_MASK_COLS
EXEC EXECUTE IMMEDIATE ‘drop table TMP_MASK_COLS cascade constraint’; EXCEPTION WHEN OTHERS THEN NULL;
CREATE TABLE TMP_MASK_COLS
AS
SELECT table_name,column_name,data_type,data_length,data_precision,data_scale,
(CASE data_type
WHEN ‘VARCHAR2′ THEN data_type||’(‘||data_length||’)’
WHEN ‘DATE’ THEN data_type
WHEN ‘NUMBER’ THEN DECODE(data_precision,NULL,data_type,data_type||’(‘||data_precision||’,’||data_scale||’)’)
END) fmt_data_type
FROM user_tab_cols
WHERE table_name IN (‘PRIVATE1′,’PRIVAT2′,’PPRIVATE3′)
AND REGEXP_LIKE (column_name,’ADDR|CITY|EMAIL|FAX|NAME|PHONE|ZIP|DOB|SSN’)
ORDER BY table_name, column_id ASC;
– SET UNUSED COLUMNS
DECLARE
l_sql VARCHAR2 (4000);
BEGIN
FOR x IN (
SELECT table_name tn, LISTAGG(column_name,’,’) WITHIN GROUP(ORDER BY column_name) cn FROM TMP_MASK_COLS GROUP BY table_name
) LOOP
l_sql := ‘ALTER TABLE ‘||x.tn||’ SET UNUSED (‘||x.cn||’)’;
DBMS_OUTPUT.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END;
/
– RE-ADD COLUMNS TO REPLACE UNUSED
DECLARE
l_sql VARCHAR2 (4000);
BEGIN
FOR x IN (
SELECT table_name tn, column_name||’ ‘||fmt_data_type cn FROM TMP_MASK_COLS ORDER BY tn,cn
) LOOP
l_sql := ‘ALTER TABLE ‘||x.tn||’ ADD (‘||x.cn||’)’;
DBMS_OUTPUT.put_line(l_sql);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END;
/
– UPDATE CHILD3 FK (only 9 digit SSN candidate)
– Step is necessary since column is part of composite primary key
set echo on
timing start dm_CHILD3
alter table CHILD3 disable constraint FK_CHILD3_PARENT3;
alter table CHILD3 disable all triggers;
UPDATE CHILD3
SET fk_parent3_id=DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(fk_parent3_id),3)
WHERE REGEXP_LIKE(fk_parent3_id,’^[[:digit:]]{9}$’);
commit;
timing stop dm_CHILD3
– UPDATE PARENT3 PKĀ (only 9 digit SSN candidate)
timing start dm_PARENT3
alter table PARENT3 disable all triggers;
UPDATE PARENT3
SET pk_parent3_id=DBMS_CRYPTO.hash(UTL_RAW.cast_to_raw(pk_parent3_id),3)
WHERE REGEXP_LIKE(pk_parent3_id,’^[[:digit:]]{9}$’);
timing stop dm_PARENT3
– ENABLE CONSTRAINTS / TRIGGERS
timing start dm_ENABLE
alter table PARENT3 enable all triggers;
alter table CHILD3 enable all triggers;
alter table CHILD3 enable constraint FK_CHILD3_PARENT3;
timing stop dm_ENABLE
– LIST TABLES AND COLUMNS DESTROYED
timing stop dm_DESTROY
SELECT table_name, LISTAGG(column_name,’,’) WITHIN GROUP(ORDER BY column_name) mask_cols
FROM TMP_MASK_COLS GROUP BY table_name;
timing stop dm_TMP_MASK_COLS
spool off
exit

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 294 other followers

%d bloggers like this: