{"id":149,"date":"2015-03-12T21:26:54","date_gmt":"2015-03-13T01:26:54","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2015\/03\/12\/obfuscate-sensitive-data-in-oracle\/"},"modified":"2015-03-12T21:26:54","modified_gmt":"2015-03-13T01:26:54","slug":"obfuscate-sensitive-data-in-oracle","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2015\/03\/12\/obfuscate-sensitive-data-in-oracle\/","title":{"rendered":"Obfuscate sensitive data in Oracle"},"content":{"rendered":"<p>If business needs requires you to store sensitive data such as social security numbers, bank routing\/account numbers, and so on, you should ensure the data is stored in a safe way.  Below are a set of two simple functions to encrypt\/obfuscate such data to get your started.<\/p>\n<p>To encrypt a varchar2 string with a specific encryption phrase (or &#8220;key&#8221;):<\/p>\n<pre class=\"code\">\ncreate or replace function your_schema.encrypt(clear_varchar_ varchar2, key_ varchar2) return varchar2 \nis\n\tv_clear_varchar varchar2(2000);\n\tv_enc_raw\t\traw(2000);\n\tv_enc_varchar\tvarchar2(2000);\nbegin\n\tif (mod(length(clear_varchar_), 8) != 0) then\n\t\tv_clear_varchar := rpad(clear_varchar_, length(clear_varchar_) + 8 - mod(length(clear_varchar_), 8), chr(0));\n\telse\n\t\tv_clear_varchar := clear_varchar_;\n\tend if;\n\tdbms_obfuscation_toolkit.desencrypt(input => utl_raw.cast_to_raw(rpad(v_clear_varchar, 64, ' ')),\n\t\tkey => utl_raw.cast_to_raw(key_), \n\t\tencrypted_data => v_enc_raw);\n\t\tv_enc_varchar := utl_raw.cast_to_varchar2(v_enc_raw);\n\treturn v_enc_varchar;\nend;\n<\/pre>\n<p>The following function decrypts; you must use the same key that was used to encrypt it.<\/p>\n<pre class=\"code\">\ncreate or replace function your_schema.decrypt(enc_varchar_ varchar2, key_ varchar2) return varchar2 \nis\n\tv_tmp_raw    \t raw(2048);\n\tv_clear_varchar\tvarchar2(4000);\nbegin\n\tdbms_obfuscation_toolkit.desdecrypt(input => utl_raw.cast_to_raw(enc_varchar_),\n\t\tkey =>  utl_raw.cast_to_raw(key_), \n\t\tdecrypted_data => v_tmp_raw);\n\tv_clear_varchar := replace(trim(utl_raw.cast_to_varchar2(v_tmp_raw)),chr(0),'');\n\treturn v_clear_varchar;\nend;\n<\/pre>\n<p>Here is an example usage: The following SQL statement inserts an obfuscated password into a table that stores user data.<\/p>\n<pre class=\"code\">\ninsert into your_schema.user_accounts (username, password)\nvalues(\n\t'scott', \n\tyour_schema.encrypt('tiger', '_seCret!keY:3')\n);\n<\/pre>\n<p>And below is how you would retrieve and decrypt the password.<\/p>\n<pre class=\"code\">\nselect your_schema.decrypt(password, '_seCret!keY:3') from your_schema.user_accounts where username='scott';\n<\/pre>\n<p>Security is a serious matter and it warrants extensive research.  This article merely offers the awareness that sensitive data should not be stored in clear text, and hopefully provides a good starting point.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article provides simple functions to obfuscate sensitive data stored in an Oracle database table.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-149","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=149"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/149\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}