Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle dbms_crypto tips



Oracle Database Tips by Donald Burleson

Oracle DBMS_CRYPTO code listings

Oracle DBMS_CRYPTO package allows a user to encrypt and decrypt Oracle data. Oracle DBMS_CRYPTO supports the National Institute of Standards and Technology (NIST) approved Advanced Encryption Standard (AES) encryption algorithm. Oracle DBMS_CRYPTO also supports Data Encryption Standard (DES), Triple DES (3DES, 2-key and 3-key), MD5, MD4, and SHA-1 cryptographic hashes, and MD5 and SHA-1 Message Authentication Code (MAC).

DBMS_CRYPTO can encrypt most common Oracle datatypes including RAW and large objects (LOBs), as well as BLOBs and CLOBs.


dbms_crypto Code Listing - A simple encryption function using dbms_crypto

1 create or replace function get_enc_val
2 (
3 p_in in varchar2,
4 p_key in raw
5 )
6 return raw is
7 l_enc_val raw (2000);
8 l_mod number := dbms_crypto.ENCRYPT_AES128
9 + dbms_crypto.CHAIN_CBC
10 + dbms_crypto.PAD_PKCS5;
11 begin
12 l_enc_val := dbms_crypto.encrypt
13 (
14 UTL_I18N.STRING_TO_RAW
15 (p_in, 'AL32UTF8'),
16 l_mod,
17 p_key
18 );
19 return l_enc_val;
20* end;

dbms_crypto Code Listing - A simple decryption function using dbms_crypto

1 create or replace function get_dec_val
2 (
3 p_in in raw,
4 p_key in raw
5 )
6 return varchar2
7 is
8 l_ret varchar2 (2000);
9 l_dec_val raw (2000);
10 l_mod number := dbms_crypto.ENCRYPT_AES128
11 + dbms_crypto.CHAIN_CBC
12 + dbms_crypto.PAD_PKCS5;
13 begin
14 l_dec_val := dbms_crypto.decrypt
15 (
16 p_in,
17 l_mod,
18 p_key
19 );
20 l_ret:= UTL_I18N.RAW_TO_CHAR
21 (l_dec_val, 'AL32UTF8');
22 return l_ret;
23* end;


DBMS_CRYPTO

Dbms_crypto provides a modern and effectual ability to encrypt and decrypt data using any of the following cryptographic algorithms:

  • Data Encryption Standard (DES)

  • Triple DES (3DES, 2-key and 3-key)

  • Advanced Encryption Standard (AES)

  • MD5, MD4, and SHA-1 cryptographic hashes

  • MD5 and SHA-1 Message Authentication Code (MAC)

Be aware that effective dbms_crypto usage requires a general level of security familiarity and/or expertise. Key management is entirely programmatic, thus the application, or caller of dbms_crypto, must supply the encryption key. Furthermore, the application is responsible for storing and retrieving keys securely. Common options for applications storing keys include within the database, on the operating system, and user self-managed. Of course, one can always rely instead upon Oracle's encrypted tables and tablespaces and their inherently automatic key management.

Dbms_obfuscation_toolkit usage should be replaced by the newer dbms_crypto package, available in 10g and later, which offers more modern and secure cryptographic algorithms as well as support for more database data types.

There are a few enumerated constants that one must know to use this package:

-- Hash Functions

HASH_MD4 CONSTANT PLS_INTEGER := 1;

HASH_MD5 CONSTANT PLS_INTEGER := 2;

HASH_SH1 CONSTANT PLS_INTEGER := 3;

-- MAC Functions

HMAC_MD5 CONSTANT PLS_INTEGER := 1;

HMAC_SH1 CONSTANT PLS_INTEGER := 2;

-- Block Cipher Algorithms

ENCRYPT_DES CONSTANT PLS_INTEGER := 1; -- 0x0001

ENCRYPT_3DES_2KEY CONSTANT PLS_INTEGER := 2; -- 0x0002

ENCRYPT_3DES CONSTANT PLS_INTEGER := 3; -- 0x0003

ENCRYPT_AES CONSTANT PLS_INTEGER := 4; -- 0x0004

ENCRYPT_PBE_MD5DES CONSTANT PLS_INTEGER := 5; -- 0x0005

ENCRYPT_AES128 CONSTANT PLS_INTEGER := 6; -- 0x0006

ENCRYPT_AES192 CONSTANT PLS_INTEGER := 7; -- 0x0007

ENCRYPT_AES256 CONSTANT PLS_INTEGER := 8; -- 0x0008

-- Block Cipher Chaining Modifiers

CHAIN_CBC CONSTANT PLS_INTEGER := 256; -- 0x0100

CHAIN_CFB CONSTANT PLS_INTEGER := 512; -- 0x0200

CHAIN_ECB CONSTANT PLS_INTEGER := 768; -- 0x0300

CHAIN_OFB CONSTANT PLS_INTEGER := 1024; -- 0x0400

-- Block Cipher Padding Modifiers

PAD_PKCS5 CONSTANT PLS_INTEGER := 4096; -- 0x1000

PAD_NONE CONSTANT PLS_INTEGER := 8192; -- 0x2000

PAD_ZERO CONSTANT PLS_INTEGER := 12288; -- 0x3000

PAD_ORCL CONSTANT PLS_INTEGER := 16384; -- 0x4000

-- Stream Cipher Algorithms

ENCRYPT_RC4 CONSTANT PLS_INTEGER := 129; -- 0x0081

-- Convenience Constants for Block Ciphers

DES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_DES

+ CHAIN_CBC

+ PAD_PKCS5;

DES3_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_3DES

+ CHAIN_CBC

+ PAD_PKCS5;

AES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_AES

+ CHAIN_CBC

+ PAD_PKCS5;

Below are the dbms_crypto procedures and functions but remember, security and especially this package are not for novices or security neophytes. If one loses a key or improperly implements any factors, the data will almost surely be unrecoverable.

Decrypt is an overloaded procedure for decrypting the data with the following parameters and defaults.

Argument

Type

In / Out

Default Value

DS

BLOB | CLOB

IN | OUT

SRC

BLOB

IN

TYP

PLS_INTEGER

IN

KEY

RAW

IN

IV

RAW

IN

NULL

Table 6.20: Decrypt Parameters

Decrypt is also an overloaded function for decrypting the date with the following parameters and defaults, returning a RAW value.

Argument

Type

In / Out

Default Value

SRC

BLOB

IN

TYP

PLS_INTEGER

IN

KEY

RAW

IN

IV

RAW

IN

NULL

Table 6.21: Additional Decrypt Parameters

Encrypt is an overloaded procedure for encrypting the data with the following parameters and defaults.

Argument

Type

In / Out

Default Value

DST

BLOB | CLOB

IN | OUT

SRC

BLOB

IN

TYP

PLS_INTEGER

IN

KEY

RAW

IN

IV

RAW

IN

NULL

Table 6:22: Encrypt Parameters

Encrypt is also an overloaded function for encrypting the data with the following parameters and defaults, returning a RAW value.

Argument

Type

In / Out

Default Value

SRC

BLOB

IN

TYP

PLS_INTEGER

IN

KEY

RAW

IN

IV

RAW

IN

NULL

Table 6.23: Additional Encrypt Parameters

Hash is a function that accepts an input string and returns a unique identifier based upon the value as a RAW value.

Argument

Type

In / Out

Default Value

SRC

RAW |BLOB | CLOB

IN

TYP

PLS_INTEGER

IN

Table 6.24: Hash Parameters

MAC for message authentication code is similar to hash, but it is based off a supplied key as well as the data. It also returns the result via a RAW value.

Argument

Type

In / Out

Default Value

SRC

RAW |BLOB | CLOB

IN

TYP

PLS_INTEGER

IN

KEY

RAW

IN

Table 6.25: Mac Parameters

The remaining functions provide simple random number generation, and include:

DBMS_CRYPTO.RANDOMBYTES (number_bytes IN POSITIVE) RETURN RAW;

DBMS_CRYPTO.RANDOMINTEGER RETURN BINARY_INTEGER;

DBMS_CRYPTO.RANDOMNUMBER RETURN NUMBER;

 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 

Oracle has provided the DBMS_CRYPTO package for data encryption starting from the version 10gR1 as an attempt to replace its predecessor, the DBMS_OBFUSCATION_TOOLKIT package. This package acts as a PL/SQL interface to encrypt and decrypt the data that are stored in the database tables. This package supports much industry-standardized encryption and hashing algorithm like DES, AES, MD5, etc. Advanced Encryption Standard or AES has been approved by the National Institutes of Standards and Technology (NIST) to replace the Data Encryption Standard (DES) algorithm.

 

The DBMS_OBFUSCATION_TOOLKIT package is still available in the Oracle version 12c, although Oracle recommends us to start using the DBMS_CRYPTO as the predecessor’s capabilities are limited compared to this new package.

 

The below tabular column describes the differences between the two cryptographic packages in Oracle.

 

S. No.

Description

CRYPTO

OBFUSCATION TOOLKIT

1.       

Cryptographic algorithms

This procedure supports the DES encryption methods along with the newer encryption algorithm, AES. They are DES, 3DES, AES, RC4, 3DES_2KEY.

This procedure supports only DES encryption methods, which is not widely used by many organizations. They are DES and 3DES.

 

2.       

Ciphering methods.

 

1)      Block ciphering process performs encryption on blocks of data.

 

2)      Stream ciphering encrypts data in any form.

This procedure supports both stream and block ciphering.

This procedure supports only block ciphering, thus limiting its usage across all types of data.

3.       

Hash algorithms – Modern secure algorithms for data integrity.

This procedure supports MD4 (Message Digest), MD5, SHA-1 (Secure Hash Algorithm), SHA-2 (SHA-256, SHA-384, SHA-512).

This procedure supports only MD5 (Message digest) hash algorithm and no modern, secure algorithms like SHA-1, SHA-2, etc.

4.       

Keyed hash (MAC) algorithms – Message Authentication Code allows a hashed value of the message to be transmitted so that it is compared at the receiving end to check for the message integrity using a key.

This procedure supports HMAC_MD5, HMAC_SH1, HMAC_SH256, HMAC_SH384, HMAC_SH512 algorithms.

This procedure does not support any MAC creation.

5.       

LOB support

This procedure supports LOB data types in their native format.

This procedure can support LOB data types only when they are converted to RAW format. Thus, resulting in code complexity in applications.

6.       

Padding – Extra data have to be padded to make its length a multiple when we want to encrypt it using block ciphers.

This procedure supports PAD_PKCS5 (Public Key Cryptographic Standard -5), PAD_ZERO, PAD_NONE types.

This procedure needs to pad explicitly which is not considered as a secure method.

 

The DBMS_CRYPTO package replaces the DBMS_OBFUSCATION_TOOLKIT package by providing support for a range of new secure algorithms with easy usage. The algorithms 3DES_2KEY and MD4 are only provided for backward compatibility and the algorithms 3DES, AES, MD-5, SHA-1, and SHA-2 provide more security compared to them.

ENCRYPT Subprogram

The ENCRYPT subprogram is overloaded with a procedure and a function variant. The function accepts only RAW datatype as input and the procedures accepts CLOB and BLOB input types respectively (Overloaded for CLOB and BLOB datatypes). The input data of VARCHAR2 type must be converted to RAW before encrypting it through the DBMS_CRYPTO function.

 

% Note: Before converting an input VARCHAR2 string to RAW type, we must convert it to AL32UTF8 character set and then encrypt it using the DBMS_CRYPTO package.

The prototype of the ENCRYPT function encrypting the RAW input data using a stream or block cipher with a user supplied key and an optional initialization vector parameter is shown below,

 

DBMS_CRYPTO.ENCRYPT(

   src IN RAW,

   typ IN PLS_INTEGER,

   key IN RAW,

   iv  IN RAW          DEFAULT NULL)

 RETURN RAW;

 

The prototype of the ENCRYPT procedures encrypting BLOB and CLOB input data using a stream or block cipher with a user supplied key and an optional initialization vector parameter is shown below,

 

DBMS_CRYPTO.ENCRYPT(

   dst IN OUT NOCOPY BLOB,

   src IN            BLOB,

   typ IN            PLS_INTEGER,

   key IN            RAW,

   iv  IN            RAW          DEFAULT NULL);

 

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);

 

·         DST parameter is applicable only for the procedures. This is an INOUT parameter, thus, the value of the output will be overridden.

 

·         SRC parameter accepts RAW and LOB input data for the encryption for the overloaded function and the procedures respectively.

 

·         TYP parameter accepts the stream or block ciphers and the modifiers to be used.

 

·         KEY parameter accepts the encryption key used by the user for the encryption process.

 

·         IV parameter is also called as the initialization vector. This is for the block ciphers. The default is Null.

Type Parameter (TYP)

This parameter is the sum of the type encryption algorithm which we want to use for encryption, the type of chaining during encryption, and the type of padding for the data during the encryption process.

 

Types of encryption algorithms supported by DBMS_CRYPTO package

Encryption Type

Description

Key Length

ENCRYPT_DES

Data Encryption Standard with a block cipher.

56 bits

ENCRYPT_3DES_2KEY

Data Encryption Standard with a block cipher and operates on a block thrice (3DES) with 2 keys.

112 bits.

ENCRYPT_3DES

Data Encryption Standard with a block cipher and operates on a block thrice (3DES).

156 bits.

ENCRYPT_AES128

Advanced Encryption Standard with a block cipher.

128 bits.

ENCRYPT_AES192

192 bits.

ENCRYPT_AES256

256 bits.

ENCRYPT_RC4

Uses stream cipher. Generates secret random key unique to each session.

 

During encryption, we can encrypt the data block either in individual or chain them with their previous blocks to impose more security on them. We can choose our desired chaining method from the below table.

 

Types of chaining methods supported by DBMS_CRYPTO package

Name

Description

CHAIN_ECB

Electronic Code Book. Encrypts each block independently.

CHAIN_CBC

Cipher Block Chaining. The plaintext block is XORed with the previous ciphertext block before its encryption.

CHAIN_CFB

Cipher Feed Back. Enables encrypting units of data smaller than the block size.

CHAIN_OFB

Output Feed Back. The block cipher is converted into a synchronous stream cipher. Similar to CFB, but n-bits of the previous block is moved to the right extreme in the data queue waiting to be encrypted.

 

In block ciphering, the data must be in the unit of blocks. If they are not, we must pad the data explicitly to makes its length a multiple of 8, such that it forms a perfect block. DBMS_CRYPTO package allows us to choose from the below padding types for padding data before encryption.

 

Types of padding methods supported by DBMS_CRYPTO package

Name

Description

PAD_PKCS5

Padding based on the Public Key Cryptographic Standard #5 technique.

PAD_ZERO

Padding with zeros.

PAD_NONE

No padding is performed considering that the data is a perfect block for encryption (Multiple of 8).

 

Now, the TYP parameter can be set up from the above three method’s combinations as shown below,

 

typ => DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5

 

The package also provides two constants with the predefined combinations of the above three methods discussed.

 

Types of constants supported by DBMS_CRYPTO package

Constant Name

Encryption Type

Block Chain Type

Padding Type

DES_CBC_PKCS5

ENCRYPT_DES

CHAIN_CBC

PAD_PKCS5

DES3_CBC_PKCS5

ENCRYPT_3DES

CHAIN_CBC

PAD_PKCS5

 

Now, the TYP parameter can be written in a simplified manner rather long text by using any of the above constants as shown below.

 

typ => DBMS_CRYPTO.DES3_CBC_PKCS5

Digital Signature using Hash Function

This is a one-way hash function taking a variable length input string and converting it into a fixed-length output string (usually smaller in length than the input length) called as a hash value. This function acts as a digital signature to preserve the data integrity. This function should be used on the input data before and after its encryption. If the hash value returned at both the times is a match, then the data has not been altered.

 

This function is called one-way because the conversion of the input value to hash value cannot be re-engineered. That is, we cannot get the input value out of the hash value.

 

% Note: The hash values must be at least 128 bits in length to be considered secure.

The prototype of the overloaded hash functions is shown below,

 

DBMS_CRYPTO.Hash (

   src IN RAW,

   typ IN PLS_INTEGER)

 RETURN RAW;

 

DBMS_CRYPTO.Hash (

   src IN BLOB,

   typ IN PLS_INTEGER)

 RETURN RAW;

 

DBMS_CRYPTO.Hash (

   src IN CLOB CHARACTER SET ANY_CS,

   typ IN PLS_INTEGER)

 RETURN RAW;

 

·         SRC parameter accepts RAW, BLOB, and CLOB input strings that are to be hashed.

 

·         TYP parameter accepts any one of the hash algorithms for hashing.

 

Types of Hash algorithms supported by DBMS_CRYPTO package

Name

Description

HASH_MD4

Produces a 128-bit message digest hash value.

HASH_MD5

Produces a 128-bit message digest hash value, but secure than the HASH_MD4 algorithm.

HASH_SH1

This comes under Secure Hash Algorithm – 1 (SHA-1). Produces a 160-bit secure hash value.

HASH_SH256

This comes under Secure Hash Algorithm – 2 (SHA-2). Produces a 256-bit secure hash value.

HASH_SH384

This comes under Secure Hash Algorithm – 2 (SHA-2). Produces a 384-bit secure hash value.

HASH_SH512

This comes under Secure Hash Algorithm – 2 (SHA-2). Produces a 512-bit secure hash value.

 

% Note: The security of the hash algorithm gets stronger in the above incremental order.

Secure Digital Signature using MAC Function

A MAC or Message Authentication Code is a key-dependent one-way hash algorithm. This function is used to check the data integrity similar to the hash function, but with an additional key such that only authorized people can verify the hash value.

 

The prototype of the overloaded MAC functions is shown below,

 

DBMS_CRYPTO.MAC (

   src IN RAW,

   typ IN PLS_INTEGER,

   key IN RAW)

 RETURN RAW;

 

DBMS_CRYPTO.MAC (

   src IN BLOB,

   typ IN PLS_INTEGER

   key IN RAW)

 RETURN RAW;

 

DBMS_CRYPTO.MAC (

   src IN CLOB CHARACTER SET ANY_CS,

   typ IN PLS_INTEGER

   key IN RAW)

 RETURN RAW;

 

·         SRC parameter accepts RAW, BLOB, and CLOB input strings that are to be hashed using the MAC algorithms.

 

·         TYP parameter accepts any one of the MAC algorithms for hashing.

 

·         KEY parameter accepts the key for hashing using the MAC algorithms.

 

Types of Hash algorithms supported by DBMS_CRYPTO package

Name

Description

HMAC_MD5

Similar to the HASH_MD5 algorithm, except it requires a secret key for verifying the hash value.

HMAC_SH1

Similar to the HASH_SH1 algorithm, except it requires a secret key for verifying the hash value.

HMAC_SH256

Similar to the HASH_SH256 algorithm, except it requires a secret key for verifying the hash value.

HMAC_SH384

Similar to the HASH_SH384 algorithm, except it requires a secret key for verifying the hash value.

HMAC_SH512

Similar to the HASH_SH512 algorithm, except it requires a secret key for verifying the hash value.

Key Generation

The DBMS_CRYPTO package has three functions for generating random encryption keys, but there is no mechanism for maintaining them. We must make sure that the encryption keys are securely generated, stored, and transferred across the connection having a network encryption.

 

The prototype of the RANDOMBYTES function accepting a number of bytes as its input and returning a RAW value containing a secure random sequence of the input bytes for the encryption key generation is shown below. This function is based on the RSA X9.31 Pseudo-Random Number Generator (PRNG).

 

DBMS_CRYPTO.RANDOMBYTES (number_bytes IN POSITIVE) RETURN RAW;

 

The prototype of the RANDOMINTEGER function returning a BINARY_INTEGER value containing a random encryption key that is cryptographically secure is shown below.

 

DBMS_CRYPTO.RANDOMINTEGER RETURN BINARY_INTEGER;

 

The prototype of the RANDOMNUMBER function returning an integer in the NUMBER datatype as the encryption key is shown below.

 

DBMS_CRYPTO.RANDOMNUMBER RETURN NUMBER;

 

In the below example, we have encrypted a user defined input string using the properties below,

 

1.      The encryption type handled was a combination of AES256, Chain cipher block chaining, and PKCS5 padding.

 

2.      The encryption key consisted of a 32-byte long random RAW string.

 

3.      The secure hash 256 algorithm is chosen for hashing.

 

The necessary values like the input string, encryption key, hash value, and the encrypted input string are printed out using the PUT_LINE procedure as shown below.

 

The input string is converted into RAW type before encryption along with the character set conversion which is performed by using the UTL_I18N.STRING_TO_RAW function. The UTL_I18N package is provided as a part of Oracle’s globalization support to perform internationalization. The package was derived from the word INTERNATIONALIZATION (Starts with an I, ends with an N, and has 18 characters in the middle).

 

DECLARE

  l_vc_input_string VARCHAR2(50):=

'Advanced PL/SQL - The Definitive Reference';

 

  l_rw_encryption_key raw(32);

  l_rw_hash_value raw(32);

  l_rw_encrypted_string raw(2000);

 

  l_pi_encryption_type pls_integer:=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;

 

BEGIN

  l_rw_encryption_key :=dbms_crypto.randombytes(32);

 

  l_rw_hash_value     :=dbms_crypto.hash (utl_i18n.string_to_raw (l_vc_input_string, 'AL32UTF8'), dbms_crypto.hash_sh256);

 

  l_rw_encrypted_string := DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (l_vc_input_string, 'AL32UTF8'), l_pi_encryption_type, l_rw_encryption_key);

 

  dbms_output.put_line('Input string before encryption=> '||l_vc_input_string);

 

  dbms_output.put_line('Encryption Key=> '||l_rw_encryption_key);

  dbms_output.put_line('Hash value=> '||l_rw_hash_value);

  dbms_output.put_line('Encrypted value=> '||l_rw_encrypted_string);

END;

/

 

Result:

 

Input string before encryption=> Advanced PL/SQL - The Definitive Reference

 

Encryption Key=> 9193CDE58C04B34865409A732C59116AE0887251BC4D1F206E058FDEE6B7FEC4

 

Hash value=> F40E65E43818C3B55A51C1B94DE0CA7E91E759CD47D61468AA8E8477138C1DDF

 

Encrypted value=> A342D930E9D5D66AB44ADA8E60AFE4DD3301D8AF

F18C58077C636FDFEFEAB449EE5BB9D5415AE682B7983BFAD128D029

DECRYPT Subprogram

The DECRYPT subprogram is overloaded with a procedure and a function variant. The function is used for decrypting RAW input and the procedures accept CLOB and BLOB input types respectively (Overloaded for CLOB and BLOB datatypes). The decrypted data must be converted back to its appropriate character set using the UTL_I18N package.

 

% Note: To decrypt the original data, the DECRYPT subprogram must be called with the same cipher, modifiers, keys, and the initialization vector which was used during the encryption process.

The prototype of the DECRYPT function decrypting the RAW input data using the stream or block cipher with the secret key and the optional initialization vector used during the encryption is shown below,

 

DBMS_CRYPTO.DECRYPT(

   src IN RAW,

   typ IN PLS_INTEGER,

   key IN RAW,

   iv  IN RAW DEFAULT NULL)

 RETURN RAW;

 

The prototype of the DECRYPT procedures decrypting BLOB and CLOB input data using the stream or block cipher with the secret key and the optional initialization vector used during the encryption is shown below,

 

DBMS_CRYPTO.DECRYPT(

   dst IN OUT NOCOPY BLOB,

   src IN            BLOB,

   typ IN            PLS_INTEGER,

   key IN            RAW,

   iv  IN            RAW          DEFAULT NULL);

 

DBMS_CRYPT.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);

 

·         DST parameter is applicable only for the procedures. This is an INOUT parameter, thus, the value of the output will be overridden.

 

·         SRC parameter accepts RAW and LOB input data for decryption in the overloaded function and the procedures respectively.

 

·         TYP parameter accepts the stream or block ciphers and the modifiers to be used.

 

·         KEY parameter accepts the key used by the user for the encryption process.

 

·         IV parameter is also called as the initialization vector. This is for the block ciphers. The default is Null.

 

The encrypted string, encryption key, and the encryption type from the above example are passed as inputs to the below anonymous block for decryption. The UTL_I18N.RAW_TO_CHAR function is used for converting the decrypted RAW data into character format along with the character set conversion. The hash values which are generated during the encryption and decryption are compared to see if the data has been tampered or not. This block is executed to print the decrypted secret input text and its integrity status as shown below.

 

DECLARE

  l_rw_encrypted_string raw(2000):='6249B8F6F9B594EB8FC72DA152A40ADC5501C55969AAFDA660

0437C9518CBBC0F372858E0ADE212EC1FA67FE2EB7ADC8';

 

  l_rw_encryption_key raw(32):='6AA28EBC54ECDBCE68791F14AE1BA6901133365DBF9CF5E16C1A55B3E02F78FD';

 

  l_rw_input_hash_value raw(32):='F40E65E43818C3B55A51C1B94DE0CA7E91E759CD47D61468AA8E8477138C1DDF';

 

  l_pi_encryption_type pls_integer:=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;

 

  l_rw_decrypted_string raw(2000);

  l_rw_output_hash_value raw(32);

  l_vc_msg_integrity_status VARCHAR2(50);

BEGIN

  l_rw_decrypted_string := DBMS_CRYPTO.DECRYPT(l_rw_encrypted_string, l_pi_encryption_type, l_rw_encryption_key);

 

  l_rw_output_hash_value :=dbms_crypto.hash (utl_i18n.string_to_raw (UTL_I18N.RAW_TO_CHAR(l_rw_decrypted_string, 'AL32UTF8'), 'AL32UTF8'), dbms_crypto.hash_sh256);

 

  l_vc_msg_integrity_status:=

  CASE

  WHEN l_rw_input_hash_value=l_rw_output_hash_value THEN

    'Message has not been altered!'

  ELSE

    'Message has been altered!'

  END;

 

  dbms_output.put_line('Integrity Status=> '||l_vc_msg_integrity_status);

  dbms_output.put_line('Decrypted value=> '||UTL_I18N.RAW_TO_CHAR(l_rw_decrypted_string, 'AL32UTF8'));

 

END;

/

 

Result:

 

Integrity Status=> Message has not been altered!

 

Decrypted value=> Advanced PL/SQL - The Definitive Reference




For more information on dbms_crypto and dbms_crypto related algorithms, see these related links:

Oracle Internals - Implementing the SHA1 Algorithm with DBMS_CRYPTO

DBMS_CRYPTO package tips


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational