Implementing Oracle Transparent Data Encryption (TDE) in Single Instance Oracle dSources and VDBs (KBA1389)

Establishing the Oracle Wallet in the dSource

In order for the Transparent Data Encryption to function it requires a wallet be built which will hold the encryption key information used to perform the encryption and decryption of data.

As the location of wallet is going to be linked with the databases unique name the value for this needs to be confirmed.

The following will need to be performed as the owner of the Oracle home.

1. Determine the db_unique_name value for the database.

Log in to the database via sqlplus and execute the show parameter command below

sqlplus / as sysdba
SQL> show parameter unique
NAME                                 TYPE          VALUE
------------------------------------ -----------   ------------------------------
db_unique_name                       string        rh7si11g

2. Set an environment variable using the db_unique_name value.

Export an operating system environment variable called ORACLE_UNQNAME and set it to this value

$ export ORACLE_UNQNAME=rh7si11g

3. Set up the wallet location in sqlnet.ora.

Set the wallet location in the SQLNET.ORA used by the database by placing an entry for the SQLNET directive  ENCRYPTION_WALLET_LOCATION into the file. This file us typically located in ORACLE_HOME/network/admin/sqlnet.ora but may be located using the environment variable TNS_ADMIN if it is located in a non-default location. Oracle databases started using this ORACLE_HOME's binaries will examine the sqlnet.ora file during startup and use the value associated with ENCRYPTION_WALLET_LOCATION to locate the wallet and open it for TDE use.

$ vi $ORACLE_HOME/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCP)

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/)))

4. Create a directory to hold the Oracle wallet.

Create a directory for the wallet using the same value in the DIRECTORY directive from the sqlnet.ora.

$ mkdir -p /u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet

5. Create the Oracle wallet.

$ orapki wallet create -wallet /u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/ -pwd delphix123 -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

The wallet should now be present as the following files:

[ora11204@rh73-ora-src ~]$ ls -l /u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/

-rw-------. 1 ora11204 oinstall 3589 Jun 13 18:06 cwallet.sso
-rw-------. 1 ora11204 oinstall 3512 Jun 13 18:06 ewallet.p12

6. Identifying the new wallet is in use.

A restart of the dSource database may be required for it to pick up and start using the new wallet.  To determine that wallet has been successfully opened by the database start sqlplus and query v$encryption_wallet :

SQL> set lines 132
SQL> col wrl_parameter format a50
SQL> select * from v$encryption_wallet;

WRL_TYPE         WRL_PARAMETER                                              STATUS
---------- -------------------------------------------------- ------------------
file             /u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/    OPEN_NO_MASTER_KEY

7. Set the wallet master key

Initially the wallet will be open but no master key is set. To set the master key issue the following.

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "delphix123";

System altered.

Testing the TDE configuration in the dSource

1. Create a tablespace where everything stored in that tablespace is encrypted as the oracle SYS user.

SQL> connect / as sysdba
Connected.
SQL> create tablespace obfuscated_tbs datafile size 100M encryption using 'AES128' default storage (encrypt);

2. Establish a user to create and own the encrypted table as the SYS user.

SQL> alter user scott identified by tiger account unlock;
User altered.

SQL> alter user scott quota unlimited on obfuscated_tbs;
User altered.

3. Connect as the scott user and create a table placing it in the encrypted tablespace.

SQL> connect scott/tiger
Connected.

SQL> create table obsfucat (key1 number, name varchar2(30)) tablespace obfuscated_tbs;
Table created.

4. Insert some data into the encrypted table

SQL> insert into obsfucat values (1111,'Paul');
SQL> commit;

5. Select from the new table

SQL> select * from scott.obsfucat;

      KEY1 NAME
---------- ------------------------------
      1111 Paul

Preparing TDE in the VDB Target Home

Configure /etc/oratab with an entry for the Oracle Home the VDB is to be provisioned against:

1. Add an entry for the home to be provisioned to in oratab to make life a little easier when it comes to setting up environments. Set the ORACLE_HOME to the VDB's home using this new oratab entry.  Note do not use the VDB's name for the entry as it may prevent the VDB's name from being re-used during the provision process.

[ora11204@rh73-ora-tgt ~]$ vi /etc/oratab
[ora11204@rh73-ora-tgt ~]$ . oraenv
ORACLE_SID = [ora11204] ? o11204

The Oracle base has been set to /u01/app/ora11204

2. Add the entry to sqlnet.ora required to point the VDB's using this home to appropriate wallet locations.

$ cd $ORACLE_HOME/network/admin
$ vi sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCP)

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/)))

3. Export/set the environment variable ORACLE_UNQNAME to match the name of the proposed VDB, in this case a VDB called vdb1.

$ export ORACLE_UNQNAME=vdb1

4. Create a wallet directory based on this environment variable ORACLE_UNQNAME

$ mkdir -p /u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/
$ ls -lart /u01/app/ora11204/admin/
total 0
drwxrwxr-x. 6 ora11204 oinstall 65 Jun 15 22:52 ..
drwxr-xr-x. 3 ora11204 oinstall 20 Jun 15 22:52 vdb1
drwxr-xr-x. 3 ora11204 oinstall 18 Jun 15 22:52 .

5. Copy the wallet from the source environment and place it in the wallet location for the VDB. In this scp is being used.

$ scp ora11204@plb11204.dc1:/u01/app/ora11204/admin/rh7si11g/wallet/* /u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/
The authenticity of host 'plb11204.dc1 (10.43.42.240)' can't be established.
ECDSA key fingerprint is d6:81:c0:ad:44:13:a3:56:f7:5d:d6:b2:22:00:c1:30.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'plb11204.dc1,10.43.42.240' (ECDSA) to the list of known hosts.
ora11204@plb11204.dc1's password:
cwallet.sso                                  100% 4298     4.2KB/s   00:00    
ewallet.p12                                  100% 4221     4.1KB/s   00:00    

$ ls -l /u01/app/ora11204/admin/$ORACLE_UNQNAME/wallet/

-rw-------. 1 ora11204 oinstall 4298 Jun 15 22:54 cwallet.sso
-rw-------. 1 ora11204 oinstall 4221 Jun 15 22:54 ewallet.p12

6. Provision the VDB using a unique name of vdb1