 |
|
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:
