EBS : Transparent Data Encryption (TDE) implementation

1. Add the below entry in the sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
  (SOURCE= (METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/oradb/oracle/11.2.0.3/admin/<CONTEXT_NAME>/encryption_wallet/)
))


2. Create the directory to contain the encrypted key:

mkdir -p /oradb/oracle/11.2.0.3/admin/<CONTEXT_NAME>/encryption_wallet/


3. Creating the wallet with password "g00g1e": login as sysdba

Syntax:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY <password>;

Eg:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "g00g1e";

4. Encrypting the columns of the table: login as sysdba

Syntax:
ALTER TABLE owner.table_name MODIFY(column_name ENCRYPT NO SALT);
ALTER TABLE owner.table_name MODIFY(column_name ENCRYPT);

Eg:
ALTER TABLE HR.PER_ALL_PEOPLE_F MODIFY(NATIONAL_IDENTIFIER encrypt no salt);
ALTER TABLE HR.PQP_EXT_CROSS_PERSON_RECORDS MODIFY(NATIONAL_IDENTIFIER encrypt no salt);
ALTER TABLE BEN.BEN_PL_F MODIFY(NAME  encrypt);

5. Once you added the columns to be encrypted close the wallet using below command: login as sysdba

Syntax:
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY <password>;

Eg: 
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "g00g1e";

6. Opening the wallet: login as sysdba
Syntax: 
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY <password>;

Eg: 
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "g00g1e";

7. How to check whether the wallet is open / closed : login as sysdba

SQL> COL WRL_PARAMETER FOR A65
SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                                     STATUS
-------------------- ----------------------------------------------------------------- ------------------
file                 /oradb/oracle/11.2.0.3/admin/TEST_drebsdb01/encryption_wallet/    OPEN


8. SQL to check what are the table colums that are encrypted : login as sysdba

SQL> COL OWNER FOR A5
SQL> COL COLUMN_NAME FOR A20
SQL> COL ENCRYPTION_ALG FOR A16
SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME                     COLUMN_NAME          ENCRYPTION_ALG   SAL
----- ------------------------------ -------------------- ---------------- ---
HR    PER_ALL_PEOPLE_F               NATIONAL_IDENTIFIER  AES 192 bits key NO
HR    PQP_EXT_CROSS_PERSON_RECORDS   NATIONAL_IDENTIFIER  AES 192 bits key NO
BEN   BEN_PL_F                       NAME                 AES 192 bits key NO
BEN   BEN_OPT_F                      NAME                 AES 192 bits key NO
BEN   BEN_REPORTING                  NATIONAL_IDENTIFIER  AES 192 bits key YES
HR    GHR_MASS_ACTIONS_PREVIEW       NATIONAL_IDENTIFIER  AES 192 bits key YES
HR    GHR_RIF_REGISTERS              NATIONAL_IDENTIFIER  AES 192 bits key YES
HRI   EDW_HR_PERM_ASSIGN_LSTG        NATIONAL_IDENTIFIER  AES 192 bits key YES
HRI   EDW_HR_PERM_ASSIGN_LTC         NATIONAL_IDENTIFIER  AES 192 bits key YES
HR    HR_H2PI_EMPLOYEES              NATIONAL_IDENTIFIER  AES 192 bits key YES
BEN   BEN_PRTT_ENRT_RSLT_F           BNFT_AMT             AES 192 bits key YES

9. When the Wallet is close we cannot access the data from backend:
SQL> SELECT NATIONAL_IDENTIFIER FROM HR.PER_ALL_PEOPLE_F WHERE NATIONAL_IDENTIFIER IS NOT NULL;
SELECT NATIONAL_IDENTIFIER FROM HR.PER_ALL_PEOPLE_F WHERE NATIONAL_IDENTIFIER IS NOT NULL
                                   *
ERROR at line 1:
ORA-28365: wallet is not open

Note: We need to open the wallet in order to view the values of the columns table that are encrypted.