{"id":2143,"date":"2018-12-09T21:58:52","date_gmt":"2018-12-09T20:58:52","guid":{"rendered":"http:\/\/van-maanen.com\/?p=2143"},"modified":"2018-12-09T21:58:52","modified_gmt":"2018-12-09T20:58:52","slug":"encryption-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=2143","title":{"rendered":"Encryption in Oracle"},"content":{"rendered":"\n

Oracle has a nice facility to store data in an encrypted form. This allows to hide data content from users while still allowing them to see the columns. If one would like to use the encryption facilities, one must be given this grant:\u00a0GRANT EXECUTE\u00a0\u00a0ON dbms_crypto TO username;<\/p>\n\n\n\n

The code to store data in an encrypted form is:<\/p>\n\n\n\n

CREATE OR REPLACE FUNCTION F_ENCRYPTION 
(text IN VARCHAR2
)
RETURN VARCHAR2 AS 
encrypted_text  VARCHAR2(255);
raw_set RAW(100);  
raw_password RAW(100);  
encryption_result RAW(100);  
encryption_password VARCHAR2(100) := 'bunvegni';  
operation_mode NUMBER; 
BEGIN
        raw_set:=utl_i18n.string_to_raw(text,'AL32UTF8');    
        raw_password := utl_i18n.string_to_raw(encryption_password,'AL32UTF8');        
        operation_mode:=DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.PAD_ZERO + DBMS_CRYPTO.CHAIN_ECB;        
        encryption_result:=DBMS_CRYPTO.ENCRYPT(raw_set,operation_mode,raw_password);           
        encrypted_text := RAWTOHEX (encryption_result);  
        RETURN encrypted_text;
END F_ENCRYPTION;<\/pre>\n\n\n\n
This can then be used to store data. As an example:<\/p>\n\n\n\n
create table Tom_Encrypt(naam_enc varchar2(255));<\/p>\n\n\n\n
followed by:<\/p>\n\n\n\n
insert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Dikke Tom')); 
\ninsert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Lieve Ine')); 
\ninsert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Paula 24 jan')); 
\ninsert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Stella')); 
\ncommit;
\nselect * from Tom_Encrypt;<\/pre>\n\n\n\n
The stored data can be seen, but the actual content remains unknown:<\/p>\n\n\n\n
\"\"
unknown content<\/figcaption><\/figure>\n\n\n\n
With a special function, we are able to see the actual content. The function to decrypt is:<\/p>\n\n\n\n
CREATE OR REPLACE FUNCTION F_DECRYPTION 
\n(encrypted_text IN VARCHAR2)
\nRETURN VARCHAR2 AS 
\ndecrypted_text varchar2(255);
\nraw_set RAW(100);   
\nraw_password RAW(100);   
\ndecryption_result RAW(100);   
\ndecryption_password VARCHAR2(100) := 'bunvegni';   
\noperation_mode NUMBER; 
\nBEGIN
\n        raw_set:=HEXTORAW(encrypted_text);   
\n        raw_password :=utl_i18n.string_to_raw(decryption_password,'AL32UTF8');   
\n        operation_mode:=DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.PAD_ZERO + DBMS_CRYPTO.CHAIN_ECB;      
\n        decryption_result:=DBMS_CRYPTO.DECRYPT(raw_set,operation_mode,raw_password);   
\n        dbms_output.put_line(decryption_result);   
\n        decrypted_text := utl_i18n.raw_to_char (decryption_result,'AL32UTF8'); 
\n        RETURN decrypted_text;
\nEND F_DECRYPTION;<\/pre>\n\n\n\n
This then allows us to see the content. The SQL code is:\u00a0select F_DECRYPTION(naam_enc) from Tom_encrypt;<\/p>\n\n\n\n
\"\"
Being able to read data.<\/figcaption><\/figure>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle has a nice facility to store data in an encrypted form. This allows to hide data content from users while still allowing them to see the columns. If one would like to use the encryption facilities, one must be given this grant:\u00a0GRANT EXECUTE\u00a0\u00a0ON dbms_crypto TO username; The code to store data in an encrypted […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2143","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2143"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2143\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2143"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}