One of my previous article 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.
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.
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