{"id":28,"date":"2008-09-25T13:06:48","date_gmt":"2008-09-25T11:06:48","guid":{"rendered":"http:\/\/192.168.0.91\/?p=21"},"modified":"2008-10-08T18:51:05","modified_gmt":"2008-10-08T16:51:05","slug":"data-anonymizer-sql","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2008\/09\/25\/data-anonymizer-sql\/","title":{"rendered":"Data Anonymizer SQL DIY"},"content":{"rendered":"<p>From time to time it is necessary to remove sensitive data, e.g. credit card information, birth dates, names, etc. from tables. One way to accomplish this can be done is with parallel DML:<\/p>\n<ul>\n<li>The first step is to seed the random number generator.<\/li>\n<\/ul>\n<blockquote><p>begin dbms_random.seed(&#8216;oiw3284js&#8217;);<br \/>\nend;<br \/>\n\/<\/p><\/blockquote>\n<ul>\n<li>Then, parallel DML has to be enabled as it is disabled per default. Note the different locking mechanism when DML is enabled.<\/li>\n<\/ul>\n<blockquote><p>ALTER SESSION ENABLE PARALLEL DML;<\/p><\/blockquote>\n<ul>\n<li>Next, the update statement is executed. Charactar columns can be set with dbms_random.string whereas number values can be set with dbms_random.value.<\/li>\n<\/ul>\n<blockquote><p>UPDATE \/*+ PARALLEL(C,8) *\/ CONTACT C<br \/>\nset EMAIL_ADDR\u00a0 = dbms_random.string(&#8216;A&#8217;, nvl(length(EMAIL_ADDR), 0)),<br \/>\nFAX_PH_NUM\u00a0 = dbms_random.string(&#8216;A&#8217;, nvl(length(FAX_PH_NUM), 0)),<br \/>\nFST_NAME\u00a0\u00a0\u00a0 = dbms_random.string(&#8216;A&#8217;, nvl(length(FST_NAME), 0)),<br \/>\nHOME_PH_NUM = dbms_random.string(&#8216;A&#8217;, nvl(length(HOME_PH_NUM), 0)),<br \/>\nLAST_NAME\u00a0\u00a0 = dbms_random.string(&#8216;A&#8217;, nvl(length(LAST_NAME), 0)),<br \/>\nWORK_PH_NUM = dbms_random.string(&#8216;A&#8217;, nvl(length(WORK_PH_NUM), 0)),<br \/>\n&#8211;nicht verwendet in query<br \/>\nX_BC_NUMBER\u00a0\u00a0\u00a0\u00a0\u00a0 = CASE WHEN nvl(length(X_BC_NUMBER), 0) &gt; 0 THEN to_char(round(dbms_random.value(4917888006015004, 7081430000510616), 0)) ELSE NULL END,<br \/>\nX_ACCOUNT_HOLDER = dbms_random.string(&#8216;A&#8217;,<br \/>\nnvl(length(X_ACCOUNT_HOLDER), 0)),<br \/>\nX_ACCOUNT_NUMBER = dbms_random.string(&#8216;A&#8217;,<br \/>\nnvl(length(X_ACCOUNT_NUMBER), 0)),<br \/>\nX_PHOTO_URL\u00a0\u00a0\u00a0\u00a0\u00a0 = CASE WHEN<br \/>\nnvl(length(X_PHOTO_URL), 0) &gt; 0<br \/>\nTHEN &#8216;https:\/\/www.myurl.de\/&#8217; || to_char(round(dbms_random.value(1111111111111111,\u00a0 9999999999999999),0))<br \/>\nELSE NULL END;<\/p>\n<p>commit;<\/p><\/blockquote>\n<p>The professional solution to data anonymizing is the new <a href=\"http:\/\/www.oracle.com\/technology\/products\/oem\/pdf\/ds_datamasking.pdf\">ORACLE ENTERPRISE MANAGER 10 DATA MASKING PACK.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>From time to time it is necessary to remove sensitive data, e.g. credit card information, birth dates, names, etc. from tables. One way to accomplish this can be done is with parallel DML: The first step is to seed the random number generator. begin dbms_random.seed(&#8216;oiw3284js&#8217;); end; \/ Then, parallel DML has to be enabled as [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13],"tags":[],"class_list":["post-28","post","type-post","status-publish","format-standard","hentry","category-10g"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/28","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=28"}],"version-history":[{"count":4,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/28\/revisions"}],"predecessor-version":[{"id":241,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/28\/revisions\/241"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=28"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=28"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=28"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}