Transparent Data Encryption (TDE) for the Reviewer workspace in Oracle—ArcMap

Available with Data Reviewer license.

Oracle 12x
Oracle 11.2

You can take several precautions to help secure the database, such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.

Transparent Data Encryption (TDE) enables you to encrypt sensitive data, such as credit card numbers, stored in tables and tablespaces. Encrypted data is transparently decrypted for a database user or application that has access to data. TDE helps protect data stored on media in the event that the storage media or data file is stolen. Oracle 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 provides TDE. TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database.

With Oracle, new tablespaces can be defined as encrypted. Defining a tablespace as encrypted means the physical data files created on the operating system will be encrypted. Any tables, indexes, and other objects defined in the new tablespace will be encrypted by default with no additional storage space requirements. Data is automatically encrypted when it is written to disk and automatically decrypted when accessed by the application.

Benefits of using TDE include the following:

As a security administrator, you can be sure that sensitive data is safe in case the storage media or data file is stolen.
Implementing TDE helps you address security-related regulatory compliance issues.
You do not need to create triggers or views to decrypt data for the authorized user or application. Data from tables is transparently decrypted for the database user and application.
Database users and applications need not be aware that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and applications.
Applications need not be modified to handle encrypted data. Data encryption and decryption are managed by the database.
Key management operations are automated. The user or application does not need to manage encryption keys.

Oracle 12x

For more information on configuring TDE tablespace encryption, see the Oracle documentation.

Configuring TDE manually

The following steps show how to manually configure TDE.

Create the keystore directory.

mkdir $ORACLE_HOME/admin/$ORACLE_SID/wallet

Modify the SQLNET.ORA file if you want to manage the encryption wallet.
The default encryption wallet location is $ORACLE_BASE/admin/<global_db_name>/wallet. If you want to allow Oracle to manage a wallet in the default location, you don't need to set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.
For Windows
```
ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=C:/oracle/admin/%ORACLE_SID%/wallet/)))
```
For Linux
```
ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/app/oracle/admin/$ORACLE_SID/wallet/)))
```

Check the COMPATIBLE initialization parameter for the correct version number. It should be 12.x.

ORA> sqlplus /nolog
SQL> connect /as sysdba
Connected.
SQL> select instance_name,status,database_status from v$instance;
INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
mcs1              OPEN         ACTIVE

SQL> show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.0.0

Create the keystore.

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'C:\oracle\admin\mcs1\wallet' IDENTIFIED BY "mcs1$admin";

--the ewallet.p12 file, which contains the keystore, appears in the keystore location.

Open the password-based keystore.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs1$admin" CONTAINER=ALL;


-- check the status
SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

Back up a password-based software keystore.

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'keystore_bkp' IDENTIFIED BY "mcs1$admin";
SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

Create the master encryption key.

Create master key to CDB and all PDBs.

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "mcs1$admin" WITH BACKUP USING 'masterkey_all_bkp' CONTAINER=ALL;
SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;

Export the master key.

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mcs1.exp$admin" TO 'C:\oracle\admin\mcs1\wallet\masterkey_cdb_exp.bkp' IDENTIFIED BY "mcs1$admin";

Optionally create the master key for the current container. You can skip this step if you completed step 7.

Container Database (CDB):

ALTER SESSION SET CONTAINER = CDB$ROOT;
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "mcs1$admin" WITH BACKUP USING 'masterkey_cdb_backup' CONTAINER=CURRENT;
SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;
--export master key
ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mcs1.exp$admin" TO 'C:\oracle\admin\mcs1\wallet\masterkey_cdb_exp.bkp' IDENTIFIED BY "mcs1$admin";

Pluggable Database (PDB): revpdb

ALTER SESSION SET CONTAINER = revpdb;
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "mcs1$admin" WITH BACKUP USING 'masterkey_revpdb_backup' CONTAINER=CURRENT;
SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;
--export master key
ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mcs1.exp$admin" TO 'C:\oracle\admin\mcs1\wallet\masterkey_revpdb_exp.bkp' IDENTIFIED BY "mcs1$admin";

Check status

SELECT * FROM V$ENCRYPTION_WALLET;
SELECT * FROM V$ENCRYPTION_KEYS;
SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;
SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;

Set the Local Auto Login keystore.

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'C:\oracle\admin\mcs1\wallet' IDENTIFIED BY "mcs1$admin";

SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

--the cwallet.sso file appears in the keystore location. The ewallet.p12 file is the password-based wallet.
--Note:
--Do not remove the PKCS#12 wallet (ewallet.p12 file) after you create the auto login keystore (.sso file). 
--You must have the PKCS#12 wallet to regenerate or rekey the TDE master encryption key in the future. 
--By default, this file is located in the $ORACLE_HOME/admin/ORACLE_SID/wallet directory.

Open the auto login keystore.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN CONTAINER=ALL;


-- check the status
SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

It’s recommended that you verify the status of TDE configuration using the following SQL statements.

SELECT * FROM V$ENCRYPTION_WALLET;

SELECT * FROM V$ENCRYPTION_KEYS;

SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;
SELECT KEY_ID,KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;

SELECT KEY_ID FROM V$ENCRYPTION_KEYS;
SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;

SELECT WRL_PARAMETER FROM V$ENCRYPTION_WALLET;
SELECT STATUS FROM V$ENCRYPTION_WALLET;

SELECT * FROM V$ENCRYPTED_TABLESPACES;
SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;
SELECT * FROM DBA_ENCRYPTED_COLUMNS;

Oracle 11.2

For more information on configuring TDE tablespace encryption, see the Oracle documentation.

To use TDE, follow one of these methods.

Configuring TDE Oracle Enterprise Manager

The following steps show how to configure TDE using Oracle Enterprise Manager (OEM).

To use TDE, follow one of these methods.

Create the wallet folder.

mkdir C:\oracle\admin\wallets

OEM > login as sys / sysdba

OEM > Server > Transparent Data Encryption

Advanced Options > Change Location

	Host Credentials
	Username: <DOMAIN>\dbs_ora
	Password: xxxxxxx

	Configuration Method: File System
	
	Encryption Wallet Directory: C:\oracle\admin\wallets

	OK

Create Wallet > Local Auto-Open Wallet > Create

	Host Credentials
	Username: <DOMAIN>\dbs_ora
	Password: xxxxxxx

	Wallet Password:  walletadmin

	Continue

Back up the wallet folder.

cd C:\oracle\admin

zip -r wallets wallets

Configuring TDE manually

The following steps show how to manually configure TDE.

From the command window, create the wallet folder.
```
mkdir C:\oracle\admin\wallets
```
Add the wallet location to the sqlnet.ora file.
```
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = C:\oracle\admin\wallets\$ORACLE_SID)
```
Note:
The default encryption wallet location is $ORACLE_BASE/admin/<global_db_name>/wallet. If you want to allow Oracle to manage a wallet in the default location, there is no need to set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.

Using Oracle SQL*Plus or Oracle SQL Developer, generate a master key.

alter system set encryption key identified by "walletadmin";

Using Oracle SQL*Plus or Oracle SQL Developer, verify the status of the wallet.
```
select * from "v$encryption_wallet";
```

Set the wallet to auto login.

set ORACLE_SID=revdb

orapki wallet create -wallet C:\oracle\admin\wallets -auto_login -pwd walletadmin

From the command window, back up the wallet folder.
```
cd C:\oracle\admin

zip -r wallets wallets
```