Database Security
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
or
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
or
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);
or
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
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