Wednesday, April 5, 2017

Transparent Data Encryption (12c)

One of my previous articlse explains how to setup TransparentData Encryption in 11g. In this article I would discuss how to implement Transparent Data Encryption (TDE) in 12c database to encrypt the data in tables/column and tablespace. Following is the link to official 12c documentation I used to write this article
https://docs.oracle.com/database/121/ASOAG/asotrans_config.htm#ASOAG10474
Before we implement TDE, we need to perform a few steps that are prerequisites to implement the TDE. This includes creating keystore to store the master encryption key, and creation of master encryption key. Following are the steps to implement TDE in 12c.

1)
Defining Keystore
Keystore can be a hardware keystore or a software keystore. Hardware keystore is a physical device that is used to store the encryption key. Software keystore is the location on the file system where encryption key is stored. We will be working on software keystore for this article.
Software keystores have 3 types.
Password-based software keystore: As names suggests, this type of keystore is protected by a password, and password is required to open the keystore to retrieve the encryption keys.
Auto-login software keystore: This kind of keystores are protected by system-generated password, and does not need to opened explicitly because these keystores open automatically upon access.
Local auto-login software keystores: This type of keystores have auto-login functionality on the computer where these are created and these cannot be opened from any other computer.
SQLNET.ORA file is used to specify the location of keystore. If we have multiple databases, each database will have its own keystore to store the master encryption key. Edit sqlnet.ora file and add the keystore location. Directory structure specified in the SQLNET.ORA must already exist. In case of RAC and/or dataguard, sqlnet.ora file on all RAC nodes and standby host must be edited to add the keystore location. For this article, my database SID is SALMAN12C.
ENCRYPTION_WALLET_LOCATION=
               (SOURCE=
               (METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C))
               )
If we want to define keystore for multiple databases, we use entry similar to the following
ENCRYPTION_WALLET_LOCATION=
               (SOURCE=
               (METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\APP\ORACLE\ADMIN\WALLET\%ORACLE_SID%))
               )

Directory with the same name as %ORACLE_SID% must already exist. For Unix based systems, use $ORACLE_SID instead. For example, if we have 2 databases (MYDB1 and MYDB2) on same database server and both need implementation of TDE, we will add above entry in sqlnet.ora, and then create following 2 directories as keystore for each database.
C:\app\oracle\admin\wallet\mydb1
C:\app\oracle\admin\wallet\mydb2

In a multitenant environment where we may have one or more pluggable databases, ENCRYPTION_WALLET_LOCATION parameter is set for the Container Database (CDB), not for individual pluggable databases.
Keystore can also be placed on the ASM. Following is an example of setting keystore location to a directory located on ASM.
ENCRYPTION_WALLET_LOCATION=
               (SOURCE=
               (METHOD=FILE)(METHOD_DATA=(DIRECTORY=+DATA/WALLETS/SALMAN12C))
               )

2)
Create Password-Based Keystore
In 12c there is a new privilege called “administer key management” and the user granted this privilege should log into the database as “SYSKM”. SYS user can also perform this task and I am using SYS user here by logging into the root container.
C:\Users\salmqure>sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 9 16:48:16 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
Create keystore as follows. Provide the password for the keystore in IDENTIFIED BY clause.
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C’ IDENTIFIED BY salman12;

keystore altered.

-- Check Status of the keystore. Keystore will be closed initially
SQL> select WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;



WRL_PARAMETER                                                         STATUS                         WALLET_TYPE        
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- ---
 C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C     CLOSED                         UNKNOWN             



As soon as above command is executed, a file with name “ewallet.p12” gets created under the keystore location mentioned in the command.
To open the keystore, use following method.
Note that in a multitenant environment, keystore needs to be opened for each pluggable database individually by using same method as mentioned bellow. Keystore for root container (CDB$ROOT) needs to be opened first before opening keystore for other pluggable databases. Using CONTAINER=ALL option while opening keystore of root container would open the keystore for all containers at once.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY salman12 container=all;

keystore altered.

SQL> select WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;

WRL_PARAMETER                                                              STATUS                                            WALLET_TYPE
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ---------
C:\APP\ORACLE\ADMIN\\WALLET\SALMAN12C     OPEN_NO_MASTER_KEY             PASSWORD

     
-- To Open keystore for individual pluggable database, if not opened using CONTAINER=ALL option.

SQL> ALTER SESSION SET CONTAINER=PDB;

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY salman12 ;

keystore altered.

SQL> select WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;

WRL_PARAMETER                                                            STATUS                                            WALLET_TYPE
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ---
C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C     OPEN_NO_MASTER_KEY             PASSWORD


-- To close keystore in all containers at once. Execute following from root container.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY salman12 container=all;

keystore altered.


3)
Create TDE Master Encryption Key
In this step we will create the master encryption key that will be stored in the keystore. Master encryption key protects tables and tablespaces encryption keys that are used for encryption of data. In a multitenant environment, master encryption key can be created from root container by using CONTAINER=ALL option with the command. If we don’t use CONTAINER=ALL clause, then we would need to create master encryption key individually in each pluggable database where we plan to use TDE. Off course, the user managing the master encryption key must have SYSKM privilege. Whether we use root container or PDB to create the master encryption key, database should be open in read-write mode.
-- Password-based key store must be opened first

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY salman12  container=all;

keystore altered.

-- Create master encryption key from root container. WITH BACKUP clause creates an initial backup of keystore for root container as well as all pluggable databases. Location of backup is same as keystore location

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY salman12 WITH BACKUP USING 'initial_backup' CONTAINER = ALL ;

keystore altered.

-- In the following we can check encryption key created for root container as well as one  pluggable database that I have.

SQL> select KEY_ID, CREATOR_DBNAME,ACTIVATING_PDBNAME from v$encryption_keys;

KEY_ID                                                                                                                     CREATOR_DBNAME          ACTIVATING_PDBNAME
------------------------------------------------------------------------------ ---------------------------- ------------------------------------------------------------------
AWUgyr9yGk93v4UZQq/jSGwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    salman12c                              PDB
AdRgOwJjXU+lv+xXjJ5JFbEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA      salman12c                              CDB$ROOT

4)
Create an Auto-Login Software Keystore
Prerequisite for auto-login keystore is to create a password-based keystore first as explained above, and then create autologin for the keystore using method explained in this step. Before 12c, enabling of autologin for keystore was done using orapki utility but usage of orapki utility has been deprecated in 12c for this purpose.

SQL> ADMINISTER KEY MANAGEMENT CREATE  AUTO_LOGIN KEYSTORE FROM KEYSTORE 'C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C' IDENTIFIED BY salman12;

keystore altered.


-- Check status of the keystore. WALLET_TYPE column would show AUTOLOGIN

SQL> select WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;

WRL_PARAMETER                                                        STATUS                                            WALLET_TYPE   
---------------------------------------- ------------------------------ -------------------- ----------------------------------------------
C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C         OPEN_NO_MASTER_KEY              AUTOLOGIN        

-- To create a local auto-login keystore, use following syntax

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE 'C:\APP\ORACLE\ADMIN\WALLET\SALMAN12C' IDENTIFIED BY salman12;


As soon as we execute above statement, we will see cwallet.sso file gets created under keystore location directory. Once we have AUTO-LOGIN keystore, there is no need to open keystore for individual pluggable databases because auto-login keystore would open automatically for all pluggable databases as well.
Implementing TDE in a 12c database
Once master encryption key has been created, now it is time to encrypt our data at either table/column level, or tablespace level.
Creating a table with encrypted column
SQL> create table test_tde (id number,
   name varchar2(200),
   salary number encrypt);

Table created.

Creating an encrypted tablespace
SQL> CREATE TABLESPACE encrypted_tablespace
   DATAFILE 'C:\APP\ORACLE\ORADATA\SALMAN12C\ENCRYPTED_TABLESPACE01.DBF.dbf' SIZE 10M  ENCRYPTION USING 'AES256'    DEFAULT STORAGE (ENCRYPT);

Tablespace created.

All the tables created in this tablespace will have their data encrypted. Keystore needs to be open while accessing the data in encrypted tables/tablespaces.

Rotating Keystore Password and Master Encryption Key

Changing Keystore Password
For security reasons, it is a good practice to keep changing/rotating keystore password. Following is the command. Make sure that keystore is open, otherwise you will face ORA-28365: wallet is not open
-- “new_password” is the new password I am setting here.
SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY salman12 SET new_password WITH BACKUP USING 'backup_after password_change';

keystore altered.

After execution of above statement, a fresh backup of keystore is created under keystore location directory. You can also use “TO <backup_directory_location>” clause to specify a different location for backup. “USING” option is used to specify a string that will be part of the name of backup file.
Manually backing up software keystore
SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'TEST_BACKUP' IDENTIFIED BY new_password;

keystore altered.

Rotating/REKEY Master Encryption Key
Just like rotating keystore password, it is also a good practice to rotate/rekey the master encryption key. Following is the method to do this. Make sure that keystore is open all containers before executing this command, otherwise you can use CONTAINER=CURRENT to rotate the masker encryption key in root container, and then individually in other containers (PDBs)
SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY  IDENTIFIED BY new_password WITH BACKUP USING 'backup_after rekey' container=all;


-- In the following you can see that new master encryption key has been generated for root as well as for the PDB

SQL> select KEY_ID, CREATOR_DBNAME,ACTIVATING_PDBNAME from v$encryption_keys;

KEY_ID                                                                                                                                  CREATOR_DBNAME         ACTIVATING_PDBNAME
------------------------------------------------------------------------------ ------------------------------ -----------------------------------------------------------------
AemAdr+dq0/Iv1F2JEC4fOkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA        salman12c                               PDB
ATaB3WLfrU/lv+kLIgFL9RYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA      salman12c                               CDB$ROOT
AdRgOwJjXU+lv+xXjJ5JFbEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA       salman12c                              CDB$ROOT
AWUgyr9yGk93v4UZQq/jSGwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    salman12c                               PDB

Related Articles
TDE Related Error Messages


No comments:

Post a Comment