Encryption in Oracle

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: GRANT EXECUTE  ON dbms_crypto TO username;

The code to store data in an encrypted form is:

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;

This can then be used to store data. As an example:

create table Tom_Encrypt(naam_enc varchar2(255));

followed by:

insert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Dikke Tom')); 

insert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Lieve Ine')); 

insert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Paula 24 jan')); 

insert into Tom_Encrypt(naam_enc) values(F_ENCRYPTION('Stella')); 

commit;

select * from Tom_Encrypt;

The stored data can be seen, but the actual content remains unknown:

With a special function, we are able to see the actual content. The function to decrypt is:

CREATE OR REPLACE FUNCTION F_DECRYPTION 

(encrypted_text IN VARCHAR2)

RETURN VARCHAR2 AS 

decrypted_text varchar2(255);

raw_set RAW(100);   

raw_password RAW(100);   

decryption_result RAW(100);   

decryption_password VARCHAR2(100) := 'bunvegni';   

operation_mode NUMBER; 

BEGIN

        raw_set:=HEXTORAW(encrypted_text);   

        raw_password :=utl_i18n.string_to_raw(decryption_password,'AL32UTF8');   

        operation_mode:=DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.PAD_ZERO + DBMS_CRYPTO.CHAIN_ECB;      

        decryption_result:=DBMS_CRYPTO.DECRYPT(raw_set,operation_mode,raw_password);   

        dbms_output.put_line(decryption_result);   

        decrypted_text := utl_i18n.raw_to_char (decryption_result,'AL32UTF8'); 

        RETURN decrypted_text;

END F_DECRYPTION;

This then allows us to see the content. The SQL code is: select F_DECRYPTION(naam_enc) from Tom_encrypt;