Plain encryption hides data but does not detect changes: altered ciphertext still decrypts, to garbage. AES-GCM is authenticated encryption. Along with the ciphertext it produces a tag, and decryption fails if the ciphertext, the tag, or extra authenticated data has been changed. DBMS_CRYPTO supports it with AES_GCM_NONE.
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
encrypted := dbms_crypto.encrypt(src, dbms_crypto.aes_gcm_none, key, iv,
aad, tag_out);
decrypted := dbms_crypto.decrypt(src, dbms_crypto.aes_gcm_none, key, iv,
aad, tag);The IV is 12 bytes and must never repeat with the same key. AAD, additional authenticated data, is not encrypted but is protected by the tag.
Encrypt, Decrypt, Detect Tampering
The examples use a fixed test key and IV so the output is repeatable.
Example:
declare
v_key raw(32) := hextoraw('000102030405060708090A0B0C0D0E0F'
|| '101112131415161718191A1B1C1D1E1F');
v_iv raw(12) := hextoraw('B0B1B2B3B4B5B6B7B8B9BABB');
v_aad raw(20) := utl_raw.cast_to_raw('booking NX7Q2P'); -- authenticated, not secret
v_tag raw(16);
v_enc raw(200);
begin
v_enc := dbms_crypto.encrypt(utl_raw.cast_to_raw('Seat 12A'), dbms_crypto.aes_gcm_none,
v_key, v_iv, v_aad, v_tag);
dbms_output.put_line('encrypted ' || v_enc || ', tag ' || v_tag);
dbms_output.put_line('decrypted: ' || utl_raw.cast_to_varchar2(
dbms_crypto.decrypt(v_enc, dbms_crypto.aes_gcm_none, v_key, v_iv, v_aad, v_tag)));
-- change one byte of the ciphertext: the tag no longer matches
v_enc := utl_raw.bit_xor(v_enc, hextoraw('0100000000000000'));
dbms_output.put_line(utl_raw.cast_to_varchar2(
dbms_crypto.decrypt(v_enc, dbms_crypto.aes_gcm_none, v_key, v_iv, v_aad, v_tag)));
exception
when others then dbms_output.put_line('tampered: ' || sqlerrm);
end;
/Output:
encrypted CA303BDFCCFC891E, tag 514390E37996C66AAD115C6DDCBF2A01 decrypted: Seat 12A tampered: ORA-28817: PL/SQL function returned an error. PL/SQL procedure successfully completed.
Seat 12A encrypts to 8 bytes, the same length as the text, since GCM needs no padding, plus a 16-byte tag. Decrypting with the right key, IV, AAD, and tag returns the text. After one bit of the ciphertext is flipped, decryption fails with ORA-28817 instead of returning wrong data.
Bind Ciphertext to Its Record
Using the booking reference as AAD ties the encrypted seat to that booking.
Example:
declare
v_key raw(32) := hextoraw('000102030405060708090A0B0C0D0E0F'
|| '101112131415161718191A1B1C1D1E1F'); -- a test key
v_iv raw(12) := hextoraw('B0B1B2B3B4B5B6B7B8B9BABB');
v_tag raw(16);
v_enc raw(200);
begin
v_enc := dbms_crypto.encrypt(utl_raw.cast_to_raw('Seat 12A'), dbms_crypto.aes_gcm_none,
v_key, v_iv, utl_raw.cast_to_raw('booking NX7Q2P'), v_tag);
-- decrypt with the ciphertext moved to another booking: the AAD differs
begin
dbms_output.put_line(utl_raw.cast_to_varchar2(
dbms_crypto.decrypt(v_enc, dbms_crypto.aes_gcm_none, v_key, v_iv,
utl_raw.cast_to_raw('booking QQ1234'), v_tag)));
exception
when others then dbms_output.put_line('other booking: ' || sqlerrm);
end;
end;
/Output:
other booking: ORA-28817: PL/SQL function returned an error. PL/SQL procedure successfully completed.
Decrypting with another booking's reference fails, so ciphertext copied to a different record is rejected.
Things to Know
- Store the IV and the tag with the ciphertext; neither is secret.
- Generate a new random 12-byte IV for every encryption with DBMS_CRYPTO.RANDOMBYTES(12).
- Prefer GCM over CBC for new code: it detects tampering without a separate MAC.
Related Guides
Conclusion
AES-GCM in DBMS_CRYPTO encrypts and authenticates at once: decryption fails when the ciphertext, tag, or AAD is changed. Use a unique IV per value, store the IV and tag with the data, and use AAD to bind ciphertext to its record.
