Data encryption is very important because it’s a way to protect the current and the archived data in order to ensure its confidentiality. This prevents an unauthorized access and usage. In this article, I will briefly present some important aspects of data encryption and decryption.
Encryption Systems
In an encryption system, there are two main components: the encryption algorithm, which is the method used to alter the value, and the encryption key, whose security depends on the vulnerability of the encrypted data.
Oracle supports two types of encryption algorithms: symmetric algorithms (use the same key for encrypting and decrypting data) for encrypting stored data and asymmetric algorithms (2 keys are generated: a private key used for decryption and a public key that will be used by the message sender in order to encrypt the message) used for database login and for communicating between a database and a client.
Symmetric encryption algorithms, available in Oracle are as follows: Data Encryption Standard (DES) which encrypts a block of 64 bits of the text into 64 bits of the encrypted text, using a key of 56 bits, Triple Data Encryption Standard (3-DES), a more advanced version of DES, and Advanced Encryption Standard (AES), which encrypts a block of 128 bits of the text into 128 bits of the encrypted text, using a key of 128,129 or 256 bits.
One can notice that the encryption algorithms mentioned above use blocks of fixed dimension, and therefore, the text that must be encrypted will be divided into blocks of a certain dimension, required by the used algorithm and then, the algorithm will be applied on each obtained block.
However, what if data dimension is not a multiple of the required dimension of the block? The padding technique must be used in order to fill the last segment of the text until it reaches the dimension of a block. One can choose to pad with zeros or to use the padding scheme called PKCS#5. The scheme calculates the difference for the last text segment
d = dim_block – dim_data MOD dim_block
and fills each missing byte with the hexa value 0x0d,
where dim_block is the dimension in bytes for the block, required by the algorithm
and fills each missing byte with the hexa value 0x0d,
where dim_block is the dimension in bytes for the block, required by the algorithm
However, what if the plain text consists of several blocks to be encrypted? The chaining technique is used, which establishes if the encryption of a block is dependent or not on the encryption of the previous blocks.
The following types of chaining are available in Oracle:
- Electronic Code Book (CHAIN_ECB): each block is encrypted independently from the rest of the blocks. The disadvantage is that one can identify repetitive patterns in the text fragment.
- Cipher Block Chaining (CHAIN_CBC): for each block, before encryption, a XOR operator is applied with a vector. For the first block from the sequence, an initialization vector is used, while for a block from the rest of the sequence, the encryption result of the previous block is used as a vector of bits
- Cipher Feedback (CHAIN_CFB): it’s similar with CBC, except for the fact that the XOR operator is applied after the block encryption.
- Output Feedback (CHAIN_OFB): it’s similar with CFB, except for the fact that the result of the previous block is encrypted before the XOR operator is applied
To encrypt and decrypt data, we can use the DBMS_CRYPTO package available in ORACLE. To use this package, SYSDBA must grant users with execute rights, using the command:
GRANT EXECUTE ON dbms_crypto TO username;
For encryption, dbms_crypto.encrypt is used, which has the following parameters:
dbms_crypto.encrypt( clear_text IN RAW, operating_mode IN PLS_INTEGER, key IN RAW, initialization_vector IN RAW DEFAULT NULL) RETURN RAW;
For decryption, dbms_crypto.decrypt is used, which has the following parameters:
dbms_crypto.decrypt( clear_text IN RAW, operating_mode IN PLS_INTEGER, key IN RAW, initialization_vector IN RAW DEFAULT NULL) RETURN RAW;
An operating mode is formed of:
algorithm code + padding code + chaining code
Here are some examples for encrypting and decrypting data:
create or replace PROCEDURE encryption(text IN VARCHAR2, encrypted text OUT VARCHAR2) AS
raw_set RAW(100);
raw_password RAW(100);
encryption_result RAW(100);
encryption_password VARCHAR2(100) := 'Q?Tx.H9h@%5,:QxV';
operation_mode NUMBER;
BEGIN
raw_set:=utl_i18n.string_to_raw(text,'AL32UTF8');
raw_password := utl_i18n.string_to_raw(encryption_password,'AL32UTF8');
operation_mode:=DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.PAD_ZERO + DBMS_CRYPTO.CHAIN_ECB;
encryption_result:=DBMS_CRYPTO.ENCRYPT(raw_set,operation_mode,raw_password);
dbms_output.put_line(encryption_result);
encryptedtext := RAWTOHEX (encryption_result);
END;
variable result_encryption varchar2(200)
exec encryption('Text to be encrypted', :result_encryption);
print result_encryption
create or replace PROCEDURE decryption (encrypted_text IN VARCHAR2, decrypted_text OUT VARCHAR2) AS
raw_set RAW(100);
raw_password RAW(100);
decryption_result RAW(100);
decryption_password VARCHAR2(100) := 'Q?Tx.H9h@%5,:QxV';
operation_mode NUMBER;
BEGIN
raw_set:=HEXTORAW(encrypted_text);
raw_password :=utl_i18n.string_to_raw(decryption_password,'AL32UTF8');
operation_mode:=DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.PAD_ZERO + DBMS_CRYPTO.CHAIN_ECB;
decryption_result:=DBMS_CRYPTO.DECRYPT(raw_set,operation_mode,raw_password);
dbms_output.put_line(decryption_result);
decrypted_text := utl_i18n.raw_to_char (decryption_result,'AL32UTF8');
END;
variable result_decryption varchar2(200)
exec decryption(:result_encryption,:result_decryption)
print result_decryption
Data Encryption Challenges
Management of Encryption keys for data
It’s difficult for the database users to manually generate efficient encryption keys, which have the required length for the encryption algorithm. Concerning the manual supply of the encryption key as a string set (converted then in RAW), the length of the set is calculated using the following formula:
L_set = Length_key_in_bits / 8 (characters)
For example, for ENCRYPT_AES128, the key has 128 bits and the set will have the length L_set = 128/8 = 16 characters.
If the key ‘1234567890123456’ is supplied, it will be accepted, while the key ‘1234’ will throw the exception ‘key length too short’.
For the rest of the algorithms, the following table gives the effective key length:
Key generation and transmission
Data is securely encrypted as long as the key used for encrypting is secure. Therefore, the encryption key must be securely generated. The RANDOMBYTES of DBMS_CRYPTO function package offers a secure random number generation and implements the random number generator algorithm. Developers mustn’t use the DBMS_RANDOM package because it generates pseudo-random numbers, which can result in pseudo-security.
RANDOMBYTES function is used as follows:
key RAW (nr_bytes);
key:= DBMS_CRYPTO.randombytes (nr_bytes);
where nr_bytes represents the number of bytes of the encryption key
When the key is passed from an application to the database, it must be encrypted in order not to be stolen when it is being transmitted. Oracle Advanced Security provides network encryption, which protects the data and the cryptographic keys in their network transit.
Once the keys are generated, they must be kept safe, because their disclosure might compromise the security of the encrypted data. There are three options to keep the key:
- at the database level: stored in the database (in a special table) or in an external database file
- at the record level: stored in the database (in a special table)
- a combination between the previous two types: there is a master key at the database level and for each record, there is a key. A hybrid key is used for both encryption and decryption: hybrid_key = master_key XOR record_key (for XOR function, there is a PL/SQL function – UTL_RAW.bit_xor).
What is the most efficient key? It is the hybrid key. If the whole database is stolen, the data can’t be decrypted when the master key is stored in the file system. If the master key or a key record are stolen, the rest of the record would still be protected.
Transparent Data Encryption (TDE)
Another security facility offered by Oracle is the Transparent Data Encryption (TDE), a facility available from Oracle 10g. TDE allows declaring an encrypted column at the table level of the database. When inserting data into the encrypted column, Oracle automatically encrypts the data and stores their encryption in the database.
Any SELECT query will automatically decrypt the data. It is important to remember that the Transparent Data Encryption doesn’t make difference between users, and gives the decrypted data no matter who is interrogating the database. Not any column can be encrypted by TDE: columns from the external key (foreign key) can’t be encrypted using this method.
For example: consider a table called ACCOUNT with the relational scheme ACCOUNT (id_account#, card_series, possessor, amount), where id_account is considered as the primary key, fact denoted by the ‘#’ sign. Let’s assume we want to declare the card_series and balance columns to be encrypted. This can be done with the following:
ALTER TABLE accont MODIFY (card_series ENCRYPT USING 'AES128'); ALTER TABLE accont MODIFY (balance ENCRYPT USING 'AES128');
In order for this method to work, a wallet must be created and configured.
Why does Transparent Data Encryption prevent the decryption of data in case the database is stolen? Well, all the encrypted columns from a table T use the same private key Key_T. If we have more tables, ,…, that have encrypted columns, then there are n private keys, Key_,…,Key_. Each private key Key_, j=1,n is encrypted with the master key, Key_Master, and the result of this encryption is stored in the data dictionary. The master key is externally stored in a wallet.
Now, let’s look at the steps for this automatic encryption. They are:
- obtaining the master Key_Master from the external wallet
- decryption of the private key, Key_, using the master key
- encryption of the data to be inserted using the private key, Key_
- storing the encrypted data in the table columns
The steps for automatic decryption are:
- obtaining the master key, Key_Master, from the external wallet
- decryption of the private key, Key_, using the master key
- decryption of the data using the private key, Key_
- returning the result
What about the data integrity while encrypting? Here is we use the hashing technique.
Hashing
Data encryption ensures the confidentiality of data but doesn’t guarantee their integrity. To prevent this problem, besides the fact that only original data should be encrypted, we can use a technique called hashing . there are two important advantages: it doesn’t allow deciphering the original values and it is deterministic (which means that repetitively applied to the same data, it generates the same result). Oracle allows the following hashing algorithms: MD5 and SHA-1.
Usage:
DBMS_CRYPTO.Hash ( original_set IN RAW, operation_mode IN PLS_INTEGER) RETURN RAW; where operation_mode is either DBMS_CRYPTO.HASH_MD5 or DBMS_CRYPTO.HASH_SH1
Example:
set serveroutput on
DECLARE
credit_card_no VARCHAR2(19) := '1234-5678-1234-5678';
credit_card_no_raw RAW(128) := utl_raw.cast_to_raw(credit_card_no);
encrypted_raw RAW(2048);
BEGIN
encrypted_raw:=dbms_crypto.hash(credit_card_no_raw, dbms_crypto.hash_md5);
dbms_output.put_line('MD5: ' || encrypted_raw);
END;
Encrypting Indexed Data
It is not recommended for developers to encrypt indexed data. If an index of a column contains encrypted values, then the index can be used for simple checking and is unusable for other purposes.
For example, suppose a company uses the personal identification number for the employee number that is stored in a database table. Obviously, those numbers are considered as sensitive and confidential data and the company wants to encrypt the column where it is stored. Because this column contains unique values, an index can be performed on it in order to have better performance. This means that the index will contain encrypted data and would be basically unusable.
Conclusion
To conclude with, it is very important to protect sensitive data by encrypting and decrypting it. One should be aware of this aspect and implement the database security accordingly.
References:
- Developing Applications Using Data Encryption
- Feuerstein Steven (2009) Oracle PL/SQL Programming (5th edition). O’Reilly Publishing. ISBN 978-0-596-51446-4. Chapter 23 „Application security and PL/SQL”
- Block cipher mode of operation
