Database

Created Feb. 7, 2024 by Mumtaj Yasmin

Unit Objective:

The main objective of this unit is to reader to understand purpose of the Transparent Data Encryption (TDE) and Wallet Management. How to implement TDE encryption in table and tablespace using oracle.

8.1 Introduction of Transparent Data Encryption (TDE)

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) enables to encrypt sensitive data, such as Personally Identifiable Information (PII), Credit card number, salary, Civil card number store in tables and table spaces. After the data is encrypted, data is transparently decrypted for authorized users or applications when they access the data. Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table.Both TDE column encryption and TDE tablespace encryption use a two-tiered key-based architecture. Unauthorized users, such as intruders who are attempting security attacks, cannot read the data from storage and back up media unless they have the TDE master encryption key to decrypt it.

8.2 Encryption Components

Encryption components

Encryption keys

8.3 Encryption and Decryption Process

Encryption and Decryption

The master key of the server is stored in an external security module that is outside the database and accessible only to the security administrator.

For this external security module, Oracle uses an Oracle wallet Storing the master key in this way prevents its unauthorized use.

In addition to storing the master key, the Oracle wallet is also used to generate encryption keys and perform encryption and decryption.

When a table contains encrypted columns, a single key is used regardless of the number of encrypted columns.

The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database.

Data Dictionary Storage of Encryption key

8.4 Create Wallet

By default, the external security module stores encryption keys in the Oracle wallet specified in the sqlnet.ora configuration file.

If no wallet location is specified in the sqlnet.ora file, then the default database wallet is used.

If you wish to use a wallet specifically for transparent data encryption, then you must specify a second wallet location in sqlnet.ora by using the ENCRYPTION_WALLET_LOCATION parameter.

Create Wallet E:\app\user\admin\orcl\wallet – in the orcl directory create “Wallet” directory suppose it is not available.

insert wallet location in “sqlnet.ora”, as given below

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY=E:\app\user\admin\orcl\wallet)))

sqlnet.ora

wallet key in the data dictionary

8.5 Create Master key

To use transparent data encryption, it is needed ALTER SYSTEM privilege and a valid password to the Oracle wallet.

If an Oracle wallet does not exist, then a new one is created using the password specified in the SQL command. ALTER SYSTEM SET ENCRYPTION KEY command is a DDL command requiring the ALTER SYSTEM privilege, and it automatically commits any pending transactions.

ALTER SYSTEM SET ENCRYPTION KEY certificate_ID IDENTIFIED BY "password"

Certificate_ID is an optional string containing the unique identifier of a certificate stored in the security module. Use this parameter if intend to use r PKI private key as master key. This parameter has no default setting.

Search for a certificate_ID by querying the V$WALLET fixed view when the wallet is open. Only certificates that can be used as master keys by transparent data encryption are shown.

Password is the mandatory wallet password for the security module, with no default setting. It is case sensitive, enclose the password with double-quotation marks.

Create Encryption Master key

8.5.1 Open the Wallet

Alter system set encryption wallet open identified by "wallettest";

wallet open

Here "wallettest" is password for wallet.

Once the wallet has been opened, it remains open until you shut down the database instance

close it explicitly by issuing an ALTER SYSTEM SET ENCRYPTION WALLET CLOSE identified by "password" command.

When you restart the instance, you must issue the ALTER SYSTEM SET ENCRYPTION WALLET OPEN command again.

Once the wallet has been opened, it remains open until you shut down the database instance

close it explicitly by issuing an ALTER SYSTEM SET ENCRYPTION WALLET CLOSE command.

When you restart the instance, you must issue the ALTER SYSTEM SET ENCRYPTION WALLET OPEN command again.

8.5.2 Status of the Wallet

select wrl_type wallet,status,wrl_parameter wallet_location from v$encryption_wallet;

Wallet Status

If the wallet is already open, the command returns an error and takes no action

Wallet already open

8.5.3 Close the Wallet

Close the wallet using the below given command

Alter system set encryption wallet close identified by "wallettest";

wallet close

closed status

If the schema does not have the ALTER SYSTEM privilege, or the wallet is unavailable, or an incorrect password is given, then the command returns an error and exits.

Instead of “wallettest” try with incorrect password “wtest”

Incorrect password

8.6 Creating a Table with an Encrypted Column

To create a new table with encrypted columns, use the CREATE TABLE command in the following form:

CREATE TABLE table_name ( column_name column_type ENCRYPT,....);

The ENCRYPT keyword against a column specifies that the column should be encrypted. The ENCRYPT keyword against a column specifies that the column should be encrypted.

CREATE TABLE employee

(first_name VARCHAR2(128),

last_name VARCHAR2(128),

empID NUMBER,

salary NUMBER(6) ENCRYPT );

create encrypt table column

connect with "hr" schema and create table, salary column is encrypted. Display the salary column from employee table. it will show the value in the salary column.

display encrypted column

Encrypted is not displaying while wallet is closed, whenever wallet is open then only the encrypted table column display the value.

encrypted column

8.6.1 Salt

Salt is a way to strengthen the security of encrypted data.

It is a random string added to the data before it is encrypted, causing repetition of text in the clear to appear different when encrypted.

Salt thus removes one method attackers use to steal data, namely, matching patterns of encrypted text.

By default, transparent data encryption adds salt to clear text before encrypting it.

This makes it harder for attackers to steal the data through a brute force attack.

Adding Salt to an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

ALTER TABLE employee MODIFY (first_name ENCRYPT );

Both the statements are same, because by default all the encrypted columns in the tables are salt.

encrypted with salt

Display the salt and encrypted column details

Use user_encrypted_columns data dictionary view to display encrypted information about table, column, algorithm used for encryption and salt applied

Encryption data dictionary view

select table_name,column_name,salt from user_encrypted_columns;

Display the salt and encrypted column

Removing Salt from an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

Removing Salt

Encrypted Column Using No salt for Indexing

Index the encrypted column, must specify the NO SALT parameter with the SQL ENCRYPT clause

Remove the salt from an encrypted column before indexing it,

Encrypted Column Using No salt for Indexing

8.7 Encryption Algorithms for TDE

Encrypted Algorithm

By default, transparent data encryption uses AES with a 192-bit length key (AES192) for table.

AES128 is default for tablespace encryption.

Table with an Encrypted Column Using 3DES168

It also possible to assign the syntax for specifying a different encryption algorithm.

The string which specifies the algorithm must be enclosed in single quotation marks.

Different algorithm other than AES

Change the different algorithm already encrypted column

Include other column also same algorithm suppose if already table some column is encrypted then no problem, for example here fname also has same algorithm ‘3DES168’ like empid column. if the column is already encrypted it is not possible to change the different algorithm. In the table, all the column should be in the same algorithm

Not possible to change difference algorithm for same table

same algorithm using entire table to encrypt

Display encrypted algorithm in the table

Display encrypted algorithm

8.8 Disabling Encryption on a Column

It may be necessary to disable encryption for reasons of compatibility or performance.

To disable column encryption, use the ALTER TABLE MODIFY command with the DECRYPT clause

Disabling Encryption on a Column

8.9 Adding Encrypted Columns to Existing Tables

Adding new encryption column in existing table

8.10 Create an Encrypted Tablespace

TDE tablespace encryption enables you to encrypt an entire tablespace. By default , all the objects stored in the encrypted tablespace also encrypted.

table space encryption

conn hr

Display encrypted columns

Unit Summary

Unit 8- Exercise

Video Tutorial Link

Lecture Notes For Unit-8

Download: tde.pdf

Questions

References

1. https://docs.oracle.com/cd/B19306_01/network.102/b14268/asotrans.htm#BABJJAIG

2. https://docs.oracle.com/cloud/latest/db121/ASOAG/asotrans.htm#ASOAG10136

3. http://www.oracle.com/technetwork/testcontent/o19tte-086996.html

Return to top