Some values, such as passport numbers, should be stored so that only code holding a key can read them. DBMS_CRYPTO.ENCRYPT encrypts RAW data with AES and DECRYPT reverses it. The encryption type combines the algorithm, the chaining mode, and the padding, and each value should get its own random initialization vector.
Code for This Guide
The main examples are in the examples/pkg-security folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
c_type := dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;
encrypted := dbms_crypto.encrypt(src => raw_data, typ => c_type, key => raw_key,
iv => raw_iv);
decrypted := dbms_crypto.decrypt(src => encrypted, typ => c_type, key => raw_key,
iv => raw_iv);
dbms_crypto.randombytes(n) -- cryptographically secure random bytesAES-256 needs a 32-byte key and a 16-byte IV. Text is converted to RAW in a fixed character set first, here AL32UTF8.
Encrypt and Decrypt a Value
The example uses a fixed test key and IV so its output is repeatable; never do that with real data.
Example:
declare
-- AES with a 256-bit key, CBC chaining, and PKCS#5 padding
c_type constant pls_integer := dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc
+ dbms_crypto.pad_pkcs5;
-- fixed test values; use dbms_crypto.randombytes(32) and (16), and keep the key safe
v_key raw(32) := hextoraw('000102030405060708090A0B0C0D0E0F'
|| '101112131415161718191A1B1C1D1E1F');
v_iv raw(16) := hextoraw('A0A1A2A3A4A5A6A7A8A9AAABACADAEAF');
v_enc raw(200);
begin
v_enc := dbms_crypto.encrypt(utl_i18n.string_to_raw('Passport X1234567', 'AL32UTF8'),
c_type, v_key, v_iv);
dbms_output.put_line('encrypted: ' || v_enc);
dbms_output.put_line('decrypted: ' || utl_i18n.raw_to_char(
dbms_crypto.decrypt(v_enc, c_type, v_key, v_iv), 'AL32UTF8'));
end;
/Output:
encrypted: 209672DA7564E20FD17BF1F997F0EB1B530E9A96CBA33C5A729995F912A0876C decrypted: Passport X1234567 PL/SQL procedure successfully completed.
The 17-byte passport text becomes 32 bytes of ciphertext, padded to whole 16-byte blocks, and decrypts back to the original.
Random Key and a Fresh IV per Value
Example:
declare
c_type constant pls_integer := dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc
+ dbms_crypto.pad_pkcs5;
v_key raw(32) := dbms_crypto.randombytes(32); -- a new random key for this demo
v_data raw(100) := utl_i18n.string_to_raw('Passport X1234567', 'AL32UTF8');
v_iv raw(16);
v_enc raw(200);
begin
for i in 1 .. 2 loop
v_iv := dbms_crypto.randombytes(16); -- a fresh IV for every value
v_enc := dbms_crypto.encrypt(v_data, c_type, v_key, v_iv);
dbms_output.put_line('run ' || i || ': ciphertext '
|| rawtohex(utl_raw.substr(v_enc, 1, 8))
|| '..., decrypts to ' || utl_i18n.raw_to_char(
dbms_crypto.decrypt(v_enc, c_type, v_key, v_iv), 'AL32UTF8'));
end loop;
end;
/Output:
run 1: ciphertext 834F1F20E3D20EE7..., decrypts to Passport X1234567 run 2: ciphertext 44FB5EED2A1B7B14..., decrypts to Passport X1234567 PL/SQL procedure successfully completed.
The same text encrypted twice with fresh IVs gives different ciphertexts, so identical values cannot be spotted in the table. Store the IV next to the ciphertext; it is not secret. Your ciphertexts will differ, since the key and IVs are random.
Things to Know
- Keep keys out of the database tables they protect and out of code, for example in a wallet or a key vault.
- Using DBMS_CRYPTO needs EXECUTE on the package, granted by a DBA.
- To protect whole columns or tablespaces without code changes, Transparent Data Encryption is the tool.
Related Guides
Conclusion
DBMS_CRYPTO.ENCRYPT and DECRYPT protect values with AES, given a key, a chaining mode, padding, and an IV. Generate keys and IVs with RANDOMBYTES, use a fresh IV for every value, and keep the key somewhere other than the data.
