Securing Data at Rest in Oracle 11g

Introduction

The Oracle physical database files are primarily protected by filesystem privileges. An attacker who has read permissions on these files will be able to steal the entire database or critical information such as datafiles containing credit card numbers, social security numbers, or other types of private information. Other threats are related to data theft from storage mediums where the physical database resides. The same applies for unprotected backups or dumps that can be easily restored or imported. The data in the database is stored in proprietary format that is quite easy to decipher. There are several sites and specialized tools available to extract data from datafiles, backups, and dumps, known generically as Data Unloading ( DUL). These tools are usually the last solution when the database is corrupted and there is no backup available for restore and recovery. As you probably have already guessed, they can be used by an attacker for data extraction from stolen databases or dumps (summary descriptions and links to several DUL tools can be found at http://www.oracle-internals.com/?p=17 Blvd). The technology behind DUL utilities is based on understanding how Oracle keeps the data in datafiles behind the scenes (a very good article about Oracle datafile internals, written by Rodrigo Righetti, can be found at http://docs.google.com/Doc?id=df2mxgvb_1dgb9fv). Once you decipher the mechanism you will be able to build your tool with little effort.

One of the best methods for protecting data at rest is encryption. We can enumerate the following as data encryption methods, described in this chapter for using with Oracle database:

Using block device encryption

By using block device encryption the data is encrypted and decrypted at block-device level. The block device can be formatted with a filesystem. The decryption is performed once the filesystem is mounted by the operating system, transparently for users. This type of encryption protects best against media theft and can be used for datafile placement. In this recipe we will add a new disk and implement block-level encryption with Linux Unified Key Setup-on-disk-format (LUKS).

Getting ready

All steps will be performed with nodeorcl1 as root.

How to do it...

Shut down nodeorcl1, then add a new disk to the nodeorcl1 system and boot it. Our new device will be seen by the operating system as /dev/sdb . Next, create a new partition /dev/sdb1 using fdisk as follows:

At boot sequence, the passphrase for /storage will be requested. If no passphrase is typed then the mapper device will be not mounted.

How it works...

Block device encryption is implemented to work below the filesystem level. Once the device is offline, the data appears like a large blob of random data. There is no way to determine what kind of filesystem and data it contains.

There's more...

To dump information about the encrypted device you should execute the following command:

Using filesystem encryption with eCryptfs

The eCryptfs filesytem is implemented as an encryption/decryption layer interposed between a mounted filesystem and the kernel. The data is encrypted and decrypted automatically at filesystem access. It can be used for backup or sensitive files placement for transportable or fixed storage mediums. In this recipe we will install and demonstrate some of eCryptfs, capabilities.

Getting ready

All steps will be performed on nodeorcl1.

How to do it...

eCryptfs is shipped and bundled with the Red Hat installation kit.

The eCryptfs package is dependent on the trouser package. As root user, first install the trouser package followed by installation of the ecryptfs-util package:

To make the file accessible again, mount the /ecryptedfiles filesystem by passing the same parameters and passphrase as performed in step 3.

How it works...

eCryptfs is mapped in the kernel Virtual File System ( VFS ), similarly with other filesystems such as ext3, ext4, and ReiserFS. All calls on a filesystem will go first through the eCryptfs mount point and then to the current filesystem found on the mount point (ext4, ext4, jfs, ReiserFS). The key used for encryption is retrieved from the user session key ring, and the kernel cryptographic API is used for encryption and decryption of file content. The communication with kernel is performed by the eCryptfs daemon. The file data content is encrypted for each file with a distinct randomly generated File Encryption Key ( FEK ); FEK is encrypted with File Encryption Key Encryption Key ( FEKEK ) resulting in an Encrypted File Encryption Key ( EFEK) that is stored in the header of file.

There's more...

On Oracle Solaris you can implement filesystem encryption using the ZFS built-in filesystem encryption capabilities. On IBM AIX you can use EFS.

Using DBMS_CRYPTO for column encryption

The DBMS_CRYPTO PL/SQL package is an important component of Oracle Cryptographic API. DBMS_CRYPTO can be wrapped in your own packages and used for encryption and decryption. It is mainly used for hindering data access using encryption on designated columns. Consider it as a selective method of encryption—the columns are stored in encrypted format on storage and remain encrypted during data access unless they are decrypted with the appropriate function.

In this recipe we will create a table EMPLOYEES_ENC and encrypt and decrypt the SALARY and COMMISSION_PCT columns of this table by using DBMS_CRYPTO wrapped in two functions.

Getting Ready

All steps will be performed on the HACKDB database.

How to do it...

As root create a directory named hashkeydir and make the oracle user the owner:

mkdir /hashkeydir
chown oracle:oinstall /hashkeydir

Connect as system and create a directory named encryption_keys as follows:

At this step we will create a package named column_encryption_pkg and the wrapper function definitions for encryption and decryption implemented with DBMS_CRYPTO. We will explain in detail the scope of its functions and procedures later. Create the package column_encryption_pkg as follows:

CREATE OR REPLACE
PACKAGE encryption_pkg
IS
--Generate the encryption key for a given table
PROCEDURE store_encryption_key
(
p_dir_name IN VARCHAR2,
p_key_filename IN VARCHAR2);
--Retrieve the encryption key from the local storage
--PROCEDURE get_encryption_key(p_dir_name IN VARCHAR2,p_key_
filename IN VARCHAR2);
--Function used to encrypt a given string
FUNCTION encrypt_column
(
p_column_value IN VARCHAR2,
p_dir_name IN VARCHAR2,
p_key_filename IN VARCHAR2)
RETURN raw;
--Function used to decrypt a given string
FUNCTION decrypt_column
(
p_encrypted_value IN RAW,
p_dir_name IN VARCHAR2,
p_key_filename IN VARCHAR2)
RETURN VARCHAR2;
END column_encryption_pkg;
SQL> /
Package created.
SQL>

At this step we should be able to encrypt the SALARY and COMMISSION_PCT columns. First we have to generate the encryption key by executing the store_encryption_key procedure. Pass the directory name ( ENCRYPTION_KEYS) and the key storage file name (KEYFILE) as follows:

If the column values match, you should remove the unencrypted columns and continue to add values from now on to the corresponding encrypted columns by using the encrypt_column function. Also as an additional protection measure you should remove all the code comments and wrap the package and package body to hide the source code.

How it works...

The DBMS_CRYPTO package accepts as input values varchar2 and lob type fields, and implicitly returns RAW type data. Therefore it is necessary to cast the data from the initial type to RAW and cast back at return to the initial data type.

DBMS_CRYPTO.ENCRYPT_RC4:RC4 provides the following encryption algorithms:

For AES:

DBMS_CRYPTO.ENCRYPT_AES128: AES with 128-bit key size

DBMS_CRYPTO.ENCRYPT_AES192: AES with 192-bit key size

DBMS_CRYPTO.ENCRYPT_AES128: AES with 256-bit key size

For DES:

DBMS_CRYPTO.ENCRYPT_DES : DES wtih 56-bit key size

DBMS_CRYPTO.ENCRYPT_3DES_2KEY: 3DES with 112-bit key size

DBMS_CRYPTO.ENCRYPT_3DES: 3DES with 168-bit key size

We have briefly described these algorithms in Chapter 2, Defending the Network and Data in Transit.

The supported block cipher chaining modifiers, also known as block cipher modes of operations are ECB, CBC, CFB, and OFB. Cipher modes of operation protect against block replay attacks, enabling repeated and secure use of a block cipher under a single key, making the encryption of one block dependent on all preceding blocks.

The blocks are encrypted using an initialization vector (IV), which is a block of bits used to randomize the encryption. In this way, the resulting ciphertext is different every time even if the input plaintext is the same.

ECB (DBMS_CRYPTO.CHAIN_ECB) is the abbreviation for Electronic Codebook. It is the simplest and weakest cipher chaining modifier. It generates the same ciphertext for the same plaintext being very sensible to replay attacks. Therefore it is not recommended to use it in any circumstances.

CBC (DBMS_CRYPTO.CHAIN_CBC) is the abbreviation for Cipher block chaining. In this mode, on each block of plaintext before encryption an XOR operation is performed using the previous ciphertext block. In this method the encryption is randomized using an initialization vector at the beginning.

CFB (DBMS_CRYPTO.CHAIN_CFB) is the abbreviation for Cipher Feedback. CFB is similar to CBC; the operations are performed as in CBC but in the reverse order.

OFB (DBMS_CRYPTO.CHAIN_OFB) is the abbreviation for Output Feedback. It uses a stream cipher encryption scheme similar to CFB. It generates keystream block s, which are then XORed with the plaintext blocks to get the ciphertext.

The padding schemes provided by DBMS_CRYPTO are PKCS5, NONE, and NULL.

Padding is used to fill up empty blocks. Usually the size of plaintext to be encrypted is not an exact multiple of the block size. The recommended padding scheme is PKCS5.

There's more...

DBMS_CRYPTO can also be used for integrity check by using MD5 and SHA1 hashes, and Message Authentication Codes ( MAC). The difference between hashes and MAC is that hashes are used to guarantee integrity, whereas, a MAC guarantees integrity and authentication. The value generated by a hash is always the same and is based solely on an input value, while a MAC relies on generating the hash using a secret key.

The following is an example of a procedure for generating hash and MAC values using an input password. If the procedure is executed multiple times, it will generate the same hash and different MAC values for the same password.

Using Transparent Data Encryption for column encryption

Transparent Data Encryption (TDE) relays on the database kernel mechanism and does not require additional programming. The key management is performed automatically by the database. From an architectural point of view, it was designed to protect the data from physical theft and it does not provide data access protection. The encryption is performed at storage level, and the column decryption occurs at data access. Therefore, the data will be visible for anyone with select privileges on tables containing encrypted columns with TDE. Being a feature provided by Oracle Advanced Security (OAS ), you must purchase the OAS pack license to use this capability.

In this recipe, we will encrypt the EMPLOYEES table's columns, SALARY and COMMISSION_PCT , using various options available for TDE column encryption.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

As the oracle user, create a directory for the encryption wallet (be sure to secure the filesystem permissions as described in Chapter 1, Operating System Security):

mkdir –p /security/wallets/tde
chmod 600 /security/wallets/tde

TDE encryption is performed using an external master key placed externally within an encryption wallet which is used to encrypt the table key, which in turn is used to encrypt and decrypt data in the table column. The encryption wallet location is defined within sqlnet.ora using ENCRYPTION_WALLET_PARAMETER. Backup sqlnet.ora and add the path to directory created in the previous step to ENCRYPTION_WALLET_LOCATION parameter as follows:

The information related to the encrypted columns can be found in the USER_ENCRYPTED_COLUMNS dictionary view at user-level and in the DBA_ENCRYPTED_COLUMNS system dictionary view at database-level:

The default encryption algorithm is AES192. If you want to change the encryption algorithm, for example to AES256, issue the following command:

SQL> alter table hr.employees rekey using 'AES256';
Table altered.

If you want to regenerate the table encryption key, issue the following command:

SQL> alter table hr.employees rekey;
Table altered

The default encryption mode is performed using salt . Salt is a cryptographic term used for a random string that is added to data before encryption and is used to prevent dictionary and pattern matching type attacks. To remove salt from encrypted columns execute the following:

If you do not specify an explicit wallet location with ENCRYPTION_WALLET_LOCATION or WALLET_LOCATION the default database wallet location will be $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet or $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet.

How it works...

The data is encrypted at storage level. This means that the transactions from redo logs, undo, and temp segments will contain these columns in encrypted format. The column data is encrypted also at buffer cache level being protected in this way against different memory read techniques. The columns' encryption keys are stored in the ENC$ dictionary table in encrypted form. The column-level keys are encrypted using the master key that has an external placement configured in sqlnet.ora, using the ENCRYPTION_WALLET_LOCATION or WALLET_LOCATION parameter. The master key value is generated randomly at its definition by TDE. Using the salt default option, the column will be prefixed with randomly generated strings. This method makes statistical attacks and hash matching difficult.

By default, the columns are encrypted using salt and MAC options. The default algorithm used is AES192 and the MAC is implemented using SHA1.

Information about encrypted columns can be found in the following dictionary views:

ALL_ENCRYPTED_COLUMNS

USER_ENCRYPTED_COLUMNS

DBA_ENCRYPTED_COLUMNS

There's more…

There are some limitations regarding column encryption, recommendations to be made, and some performance implications by using column encryption.

Performance implications

The following are performance implications caused by using the column encryption:

The database performance is not affected until the encrypted data is accessed or modified. Oracle claims that column encryption and decryption will impose an approximate 5 percent performance penalty. This is a rogue approximation, the performance penalty depends on many factors such as how many encrypted columns are selected, type of joins, if sorting is performed or not against encrypted columns and more. To find out the exact performance penalty you should perform several extensive tests against the encrypted data.

Storage overheads: The overhead will not be seen by using the dictionary views.

Limitations

The following are the limitations caused by using the column encryption:

The use of streams replication, materialized view logs, transportable tablespaces, logminer, exp/imp, and Oracle Audit Vault, if you use REDO COLLECTORS that are based on streams replication technology.

You cannot encrypt indexed columns using the default salt option, and you cannot create indexes on columns encrypted with salt.

You cannot encrypt foreign key indexes using TDE column encryption. If this is a necessity consider moving tables with foreign indexes to encrypted tablespaces with TDE.

BINARY_DOUBLE

BINARY_FLOAT

CHAR

DATE

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

LOBs (Internal LOBs and SECUREFILE LOBs Only)

NCHAR

NUMBER

NVARCHAR2

RAW

TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE)

VARCHAR2

The datatypes that can be encrypted with TDE column encryption are:

Recommendations

Do not encrypt columns used in index range scans, the optimizer will not take into consideration the index anymore. The default MAC option will add an additional 20 bytes overhead per encrypted value. Also MAC induces performance overhead due to integrity checking performed at data access. Using NOMAC option will reduce space and performance penalties considerably. Also by using salt there will be an additional 16 bytes overhead per encrypted data. Consider using nosalt option to reduce storage space. The downside of suppressing MAC and salt is that you will end up with weaker security per encrypted column. To save space you can use the NOMAC option. After the columns are encrypted, there can remain portions of data in cleartext format that belonged to columns before encryption. Therefore, it is recommended to move the tables containing encrypted columns to other tablespaces.

Also, there could be situations when the unencrypted data chunks may remain in the swap area, and it is possible to be read by unauthorized users. A solution for this phenomenon may be to use a large page allocation for the database and sessions, or use encrypted swap filesystems. For example, eCryptfs provides encryption at filesystem-level for swap, and can be used on Linux.

See also

The Using filesystem encryption with eCryptfs recipe

Using TDE for tablespace encryption

While TDE Column encryption is available from 10 g R2, TDE tablespace encryption is an exclusive 11g feature and was introduced in Oracle R1 (11.1.0.5). Using this option ensures that all tables and indexes contained within a tablespace will be encrypted transparently.

In this recipe, we will create an encrypted tablespace called ENCRYPTED_TBS using TDE.

Getting ready

All steps will be performed using HACKDB database.

How to do it...

For this chapter we will reuse the encryption wallet defined in the previous recipe Using column Transparent Data Encryption:

To create encrypted objects using TDE, the encryption wallet must have the status as OPEN. To check the availability of the encryption wallet, issue the following statement:

More information about existing encrypted tablespaces can be found in the v$encrypted_tablespaces system view:

How it works...

Tablespaces are encrypted using an encryption key stored in the dictionary. Oracle 11 g R1 column encryption and tablespace encryption uses separate encryption keys in R2. These keys are unified in one principal key used for encrypting both columns and tablespaces. The algorithms that can be used for tablespace encryption are: 3DES168, AES128, AES192, and AES256, where AES192 is the default if no other algorithm is specified.

Information about encrypted tablespaces can be found in the V$ENCRYPTED_TABLESPACE dictionary view.

You may find the encrypted tablespaces in your database by querying the DBA_TABLESPACE and USER_TABLEPACES dictionary views.

The ENCRYPTED column indicates whether a tablespace is encrypted.

There's more…

Unlike column-based encryption, there is no additional storage for the encrypted tablespaces.

As a restriction, current tablespaces cannot be encrypted. The data can be moved by using alter table move, create table as select, or using data pump.

Encryption key management

TDE will not perform any encryption or decryption operation unless the encryption wallet is opened.

If you reboot or shutdown the database the encryption wallet will be closed too. To open the encryption wallet:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "UYio71+^ZaPO"

To close manually the encryption wallet issue the following:

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "UYio71+^ZaPO"

Using encryption with data pump

Table or full database dumps can also be a major source of information theft in case it is not protected. Oracle also provides encryption options for data pump exports using TDE or passwords. In this recipe we will generate dumps by exporting the HR schema using different encryption options. Next, we will import each dump by remapping the tablespace USERS to the tablespace ENCRYPTED_TBS, and using related options.

Getting ready

All steps will be performed on the database HACKDB.

How to do it...

Create a directory /security/datapump for dumps and change its ownership to the user oracle:

mkdir –p /backup/datapump
chown oracle:oinstall /backup/datapump

Connect as the user system and create an oracle directory mapped to the /backup/datapump directory by executing the following statement:

Dumps made using dual mode will first check for the encryption key within the wallet used for encrypting the exported dump (the wallet must be in open state). Import hr_encdump_dualmode.dmp made with the encryption mode dual, with the encryption wallet open:

How it works...

The encryption of dumps is controlled by the ENCRYPTION parameter. The possible values for this parameter are:

ENCRYPTED_COLUMNS_ONLY: The encrypted columns are exported to the dump file set in encrypted format

DATA_ONLY: All data is exported in encrypted format

METADATA_ONLY: All metadata is exported in encrypted format

ALL: All data and metadata is exported to the dump file set in encrypted format

NONE: Nothing is encrypted

The ENCRYPTION_MODE parameter controls the mode of encryption and may have the following values:

DUAL: This encrypts the dump using the wallet and the password provided by the ENCRYPTION_PASSWORD parameter. While importing the data, if you share the same master key as the source database then the password is not mandatory and the dump can be imported using master key decryption.

TRANSPARENT: This encrypts the dump using the master key within the wallet. The source database must have the same master key.

This mode can be combined with ENCRYPTION_PASSWORD. At import the password will be mandatory. In this way the data is encrypted using the password provided and the destination database might have another encryption master key.

While you import the data from a dump created in transparent mode, you have to ensure that your encryption wallet is opened at the destination database and contains the same encryption key.

Using encryption with RMAN

Database backups also represent a very important area to be defended. Similarly with data pump dumps, backups made with RMAN can be encrypted and decrypted using encryption wallets. In this recipe we will enable RMAN encryption. We will also make a full backup followed by a restore. Next, we will save and delete the encryption wallet, and try a restore and recovery. We also emphasize the importance of saving these keys in a safe place.

Getting ready

All steps will be performed on nodeorcl1.

How to do it...

Create a new directory to be used as the destination for future backups with the oracle user as the owner:

mkdir –p / backup/rman
chown oracle:oinstall /backup/rman

Connect with RMAN and enable the encryption of backups for the database as follows:

How it works...

The encryption of backup sets is performed in the transparent mode using the encryption wallet. The mechanism is identical with the transparent mode used for data pump.

There's more...

Always try to save the master key in a safe place and do not include it along with your backup sets, an attacker who can open the encryption wallet (if it is of the auto-login type it does not require password) will be able to restore the database (by default RMAN does not backup set the master key). Without the appropriate database master key, it will be impossible to restore and recover your database from encrypted backups.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.