TDE Encryption with Wallet/keystore

1. Create a wallet/keystore location.

# mkdir -p /u01/wallet/

2. update the wallet/keystore location in sqlnet.ora

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

# sqlnet.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)
WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/wallet)))
SQLNET.WALLET_OVERRIDE=TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

3. Create keystore:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/wallet/' IDENTIFIED BY walletpass#123;

keystore altered.

SQL> host ls /u01/wallet/
ewallet.p12

4. Now open the keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123;

5. Now activate the key:

SQL> SET LINESIZE 100
SQL> SELECT con_id, key_id FROM v$encryption_keys;

no rows selected

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP;
SQL> SELECT con_id, key_id FROM v$encryption_keys;

    CON_ID KEY_ID
--------------------------------------------------------------
     0 AV1MvOL6/E9rv2XjHwxjU/YAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> SET LINESIZE 200
SQL> COLUMN wrl_parameter FORMAT A50
SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- ------------ ------- ----------- --------- --------- ------
FILE     /u01/wallet/  OPEN    PASSWORD    SINGLE    NO         0

6. Create a encrypted a tablespace:

SQL> CREATE TABLESPACE TEST_ENCRY
datafile '/u01/oradata/DBTEST/testencry.dbf' SIZE 512M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ENCRYPTION USING 'AES256'
DEFAULT   STORAGE (ENCRYPT)
ONLINE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SQL> create table emp_ency(
empno   Number(3),
Name     varchar(10)
) tablespace TEST_ENCRY; 

7. Create Encrypt User:

  CREATE USER EUSER
  IDENTIFIED BY EUSER
  DEFAULT TABLESPACE TEST_ENCRY
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  GRANT CREATE SEQUENCE TO EUSER;
  GRANT CREATE SESSION TO EUSER;
  GRANT CREATE SYNONYM TO EUSER;
  GRANT CREATE TABLE TO EUSER;
  GRANT CREATE TABLESPACE TO EUSER;
  GRANT CREATE TRIGGER TO EUSER;
  GRANT CREATE TYPE TO EUSER;
  GRANT CREATE VIEW TO EUSER;
  GRANT UNLIMITED TABLESPACE TO EUSER;

8. Create a table with encrypted column:

CREATE TABLE employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER,
     salary NUMBER(6) ENCRYPT
);

SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='EMPLOYEE';
select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='EMPLOYEE';
                                   
OWNER       TABLE_NAME    COLUMN_NAME   ENCRYPTION_ALG
----------- ------------- ------------- -----------------
EUSER       EMPLOYEE       SALARY        AES 192 bits key

SQL>

9. ENABLE AUTOLOGIN :

SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='TEST_ENCRY';

TABLESPACE_NAME            ENC
-------------------------- ---
TEST_ENCRY                 YES

SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- ------------ ------- ----------- --------- --------- ------
FILE     /u01/wallet/  OPEN    PASSWORD    SINGLE    NO         0

==================Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet explicitly.
To avoid this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/wallet/' IDENTIFIED BY walletpass#123;

keystore altered.
 
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- ------------ ------- ----------- --------- --------- ------
FILE     /u01/wallet/  OPEN    PASSWORD    SINGLE    NO         0

SQL>
SQL> startup force
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size     2929936 bytes
Variable Size   570428144 bytes
Database Buffers   260046848 bytes
Redo Buffers     5455872 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- ------------ ------- ----------- --------- --------- ------
FILE     /u01/wallet/ OPEN    AUTOLOGIN   SINGLE     NO       0

10. Related dictionary tables for TDE:

- What tables contain TDE encrypted columns?
sql> select table_name, column_name from dba_encrypted_columns;

- What tables are stored in TDE encrypted tablespaces?
sql> select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES';

- What indexes are stored in TDE encrypted tablespaces?
sql> select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES' and index_name not like 'SYS_IL%';

- getting key/wallet details:
SQL> SELECT * FROM v$encryption_wallet;
SQL> SELECT con_id, key_id FROM v$encryption_keys;