Breadcrumbs

Using DBMS_CRYPTO

DBMS_CRYPTO does not support storing keys in a wallet by itself, but a table can be used for storing these keys that are either stored in an encrypted tablespace or have an encrypted key column.

  1. Create a table for storing the keys and add a key to the table:

›_ Console

 
SQL > CREATE TABLE secured_keys (key_name VARCHAR2(30), key RAW(32) ENCRYPT NO SALT); 

SQL > INSERT INTO secured_keys (key_name, key) VALUES ('TestKey', 

DBMS_CRYPTO.RANDOMBYTES(32));


  1. Save the following script to a file: 

DECLARE
input_string VARCHAR2 (200) := 'Secret Message';
output_string VARCHAR2 (200);
encrypted_raw RAW (2000); -- stores encrypted binary text
decrypted_raw RAW (2000); -- stores decrypted binary text
key_bytes_raw RAW (32); -- stores 256-bit encryption key
encryption_type PLS_INTEGER := -- total encryption type
DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Original string: ' || input_string);
SELECT key INTO key_bytes_raw FROM secured_keys WHERE key_name = 'TestKey';
encrypted_raw := DBMS_CRYPTO.ENCRYPT
(
src => UTL_I18N.STRING_TO_RAW (input_string, 'AL32UTF8'),
typ => encryption_type,
key => key_bytes_raw
);
-- The encrypted value "encrypted_raw" can be used here
DBMS_OUTPUT.PUT_LINE ('Encrypted: ' || RAWTOHEX(encrypted_raw));
decrypted_raw := DBMS_CRYPTO.DECRYPT
(
src => encrypted_raw,
typ => encryption_type,
key => key_bytes_raw
);
output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;

Alternatively, the following two commands can be used instead of the script:

SQL > CONNECT sqlplus as sysdba;

SQL > GRANT EXECUTE ON sys.dbms_crypto TO user2;

›_ Console

 
SQL > INSERT INTO secured_keys (key_name, key) VALUES ('TestKey', 

DBMS_CRYPTO.RANDOMBYTES(32));


  1. Enable the server output and run the script:

›_ Console

 
SQL> SET SERVEROUTPUT ON 

SQL> \(filename)  

SQL> \