DBSecWorx: DBMS

Oracle DBMS_CRYPTO Built-In Package Versions 10.1 - 21c

Security Advisory

The DBMS_CRYPTO built-in package provides encryption, decryption, hashing, random strings and random numeric value generation as a service.

The file header, in $ORACLE_HOME/rdbms/admin states the following: "... contains basic cryptographic functions and procedures. To use correctly and securely, a general level of security expertise is assumed. VARCHAR2 datatype is not supported. Cryptographic operations on this type should be prefaced with conversions to a uniform character set (AL32UTF8) and conversion to RAW type. Prior to encryption, hashing or keyed hashing, CLOB datatype is converted to AL32UTF8. This allows cryptographic data to be transferred and understood between databases with different character sets, across character set changes and between separate processes (for example, Java programs)."

When we read that excerpt what stood out was the phrase "a general level of security expertise is assumed". From our experience Oracle developers and DBAs do not have "a general level of security expertise" but do not have sufficient expertise to know what they don't know.

Recommended Security Rules

NEVER

Use this package for anything if there is any way to encrypt the data before it is inserted into database memory.

WITH GREAT CARE

Use the package to encrypt as it is better to encrypt somewhere than not to encrypt at all but monitor any process that enables tracing.
Also, while intentionally not demonstrated in this monograph, this information can also be retrieved from X$ objects accessible to DBAs and others with escalated privileges.

CAUTIONS

Reliance upon security tools requires that you be familiar not only with how they are supposed to work but also with their vulnerabilities.

How Oracle Works

This package words really really well. We advise you not to use it or to use it with great care because, as you will see in the demo, at right, we can still easily access the unencrypted values.

It makes perfect sense for a database to have built-in encryption and decryption capabilities.
      The problem with DBMS_CRYPTO is the fact that it is IN THE DATABASE. Brilliant people tthat likely never were paid to hack an Oracle Database. Lets take an in-depth look at the massive vulnerability that comes with this package.

      

      The following may appear, at first glance, as "complicated" but if the point of the exercise is to steal millions of credit cards, or to information on contracts that could be worth tens or hundreds of millions of dollars, this exploit is trivial.

      

      The first step is to create a table that will store encrypted credit cards.

      

      CREATE TABLE uwclass.credit_cards(

      raw_ccno RAW(2000),

      enc_ccno NUMBER,

      raw_key  RAW(2000));

      

      And a stored procedure that is called by an application on an application server to accept a credit card and store in the table.

      

      CREATE OR REPLACE PROCEDURE uwclass.store_encrypted(ccno IN VARCHAR2)

      AUTHID 
      CURRENT_USER IS

       l_key     RAW(2000);

       l_key_len NUMBER := 256/8; -- convert bits to bytes

       l_mod     NUMBER :=  dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO;

      BEGIN

        l_key := dbms_crypto.randombytes(l_key_len);

      

        INSERT INTO uwclass.credit_cards

        (raw_ccno, enc_ccno, raw_key)

        VALUES

        (dbms_crypto.encrypt(utl_raw.cast_to_raw(CONVERT(ccno,'AL32UTF8')), l_mod, l_key), 
      l_mod, l_key);

        COMMIT;

      END store_encrypted;

      /

      

      Let's test it.

      

      exec uwclass.store_encrypted('3114-0113-1518-7114');

      

      SQL> SELECT * FROM uwclass.credit_cards;

      

      ENC_CCNO

      --------------------------------

      16661B7699FE5B3FC304D6CD0E419A3B

      

      Now let's run it again adding in a person with DBA credential that runs a 10046 Level 4 trace which captures bind variables.
      The trace could be run from SQL*Plus, from from Enterprise Manager, or run using the DBMS_SUPPORT, DBMS_TRACE, or DBMS_MONITOR built-in packages.

      

      ALTER SESSION SET tracefile_identifier = 'dbms_monitor';

      

      exec dbms_monitor.database_trace_enable(binds=>TRUE);

      

      exec uwclass.store_encrypted('3114-0113-1518-7114');

      

      exec dbms_monitor.database_trace_disable;

      

      
      Next we open a terminal window and change to the trace directory. The following listing has been simplified for display purposes.

      

      # cd
      $ORACLE_BASE/diag/trace/orabase19/orabase19/diag

      

      # ls

      alert_orabase19_.log

      orabase19_ora_12224_dbms_monitor.trc

      orabase19_ora_12224_dbms_monitor.trm

      

      
      Use the TKPROF utility to convert the trace file to human readable form.

      

      # tkprof orabase19_ora_12224_dbms_monitor.trc trace_out.txt

      

      Review the output file to to see what was captured  in the trace and, again, the listing is simplified for display purposes.

      
        
          Trace file: orabase19_ora_12224_dbms_monitor.trc

          Sort options: default

          

          ********************************************************************************

          count = number of times OCI procedure was executed

          cpu = cpu time in seconds executing 

          elapsed = elapsed time in seconds executing

          disk = number of physical reads of buffers from disk

          query = number of buffers gotten for consistent read

          current = number of buffers gotten in current mode (usually for update)

          rows = number of rows processed by the fetch or execute call

          ********************************************************************************

          

          SQL ID: bxjjww5n3j84u Plan Hash: 0

          

          BEGIN dbms_monitor.database_trace_enable(binds=>TRUE); END;

          

          SQL ID: d9tb79vu5rwcx Plan Hash: 0

          

          BEGIN store_encrypted('3114-0113-1518-7114'); END;

          

          call     count   cpu  elapsed  disk  query  current  rows

          ------- ------ ----- -------- ----- ------ -------- -----

          Parse        1  0.00     0.00     0       0       0     0

          Execute      1  0.01     0.01     0       0       0     1

          Fetch        0  0.00     0.00     0       0       0     0

          ------- ------ ----- -------- ----- ------- ------- -----

          total        2   0.01    0.01     0       0       0     1

          

          SQL ID: 1ckrh21gcdtkn Plan Hash: 0

          

          INSERT INTO UWCLASS.CREDIT_CARDS (RAW_CCNO, ENC_CCNO) 

          VALUES

          (:B3 , DBMS_CRYPTO.ENCRYPT(ccno_in, UTL_RAW.CAST_TO_RAW(CONVERT(,'AL32UTF8')),

          :B2 , :B1 ))

          

          

          call     count   cpu  elapsed  disk  query  current  rows

          ------- ------ ----- -------- ----- ------ -------- -----

          Parse        1  0.00     0.00     0      0        0     0

          Execute      1  0.01     0.00     3      7       46     1

          Fetch        0  0.00     0.00     0      0        0     0

          ------- ------ ----- -------- ----- ------ -------- -----

          total        2  0.01     0.00     3      7       46     1
        
      
      

      
      That was too easy. And, if tracing was running in the background for hours or days, the credit card information from every in-flight transaction would be captured without the activity being observed by any pentest or any monitoring tool.

      

      There is a very simple rule encrypting data inside a database ... don't. And that doesn't just apply to Oracle it applies to all databases from all OEMs. Encrypt it before it ever gets to the database. Now you know why.

DBMS_CRYPTO Package Information

AUTHID DEFINER

Constants

Name	Data Type	Value
Hash Functions
HASH_MD4 (128 bit hash)	PLS_INTEGER	1
HASH_MD5 (128 bit hash)	PLS_INTEGER	2
HASH_SH1 (160 bit hash)	PLS_INTEGER	3
HASH_SH256	PLS_INTEGER	4
HASH_SH384	PLS_INTEGER	5
HASH_SH512	PLS_INTEGER	6
MAC Functions
HMAC_MD5 (128 bit hash)	PLS_INTEGER	1
HMAC_SH1 (160 bit hash)	PLS_INTEGER	2
HMAC_SH256	PLS_INTEGER	3
HMAC_SH384	PLS_INTEGER	4
HMAC_SH512	PLS_INTEGER	5
Encryption Algorithms
ENCRYPT_DES	PLS_INTEGER	1
ENCRYPT_3DES_2KEY	PLS_INTEGER	2
ENCRYPT_3DES	PLS_INTEGER	3
ENCRYPT_AES	PLS_INTEGER	4
ENCRYPT_PBE_MD5DES	PLS_INTEGER	5
ENCRYPT_AES128	PLS_INTEGER	6
ENCRYPT_AES192	PLS_INTEGER	7
ENCRYPT_AES256	PLS_INTEGER	8
Block Cipher Chaining Modifiers
CHAIN_CBC	PLS_INTEGER	256
CHAIN_CFB	PLS_INTEGER	512
CHAIN_ECB	PLS_INTEGER	768
CHAIN_OFB	PLS_INTEGER	1024
Block Cipher Padding Modifiers
PAD_PKCS5	PLS_INTEGER	4096
PAD_NONE	PLS_INTEGER	8192
PAD_ZERO	PLS_INTEGER	12288
PAD_ORCL	PLS_INTEGER	16384
Stream Cipher Algorithms
ENCRYPT_RC4	PLS_INTEGER	129
Block Cipher Suites
DES_CBC_PKCS5	PLS_INTEGER	ENCRYPT_DES+CHAIN_CBC+PAD_PKCS5
DES3_CBC_PKCS5	PLS_INTEGER	ENCRYPT_3DES+CHAIN_CBC+PAD_PKCS5
AES_CBC_PKCS5	PLS_INTEGER	ENCRYPT_AES+CHAIN_CBC+PAD_PKCS5

Dependencies

DBMS_CRYPTO_FFI	DBMS_REGISTRY	KUPM$MCP
DBMS_ISCHED	DBMS_SCHEDULER	MGWI_CRYPTO
DBMS_ISCHED_REMOTE_ACCESS	DBMS_SQLHASH	UTL_RAW
DBMS_METADATA_DIFF	DBMS_REGISTRY

Documented in Types & Packages Yes

Exceptions

Error Code	Reason
28827	The specified cipher suite is not defined
28829	No value has been specified for the cipher suite to be used
28233	Source data was previously encrypted
28234	DES: Specified key size too short. DES keys must be at least 8 bytes (64 bits). AES: Specified key size is not supported. AES keys must be 128, 192, or 256 bits
28239	The encryption key has not been specified or contains a NULL value

First Available 10.1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/dbmsobtk.sql

Subprograms

DECRYPT	PKDECRYPT	RANDOMNUMBER
ENCRYPT	PKENCRYPT	SIGN
HASH	RANDOMBYTES	VERIFY
MAC	RANDOMINTEGER

DECRYPT

Decrypt crypt text data using stream or block cipher with user supplied key and optional iv

Overload 1 dbms_crypto.decrypt( src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW;

See Encrypt Overload 1 demo

Overload 2 dbms_crypto.decrypt( dst IN OUT NOCOPY BLOB, src IN BLOB, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL);

TBD

Overload 3 dbms_crypto.decrypt( dst IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src IN BLOB, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL);

TBD

ENCRYPT

Encrypt plain text data using stream or block cipher with user supplied key and optional iv

Overload 1 dbms_crypto.encrypt( src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW;

set serveroutput on

      

      set linesize 121

      

      DECLARE

       l_credit_card_no VARCHAR2(19) := '1612-1791-1809-2605';

       l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);

       l_key     RAW(128) := utl_raw.cast_to_raw('abcdefgh');

       l_encrypted_raw RAW(2048);

       l_decrypted_raw RAW(2048);

      BEGIN

        dbms_output.put_line('Original : ' || l_credit_card_no);

        l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw, dbms_crypto.des_cbc_pkcs5, l_key);

        dbms_output.put_line('Encrypted : ' || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));

        l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw, typ => dbms_crypto.des_cbc_pkcs5, key => l_key);

        dbms_output.put_line('Decrypted : ' || utl_raw.cast_to_varchar2(l_decrypted_raw));

      END;

      /

set serveroutput on

      

      DECLARE

       enc_val   RAW(2000);

       l_key     RAW(2000);

       l_key_len NUMBER := 128/8; -- convert bits to bytes

       l_mod     NUMBER := dbms_crypto.ENCRYPT_AES128+dbms_crypto.CHAIN_CBC+dbms_crypto.PAD_ZERO;

      BEGIN

        l_key :=  dbms_crypto.randombytes(l_key_len);

        enc_val :=  dbms_crypto.encrypt(utl_i18n.string_to_raw('4114-0113-1518-7114', 'AL32UTF8'), l_mod, l_key);

        dbms_output.put_line(enc_val);

      END;

      /

set serveroutput on

      

      DECLARE

       enc_val RAW(2000);

       l_key RAW(2000);

       l_key_len NUMBER :=  256/8; -- convert bits to bytes

       l_mod NUMBER := dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO;

      BEGIN

        l_key := dbms_crypto.randombytes(l_key_len);

        enc_val := dbms_crypto.encrypt(utl_raw.cast_to_raw(CONVERT('Morgan','AL32UTF8')), l_mod, l_key);

        dbms_output.put_line(enc_val);

      END;

      /

Overload 2 dbms_crypto.encrypt( dst IN OUT NOCOPY BLOB, src IN BLOB, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL);

TBD

Overload 3 dbms_crypto.encrypt( dst IN OUT NOCOPY BLOB, src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL);

TBD

HASH

Hash source data by cryptographic hash type

Overload 1 dbms_crypto.hash( src IN RAW, typ IN PLS_INTEGER) RETURN RAW;

set serveroutput on

      

      DECLARE

       l_credit_card_no VARCHAR2(19) := '4114-0113-1518-7114';

       l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);

       l_encrypted_raw RAW(2048);

      BEGIN

        dbms_output.put_line('CC:  ' || l_ccn_raw);

      

        l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, dbms_crypto.hash_md4);

        dbms_output.put_line('MD4: ' || l_encrypted_raw);

      

        l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, dbms_crypto.hash_md5);

        dbms_output.put_line('MD5: ' || l_encrypted_raw);

      

        l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, dbms_crypto.hash_sh1);

        dbms_output.put_line('SH1: ' || l_encrypted_raw);

      

        l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, dbms_crypto.hash_sh256);

        dbms_output.put_line('SH2-256: ' || l_encrypted_raw);

      

        l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, dbms_crypto.hash_sh384);

        dbms_output.put_line('SH2-384: ' || l_encrypted_raw);

      

        l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, dbms_crypto.hash_sh512);

        dbms_output.put_line('SH2-512: ' || l_encrypted_raw);

      END;

      /

Overload 2 dbms_crypto.hash( src IN BLOB, typ IN PLS_INTEGER) RETURN RAW;

TBD

Overload 3 dbms_crypto.hash( src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER) RETURN RAW;

DECLARE

       lRAW  RAW(512);

       lClob CLOB := 'Patient is showing signs of pizza box intolerance: Try an ODA';

      BEGIN

        dbms_output.put_line('Source:  ' || SUBSTR(lClob,1,128));

      

        lRAW := dbms_crypto.hash(lCLOB, dbms_crypto.hash_sh512);

        dbms_output.put_line('SH2-512: ' || lRAW);

      END;

      /

MAC

Message Authentication Code algorithms provide keyed message protection

Overload 1 dbms_crypto.mac( src IN RAW, typ IN PLS_INTEGER, key IN RAW) RETURN RAW;

set serveroutput on

      

      DECLARE

       l_credit_card_no VARCHAR2(19) := '4114-0113-1518-7114';

       l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);

       l_key     RAW(128) :=
      utl_raw.cast_to_raw('abcdefgh');

       l_encrypted_raw RAW(2048);

      BEGIN

        dbms_output.put_line('CC:  ' || l_ccn_raw);

        dbms_output.put_line('Key: ' || l_key);

      

        l_encrypted_raw := dbms_crypto.mac(l_ccn_raw, 1, l_key);

        dbms_output.put_line('MD5: ' || l_encrypted_raw);

      

        l_encrypted_raw := dbms_crypto.mac(l_ccn_raw, 2, l_key);

        dbms_output.put_line('SH1: ' || l_encrypted_raw);

      END;

      /

Overload 2 dbms_crypto.mac( src IN BLOB, typ IN PLS_INTEGER, key IN RAW) RETURN RAW;

TBD

Overload 3 dbms_crypto.mac( src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER, key IN RAW) RETURN RAW;

TBD

PKDECRYPT (new 21c)

Decrypts RAW data using a private key assisted with key algorithm and encryption algorithm and returns decrypted data dbms_crypto.pkDecrypt( src IN RAW, prv_key IN RAW, pubkey_alg IN BINARY_INTEGER, enc_alg IN BINARY_INTEGER) RETURN RAW;

TBD

PKENCRYPT (new 21c)

Encrypts RAW data using a public key assisted with key algorithm and encryption algorithm and returns encrypted data dbms_crypto.pkEncrypt( src IN RAW, pub_key IN RAW, pubkey_alg IN BINARY_INTEGER, enc_alg IN BINARY_INTEGER) RETURN RAW;

TBD

RANDOMBYTES

Returns a raw value containing a pseudo-random sequence of bytes dbms_crypto.randombytes(number_bytes PLS_INTEGER) RETURN RAW;

SELECT dbms_crypto.randombytes(1) FROM dual;

      SELECT LENGTH(dbms_crypto.randombytes(1)) FROM dual;

      

      SELECT dbms_crypto.randombytes(28) FROM dual;

      SELECT LENGTH(dbms_crypto.randombytes(28)) FROM dual;

      

      SELECT  dbms_crypto.randombytes(64) FROM dual;

      SELECT LENGTH(dbms_crypto.randombytes(64)) FROM dual;

RANDOMINTEGER

Returns a random BINARY_INTEGER dbms_crypto.randominteger RETURN BINARY_INTEGER;

SELECT dbms_crypto.randominteger FROM dual;

RANDOMNUMBER

Returns a random Oracle Number dbms_crypto.randomnumber RETURN NUMBER;

SELECT dbms_crypto.randomnumber FROM dual;

SIGN (new 21c)

Signs RAW data using a private key assisted with key algorithm and sign algorithm, and returns a signature dbms_crypto.sign( src IN RAW, prv_key IN RAW, pubkey_alg IN BINARY_INTEGER, sign_alg IN BINARY_INTEGER) RETURN RAW;

TBD

VERIFY (new 21c)

Verifies RAW data using the signature, public key assisted with key algorithm, and sign algorithm. It returns TRUE if the signature was verified dbms_crypto.verify( src IN RAW, sign IN RAW, pub_key IN RAW, pubkey_alg IN BINARY_INTEGER, sign_alg IN BINARY_INTEGER) RETURN BOOLEAN;

TBD