Appendix B: DATA_CRYPTO Package

The DBMS_CRYPTO package provides a comprehensive set of procedures and functions that will allow you to encrypt and decrypt data in the confines of the database. For most of the basic data types, such as VARCHAR2, dates, and numbers, you’ll have to convert the data to a RAW prior to invoking the DBMS_CRYPTO package. CLOB and BLOB encryption is natively supported but only through procedures. Also, the encryption key used must be a RAW and has to be the appropriate size for the encryption algorithm you’ll be using.

These are facts and not criticisms. The DBMS_CRYPTO package is implemented effectively and these requirements are, at most, inconvenient. Chapter 13 discusses relevant issues, such as storing encrypted data and the importance of the encryption key, and gives many examples of how to encrypt and decrypt data.

The DATA_CRYPTO package provided here is meant to serve two purposes. First, it simplifies the encryption and decryption calls by performing the data-type castings and algorithmic-based key sizing. The encryption and decryption programs are administered as functions so they can

be called within SQL statements. See Chapter 13 for examples.

The second purpose of DATA_CRYPTO is to serve as a template for implementing your specific encryption/decryption code. The code illustrates how to invoke DBMS_CRYPTO, and it implicitly offers suggestions for helper functions that you might administer. This should allow you to more easily encrypt and decrypt data that is important in supporting the wide-scale use of stored database encryption.

CREATE OR REPLACE PACKAGE data_crypto   AS   /* DATA_CRYPTO    * * This package is a utility that invokes procedures and functions in    * the default DBMS_CRYPTO package. This    * package provides functions for strings, numbers, * dates, CLOB, and BLOBs. Helper functions are also    * provided. Default encryption is AES 128-bit. Default    * hash is SHA. * */     ---------------------------------------------------------   --                  Encryption                         --   ---------------------------------------------------------       -- Returns integer value for encryption algorithm     -- The DES algorithm. Requires 8-byte key.   FUNCTION des       RETURN PLS_INTEGER; -- Triple DES using 3 distinct keys     FUNCTION des3       RETURN PLS_INTEGER;  -- Triple DES using 2 keys   FUNCTION des32      RETURN PLS_INTEGER; -- AES at 128-bit. Requires 16-byte key     FUNCTION aes       RETURN PLS_INTEGER; -- Stronger AES with at 192-bit key length (24-bytes)   FUNCTION aes192      RETURN PLS_INTEGER; -- Strongest AES at 256-bit key length (32-bytes)   FUNCTION aes256       RETURN PLS_INTEGER; -- Stream Cipher Algorithms     -- Key size is variable.   FUNCTION rc4       RETURN PLS_INTEGER; -- Set encryption algorithm     -- Stores value in global package variable     PROCEDURE set_algorithm (p_enc_algorithm IN PLS_INTEGER); -- return character representation of currently set algorithm     FUNCTION get_algorithm      RETURN VARCHAR2; -- Stores key as a global package variable     -- Key is sized based on algorithm passed or globally     -- defined algorithm     PROCEDURE setkey (     p_key            IN  VARCHAR2,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL); -- return RAW key of appropriate size based on algorithm     -- if algorithm is null, the global algorithm will be used.   FUNCTION getkey (     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW; -- encrypts VARCHAR2 and return VARCHAR2 data     -- calls encrypt with utl_raw casting      FUNCTION encrypt (     p_data           IN  VARCHAR2,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2; -- decrypts VARCHAR2 and return VARCHAR2 data   FUNCTION decrypt (     p_data           IN  VARCHAR2,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2; -- Accept string data and return RAW output     -- RAW output is not interpreted by database     -- and is therefore more suitable for storage      FUNCTION encrypt_char (     p_data           IN  VARCHAR2,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW; -- Accept RAW data and return as VARCHAR2     -- This is the complimentary function for encrypt_char      FUNCTION decrypt_char (     p_data           IN  RAW,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2; -- encrypts number and return raw data      FUNCTION encrypt_number (     p_data           IN  NUMBER,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW; -- decrypts raw and returns number data      FUNCTION decrypt_number (     p_data           IN  RAW,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN NUMBER; -- encrypts date and return raw data      FUNCTION encrypt_date (     p_data           IN  DATE,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW; -- decrypts raw and returns data as date     FUNCTION decrypt_date (     p_data           IN  RAW,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN DATE; -- encrypts a CLOB and return value as CLOB -- done as function for performing SQL updates     FUNCTION encrypt (     p_data           IN  CLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN CLOB; -- decrypts a CLOB and return value as CLOB     -- done as function for performing SQL updates    FUNCTION decrypt (     p_data           IN  CLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN CLOB; -- encrypts a BLOB and return value as BLOB     -- done as function for performing SQL updates     FUNCTION encrypt (     p_data           IN  BLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN BLOB; -- decrypts a BLOB and return value as BLOB     -- done as function for performing SQL updates     FUNCTION decrypt (     p_data           IN  BLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN BLOB; ---------------------------------------------------------   --                     HASHING                         --   ---------------------------------------------------------        -- returns integer value for hashing algorithm    FUNCTION md4      RETURN PLS_INTEGER;   FUNCTION md5      RETURN PLS_INTEGER;   FUNCTION sha      RETURN PLS_INTEGER;   -- sets global hashing algorithm    PROCEDURE set_hash_algorithm (       p_hash_algorithm  IN  PLS_INTEGER);   -- return global hashing algorithm as string    FUNCTION get_hash_algorithm        RETURN VARCHAR2;   -- Computes hash and return value as RAW       -- Uses p_hash_algorithm if passed, else       -- uses the global variable     FUNCTION hash2raw (     p_data            IN  VARCHAR2,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW;   -- Computes hash and return value as VARCHAR2     -- Uses p_hash_algorithm if passed, else      -- uses the global variable    FUNCTION HASH (     p_data            IN  VARCHAR2,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2; ---------------------------------------------------------     --       Hashed Message Authentication Codes           --   ---------------------------------------------------------      -- returns integer value for hashing algorithm    FUNCTION hmac_md5          RETURN PLS_INTEGER;   FUNCTION hmac_sha        RETURN PLS_INTEGER;   -- returns string value for current HMAC algorithm     FUNCTION get_hmac_algorithm         RETURN VARCHAR2;   -- Sets global HMAC algorithm to p_hash_algorithm       PROCEDURE set_hmac_algorithm (     p_hash_algorithm  IN  PLS_INTEGER);   -- Computes hashed message authentication code.   -- Returns value as RAW     -- Uses p_hash_algorithm if passed, else     -- uses the global hmac variable     FUNCTION mac2raw (     p_data            IN  VARCHAR2,     p_key             IN  VARCHAR2 DEFAULT NULL,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW;   -- Computes hashed message authentication code.   -- Returns value as VARCHAR2     -- Uses p_hash_algorithm if passed, else   -- uses the global hmac variable     FUNCTION mac (     p_data            IN  VARCHAR2,     p_key             IN  VARCHAR2 DEFAULT NULL,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2; END; /     CREATE OR REPLACE PACKAGE BODY data_crypto   AS   ---------------------------------------------------------   --   Integer values for encryption algorithms   --   Values are sum of base encryption algorithm, --   the block modifier, and the padding scheme   ---------------------------------------------------------        -- 56-bit DES     g_des       CONSTANT PLS_INTEGER       :=   dbms_crypto.encrypt_des           + dbms_crypto.chain_cbc           + dbms_crypto.pad_pkcs5;  -- Triple DES using 3 distinct keys      g_des3      CONSTANT PLS_INTEGER       :=   dbms_crypto.encrypt_3des_2key           + dbms_crypto.chain_cbc           + dbms_crypto.pad_pkcs5;  -- Triple DES using 2 keys    g_des32     CONSTANT PLS_INTEGER       :=   dbms_crypto.encrypt_3des           + dbms_crypto.chain_cbc           + dbms_crypto.pad_pkcs5;   -- AES at 128-bit. Requires 16-byte key     g_aes       CONSTANT PLS_INTEGER        :=   dbms_crypto.encrypt_aes           + dbms_crypto.chain_cbc           + dbms_crypto.pad_pkcs5;   -- Stronger AES with at 192-bit key length (24-bytes)   g_aes192    CONSTANT PLS_INTEGER         :=   dbms_crypto.encrypt_aes192         + dbms_crypto.chain_cbc           + dbms_crypto.pad_pkcs5;   -- Strongest AES at 256-bit key length (32-bytes)   g_aes256    CONSTANT PLS_INTEGER         :=   dbms_crypto.encrypt_aes256          + dbms_crypto.chain_cbc           + dbms_crypto.pad_pkcs5;   -- Stream Cipher Algorithms   -- Key size is variable.   g_rc4       CONSTANT PLS_INTEGER                                      := dbms_crypto.encrypt_rc4;   -- define the default encryption algorithm     -- modify this value to change your defaults     g_enc_algorithm      PLS_INTEGER      := g_aes;   -- create a global variable for holding key     -- RC4 has no key size limit      g_key                VARCHAR2 (32767); ---------------------------------------------------------   --                 HASHING   --   Integer values for hashing and HMAC algorithms   ---------------------------------------------------------     -- Hash Algorithms   g_md4       CONSTANT PLS_INTEGER   := dbms_crypto.hash_md4;   g_md5       CONSTANT PLS_INTEGER   := dbms_crypto.hash_md5;   g_sha       CONSTANT PLS_INTEGER   := dbms_crypto.hash_sh1;   -- globally defined (default) hashing algorithm       g_hash_algorithm     PLS_INTEGER      := g_sha;   -- MAC Functions     g_hmac_md5  CONSTANT PLS_INTEGER   := dbms_crypto.hmac_md5;   g_hmac_sha  CONSTANT PLS_INTEGER   := dbms_crypto.hmac_sh1;   -- globally defined (default) hashing algorithm     g_hmac_algorithm     PLS_INTEGER      := g_hmac_sha; ---------------------------------------------------------   --   Functions for returning the integer value   --   of respective algorithms   ---------------------------------------------------------      FUNCTION des       RETURN PLS_INTEGER     AS    BEGIN       RETURN g_des;  END; ---------------------------------------------------------    FUNCTION des3       RETURN PLS_INTEGER     AS     BEGIN       RETURN g_des3;  END; ---------------------------------------------------------     FUNCTION des32        RETURN PLS_INTEGER     AS     BEGIN       RETURN g_des32;  END; ---------------------------------------------------------     FUNCTION aes      RETURN PLS_INTEGER     AS     BEGIN     RETURN g_aes;  END; ---------------------------------------------------------    FUNCTION aes192        RETURN PLS_INTEGER     AS     BEGIN       RETURN g_aes192;   END; ---------------------------------------------------------    FUNCTION aes256       RETURN PLS_INTEGER     AS     BEGIN      RETURN g_aes256;  END; ---------------------------------------------------------     FUNCTION rc4       RETURN PLS_INTEGER     AS     BEGIN   RETURN g_rc4;   END; ---------------------------------------------------------    FUNCTION get_algorithm      RETURN VARCHAR2     -- returns string value for current encryption algorithm    AS         l_enc_algorithm  VARCHAR2 (6); BEGIN       CASE          WHEN g_enc_algorithm = g_des          THEN              l_enc_algorithm := 'DES';     WHEN g_enc_algorithm = g_des32          THEN              l_enc_algorithm := 'DES32';     WHEN g_enc_algorithm = g_des3         THEN       l_enc_algorithm := 'DES3';     WHEN g_enc_algorithm = g_aes          THEN           l_enc_algorithm := 'AES';     WHEN g_enc_algorithm = g_aes192         THEN           l_enc_algorithm := 'AES192';     WHEN g_enc_algorithm = g_aes256          THEN             l_enc_algorithm := 'AES256';     WHEN g_enc_algorithm = g_rc4        THEN            l_enc_algorithm := 'RC4';     END CASE;     RETURN l_enc_algorithm;   END; ---------------------------------------------------------     PROCEDURE set_algorithm (p_enc_algorithm IN PLS_INTEGER)   -- sets global encryption algorithm to p_enc_algorithm     -- check to ensure algorithm is valid     AS      l_err_msg  VARCHAR2 (200);   BEGIN        IF (p_enc_algorithm IN                 (g_des,            g_des3,            g_des32,            g_aes,            g_aes192,            g_aes256,            g_rc4))     THEN        g_enc_algorithm := p_enc_algorithm;     ELSE        l_err_msg :=            'Unsupported encryption algorithm: '         || 'Use one of the following DATA_CRYPTO variables: '         || 'DES, DES3, DES32, AES, AES192, AES256, RC4';       raise_application_error (-20001, l_err_msg);     END IF;   END; ---------------------------------------------------------     PROCEDURE setkey (     p_key            IN  VARCHAR2,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)   -- sets global encryption key to p_key. Key is sized      -- according to p_enc_algorithm or global algorithm      AS      BEGIN          -- check pad key and store        g_key := getkey (p_key, p_enc_algorithm);   END; ---------------------------------------------------------    FUNCTION get_key_size (p_enc_algorithm IN PLS_INTEGER)     RETURN PLS_INTEGER     -- return key size in bytes based on algorithm used    AS       l_blocks  PLS_INTEGER := 2; BEGIN       -- need case statement       CASE           WHEN NVL (p_enc_algorithm, g_enc_algorithm) = g_des         THEN           l_blocks := 1;     WHEN NVL (p_enc_algorithm, g_enc_algorithm) = g_des32           THEN            l_blocks := 3;     WHEN NVL (p_enc_algorithm, g_enc_algorithm) = g_des3         THEN           l_blocks := 3;     WHEN NVL (p_enc_algorithm, g_enc_algorithm) = g_aes         THEN            l_blocks := 2;     WHEN NVL (p_enc_algorithm, g_enc_algorithm) = g_aes192         THEN            l_blocks := 3;     WHEN NVL (p_enc_algorithm, g_enc_algorithm) = g_aes256         THEN           l_blocks := 4;     -- RC4 does not require key padding         END CASE;     RETURN l_blocks * 8;   END; ---------------------------------------------------------     FUNCTION padkey (p_key IN VARCHAR2)     RETURN VARCHAR2    -- Pads p_key out 8-bytes. Used to increase Key size     -- Padding to the right to preserve any uniqueness     -- presented with the key     AS     BEGIN          RETURN RPAD (p_key,                  (TRUNC (LENGTHB (p_key) / 8) + 1) * 8,                  CHR (0));  END; ---------------------------------------------------------     FUNCTION getkey (     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW      -- return raw key of size congruent with algorithm     -- only work on key if p_key is not null     AS        -- Key size changes based on algorithm       -- Need to compute key size based on the algorithm being used       -- Key size only restricted to block ciphers      l_key_size       PLS_INTEGER;   l_key            VARCHAR2 (32767) := NVL (p_key, g_key);   -- algorithm is passed. Actual determination done in getKeySize.   l_enc_algorithm  PLS_INTEGER;  BEGIN         -- if using RC4, no padding is required       IF (NVL (p_enc_algorithm, g_enc_algorithm) = rc4)   THEN           RETURN NVL (UTL_RAW.cast_to_raw (p_key), g_key);   END IF;   -- need to contract or expand key based on algorithm       l_key_size := get_key_size (p_enc_algorithm);   -- key may be too long. Truncate using first bytes        l_key := SUBSTR (l_key, 1, l_key_size);   -- if key is too short, we have to pad       WHILE LENGTH (l_key) < l_key_size       LOOP         l_key := padkey (l_key);   END LOOP;   -- last loop iteration may have made key too large       -- need to ensure key size is not too large.   -- also convert result to RAW        RETURN UTL_RAW.cast_to_raw (SUBSTR (l_key, 1,                                       l_key_size));   END; ---------------------------------------------------------     FUNCTION encrypt_char (     p_data           IN  VARCHAR2,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW   -- takes data and key in varchar2 and returns varchar2 encrytped data      -- Key and algorithm are passed, or default global value is used.   AS        -- convert data to RAW for crytpo algorithm        l_input      RAW (32767)                   := UTL_RAW.cast_to_raw (p_data);     l_algorithm  PLS_INTEGER                    := NVL (p_enc_algorithm, g_enc_algorithm);   BEGIN         RETURN dbms_crypto.encrypt                                (src    => l_input,                        typ    => l_algorithm,                        KEY    => getkey                                        (p_key,                                        l_algorithm));   END; ---------------------------------------------------------     FUNCTION decrypt_char (     p_data           IN  RAW,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2     -- takes encrypted varchar2 data and plaintext varchar2 key    -- and returns decrypted varchar2 data.  -- Key and algorithm are passed, or default global value is used.  AS        -- convert data to RAW for crytpo algorithm        l_algorithm  PLS_INTEGER             := NVL (p_enc_algorithm, g_enc_algorithm); BEGIN      RETURN UTL_RAW.cast_to_varchar2                      (dbms_crypto.decrypt                                     (src    => p_data,                         typ    => l_algorithm,                         KEY    => getkey                                                     (p_key,                                        l_algorithm))); END; ---------------------------------------------------------     FUNCTION encrypt (     p_data           IN  VARCHAR2,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2     -- Takes plaintext data and returns encrypted data as varchar2     -- Key and algorithm are passed, or default global value is used.  AS     BEGIN     RETURN UTL_RAW.cast_to_varchar2                            (encrypt_char (p_data,                                   p_key,                                   p_enc_algorithm));   END; ---------------------------------------------------------     FUNCTION decrypt (     p_data           IN  VARCHAR2,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2     -- takes encrypted varchar2 data     -- and returns decrypted varchar2 data.  -- Key and algorithm are passed, or default global value is used.  AS      -- convert data to RAW for crytpo algorithm     BEGIN       RETURN (decrypt_char                                     (UTL_RAW.cast_to_raw (p_data),                       p_key,                       p_enc_algorithm));   END; ---------------------------------------------------------     FUNCTION encrypt_number (     p_data           IN  NUMBER,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW     -- takes data and key in number format and returns raw encrytped data     -- Key and algorithm are passed, or default global value is used.  AS       -- convert data to RAW for crytpo algorithm        l_input      RAW (32767)                 := UTL_RAW.cast_from_number (p_data);    l_algorithm  PLS_INTEGER                        := NVL (p_enc_algorithm, g_enc_algorithm); BEGIN       RETURN dbms_crypto.encrypt                                  (src    => l_input,                      typ    => l_algorithm,                      KEY    => getkey                                                            (p_key,                                        l_algorithm));   END; ---------------------------------------------------------     FUNCTION decrypt_number (     p_data           IN  RAW,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN NUMBER     -- takes encrypted raw data and plaintext varchar2 key     -- and returns decrypted number data.  -- Key and algorithm are passed, or default global value is used.  AS         -- convert data to RAW for crytpo algorithm       l_algorithm  PLS_INTEGER              := NVL (p_enc_algorithm, g_enc_algorithm);  BEGIN          RETURN UTL_RAW.cast_to_number                  (dbms_crypto.decrypt                              (src    => p_data,                         typ    => l_algorithm,                         KEY    => getkey                                                       (p_key,                                        l_algorithm)));   END; ---------------------------------------------------------     FUNCTION encrypt_date (     p_data           IN  DATE,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW    -- takes date and key and returns raw encrytped data    -- Key and algorithm are passed, or default global value is used.  AS        -- convert data to RAW for crytpo algorithm       l_input      RAW (32767)         := UTL_RAW.cast_to_raw (TO_CHAR (p_data));     l_algorithm  PLS_INTEGER               := NVL (p_enc_algorithm, g_enc_algorithm); BEGIN       RETURN dbms_crypto.encrypt                                       (src    => l_input,                        typ    => l_algorithm,                        KEY    => getkey                                                        (p_key,                                        l_algorithm)); END; ---------------------------------------------------------     FUNCTION decrypt_date (     p_data           IN  RAW,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN DATE     -- takes encrypted raw data and plaintext varchar2 key   -- and returns decrypted date.   -- Key and algorithm are passed, or default global value is used.   AS        -- convert data to RAW for crytpo algorithm        l_algorithm  PLS_INTEGER             := NVL (p_enc_algorithm, g_enc_algorithm);   BEGIN        RETURN TO_DATE                    (UTL_RAW.cast_to_varchar2                    (dbms_crypto.decrypt                                   (src    => p_data,                         typ    => l_algorithm,                         KEY    => getkey                                                             (p_key,                                        l_algorithm))));   END; ---------------------------------------------------------     FUNCTION encrypt (     p_data           IN  CLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN CLOB     -- encrypts a CLOB and return value as CLOB     -- done as function for performing SQL updates    AS        l_encrypted_blob  BLOB;     l_encrypted_clob  CLOB;     l_lang            NUMBER := DBMS_LOB.default_lang_ctx;     l_warning         NUMBER;     l_t_offset        NUMBER := 1;     l_src_offset      NUMBER := 1;  BEGIN          DBMS_LOB.createtemporary (l_encrypted_blob, TRUE);     DBMS_LOB.createtemporary (l_encrypted_clob, TRUE);     dbms_crypto.encrypt (dst    => l_encrypted_blob,                          src    => p_data,                          typ    => NVL (p_enc_algorithm,                                         g_enc_algorithm),                          KEY    => getkey (p_key,                                            p_enc_algorithm));     DBMS_LOB.converttoclob (l_encrypted_clob,                             l_encrypted_blob,                             DBMS_LOB.lobmaxsize,                             l_t_offset,                             l_src_offset,                             DBMS_LOB.default_csid,                             l_lang,                             l_warning);     RETURN l_encrypted_clob;   END; ---------------------------------------------------------     FUNCTION decrypt (     p_data           IN  CLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN CLOB    -- decrypts a CLOB and return value as CLOB     -- done as function for performing SQL updates     AS         l_encrypted_blob  BLOB;     l_decrypted_blob  BLOB;     l_decrypted_clob  CLOB;     l_lang            NUMBER := DBMS_LOB.default_lang_ctx;     l_warning         NUMBER;     l_t_offset        NUMBER := 1;     l_src_offset      NUMBER := 1;  BEGIN         DBMS_LOB.createtemporary (l_encrypted_blob, TRUE);     DBMS_LOB.createtemporary (l_decrypted_blob, TRUE);     DBMS_LOB.createtemporary (l_decrypted_clob, TRUE);     DBMS_LOB.converttoblob (l_encrypted_blob,                             p_data,                             DBMS_LOB.lobmaxsize,                             l_t_offset,                             l_src_offset,                             DBMS_LOB.default_csid,                             l_lang,                             l_warning);     dbms_crypto.decrypt (dst    => l_decrypted_blob,                          src    => l_encrypted_blob,                          typ    => NVL (p_enc_algorithm,                                         g_enc_algorithm),                          KEY    => getkey (p_key,                                            p_enc_algorithm));     l_t_offset := 1;     l_src_offset := 1;     DBMS_LOB.converttoclob (l_decrypted_clob,                             l_decrypted_blob,                             DBMS_LOB.lobmaxsize,                             l_t_offset,                             l_src_offset,                             DBMS_LOB.default_csid,                             l_lang,                             l_warning);     RETURN l_decrypted_clob;   END; ---------------------------------------------------------    FUNCTION encrypt (     p_data           IN  BLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN BLOB    -- encrypts a BLOB and return value as BLOB     -- done as function for performing SQL updates    AS        l_encrypted_blob  BLOB;  BEGIN         DBMS_LOB.createtemporary (l_encrypted_blob, TRUE);     dbms_crypto.encrypt (dst    => l_encrypted_blob,                          src    => p_data,                          typ    => NVL (p_enc_algorithm,                                         g_enc_algorithm),                          KEY    => getkey (p_key,                                            p_enc_algorithm));     RETURN l_encrypted_blob;   END; ---------------------------------------------------------    FUNCTION decrypt (     p_data           IN  BLOB,     p_key            IN  VARCHAR2 DEFAULT NULL,     p_enc_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN BLOB     -- decrypts a BLOB and return value as BLOB     -- done as function for performing SQL updates     AS        l_decrypted_blob  BLOB;  BEGIN         DBMS_LOB.createtemporary (l_decrypted_blob, TRUE);     dbms_crypto.decrypt (dst    => l_decrypted_blob,                          src    => p_data,                          typ    => NVL (p_enc_algorithm,                                         g_enc_algorithm),                          KEY    => getkey (p_key,                                            p_enc_algorithm));     RETURN l_decrypted_blob;   END; ---------------------------------------------------------   --                     HASHING                         --   ---------------------------------------------------------       -- Following functions return integer value of associated     -- hashing algorithm   ---------------------------------------------------------   FUNCTION md4       RETURN PLS_INTEGER      AS     BEGIN        RETURN g_md4;   END; ---------------------------------------------------------     FUNCTION md5       RETURN PLS_INTEGER     AS     BEGIN    RETURN g_md5;  END; ---------------------------------------------------------    FUNCTION sha       RETURN PLS_INTEGER    AS     BEGIN    RETURN g_sha;  END; ---------------------------------------------------------    FUNCTION get_hash_algorithm      RETURN VARCHAR2     -- return string representation of current hash algorithm    AS       l_hash_algorithm  VARCHAR2 (3);  BEGIN        CASE           WHEN g_hash_algorithm = g_md4            THEN            l_hash_algorithm := 'MD4';      WHEN g_hash_algorithm = g_md5          THEN            l_hash_algorithm := 'MD5';      WHEN g_hash_algorithm = g_sha           THEN             l_hash_algorithm := 'SHA';    END CASE;  RETURN l_hash_algorithm; END; ---------------------------------------------------------     PROCEDURE set_hash_algorithm (     p_hash_algorithm  IN  PLS_INTEGER)  -- sets global hashing algorithm to p_hash_algorithm    AS        l_err_msg  VARCHAR2 (50);  BEGIN       -- check to ensure algorithm is valid       IF (p_hash_algorithm IN (g_md4, g_md5, g_sha))  THEN         g_hash_algorithm := p_hash_algorithm;  ELSE         l_err_msg :=            'Unsupported Hash algorithm: '         || 'Use one of the following DATA_CRYPTO variables: '         || 'MD4, MD5, SHA';       raise_application_error (-20002, l_err_msg);     END IF;   END; ---------------------------------------------------------     FUNCTION hash2raw (     p_data            IN  VARCHAR2,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW    -- Computes hash and return value as RAW    -- Uses p_hash_algorithm if passed, else     -- uses the global variable     AS       l_input  RAW (32767) := UTL_RAW.cast_to_raw (p_data);  BEGIN       RETURN dbms_crypto.HASH (src    => l_input,                             typ    => NVL                                            (p_hash_algorithm,                                            g_hash_algorithm));   END; ---------------------------------------------------------     FUNCTION HASH (     p_data            IN  VARCHAR2,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2     -- Computes hash and return value as VARCHAR2     -- Delegates call to hash2raw function     AS  BEGIN           RETURN UTL_RAW.cast_to_varchar2                                                         (hash2raw (p_data,                                            p_hash_algorithm));   END; ---------------------------------------------------------   --            Hashed Message Authentication Codes      --   ---------------------------------------------------------       -- Following functions return integer value of associated   -- hmac algorithms   ---------------------------------------------------------     FUNCTION hmac_sha       RETURN PLS_INTEGER     AS      BEGIN       RETURN g_hmac_sha; END; ---------------------------------------------------------     FUNCTION hmac_md5       RETURN PLS_INTEGER     AS     BEGIN       RETURN g_hmac_md5;  END; ---------------------------------------------------------     FUNCTION get_hmac_algorithm        RETURN VARCHAR2     -- return string representation of current HMAC algorithm     AS        l_hash_algorithm  VARCHAR2 (3); BEGIN       CASE            WHEN g_hash_algorithm = g_hmac_md5          THEN            l_hash_algorithm := 'HMAC_MD5';      WHEN g_hash_algorithm = g_hmac_sha          THEN           l_hash_algorithm := 'HMAC_SHA';   END CASE;   RETURN l_hash_algorithm; END; ---------------------------------------------------------     PROCEDURE set_hmac_algorithm (     p_hash_algorithm  IN  PLS_INTEGER)  -- sets global hashing algorithm to p_hash_algorithm     AS       l_err_msg  VARCHAR2 (50);  BEGIN        -- check to ensure algorithm is valid       IF (p_hash_algorithm IN (g_hmac_md5, g_hmac_sha))    THEN          g_hmac_algorithm := p_hash_algorithm;    ELSE           l_err_msg :=            'Unsupported HMAC algorithm: '         || 'Use one of the following DATA_CRYPTO variables: '         || 'HMAC_MD5, HMAC_SHA';       raise_application_error (-20002, l_err_msg);    END IF;  END; ---------------------------------------------------------     FUNCTION mac2raw (     p_data            IN  VARCHAR2,     p_key             IN  VARCHAR2 DEFAULT NULL,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN RAW     -- Computes hash message authentication code.  -- Returns value as RAW     -- Uses p_hash_algorithm if passed, else    -- uses the global hmac variable     AS        l_input      RAW (32767)                             := UTL_RAW.cast_to_raw (p_data);     l_algorithm  PLS_INTEGER                                         := NVL (p_hash_algorithm, g_hmac_algorithm); BEGIN       IF (l_algorithm NOT IN (g_hmac_sha, g_hmac_md5))    THEN           raise_application_error                                  (-20003,                               'Unsupported HMAC algorithm.');    END IF;    RETURN dbms_crypto.mac (src    => l_input,                            typ    => l_algorithm,                            KEY    => getkey (p_key));  END; ---------------------------------------------------------     FUNCTION mac (     p_data            IN  VARCHAR2,     p_key             IN  VARCHAR2 DEFAULT NULL,     p_hash_algorithm  IN  PLS_INTEGER DEFAULT NULL)     RETURN VARCHAR2     -- Computes MAC and returns value as VARCHAR2     -- Delegates call to mac2raw function     AS    BEGIN          RETURN UTL_RAW.cast_to_varchar2                                                      (mac2raw (p_data,                                            p_key,                                            p_hash_algorithm));  END; ---------------------------------------------------------  END; /