Oracle TDE Wallet Management And Creating Oracle wallet in 12C

 

TDE (Transparent Data Encryption) Wallet Management

Introduction

Oracle Advanced Security Option (ASO) is an Oracle database option used to safeguard sensitive data and address regulatory compliance requirements. ASO requires additional licenses and can only be used with Oracle Enterprise Edition.

 

Advanced Security Database Features

§  Transparent Data Encryption (TDE) Transparent Data Encryption (TDE) stops would-be attackers from bypassing the database and reading sensitive information directly from storage by enforcing data-at-rest encryption in the database layer.

§  Data Redaction Data Redaction complements TDE by reducing the risk of unauthorized data exposure in applications, redacting sensitive data before it leaves the database.  It Provides an easy way to protect sensitive information that is displayed by replacing it on-the-fly with valid redacted data, while keeping the application running.

What is TDE?

 

Ø  An oracle advanced security feature that allows to encrypt data-at-rest completely transparent to applications.

Ø  It is not an access control mechanism for Oracle database users.

Ø  Notice that the data is encrypted only at rest-when the database server processes the data in the SQL layer, data records are decrypted and processed.

 

Why TDE/Encryption?

 

Ø  If attackers can gain access to the operating system as a powerful user (e.g. root or oracle), they can bypass the database and have direct access to data. Encryption can protect database files stored in the disk.

Ø  Also, many regulatory compliance requires encryption data at rest.

Encryption Options Available

 

·         DBMS_CRYPTO –client side encryption

·         TDE

-      Column encryption(10gR2 onwards)

-      Tablespace encryption(11gR1 onwards)

 

·         In this post, we only look at TDE.

TDE Workflow

 

1.   Setup wallet and master key

2.   Identify

-      Tables with sensitive columns

-      Tablespace with sensitive tables

 

3.   Open Wallet

4.   Encrypt

-      The identified columns

-      The identified tablespaces

 

5.   Close wallet

Oracle Wallet

 

Ø A PKCS#12 formatted file residing outside of the database(residing in the file system)

Ø Encrypted using password based encryption as defined in PKCS#5

Ø Holds the TDE master key

Ø It is a good practice to setup the wallet outside of the $ORACLE_BASE and grant minimal privileges to the wallet folder.

v  Creating a Wallet in 12C & Tablespace Encryption

To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:

1: Create a location for wallet files (key store location)  #mkdir -p /u01/app/oracle/product/12.1.0/db_1/network/admin/wallet #chown oracle:oinstall /u01/app/oracle/product/11.2.0/db_1/network/admin/wallet #chmod -Rf 700 /u01/app/oracle/product/12.1.0/db_1/network/admin/wallet

2: Specify wallet location using the sqlnet.ora ADD ENCRYPTION_WALLET_LOCATION:    ENCRYPTION_WALLET_LOCATION=   (SOURCE=   (METHOD=FILE)    (METHOD_DATA=    (DIRECTORY=/u01/app/oracle/product/12.1.0/db_1/network/admin/wallet)))

3: Initialize and Create the master key in SQL*PLUS in CDB$ROOT:   SQL> Administer KEY MANAGEMENT CREATE KEYSTORE ‘/u01/app/oracle/product/12.1.0/db_1/network/admin/wallet’ IDENTIFIED BY password;     Note: This creates a file called ewallet.p12 in the wallet directory     At this stage the status of the key store would be closed SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET; WRL_TYPE             STATUS     WALLET_TYPE       WALLET_OR      FULLY_BAC        CON_ID              WRL_PARAMETER --------------------        -------------   -----------------     ---------------        --------- --------  - ----------         ------------------------------ FILE                 CLOSED              UNKNOWN                  SINGLE              UNDEFINED          0   /u01/app/oracle/product/12.1.0                                                                                                                                                                  /db_1/network/admin/wallet

4: Opening the wallet          Once the wallet is open, the master key becomes available to the database   SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Asoalam#123 CONTAINER = ALL;   OR -  SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Asoalam#123;

Once the wallet is open, you can perform TDE operations -      Column encryption -      Tablespace encryption            v$encryption_wallet view shows the wallet status              SQL> Select wrl_parameter,status,con_id from  v$encryption_wallet;   SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER        from v$ENCRYPTION_WALLET;   WRL_TYPE             STATUS                      WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID    WRL_PARAMETER --------------------     ----------------------- - -------------------- -       -------- --------- ----------           -----------              ---------------- FILE                 OPEN_NO_MASTER_KEY         PASSWORD          SINGLE    UNDEFINED          0         /u01/app/oracle/product/12.1.0                                                                                                                                                                       /db_1/network/admin/wallet

         

 

5. Usually at this stage auto login key store is also created. But this lead to an issue (refer 1944507.1).    Therefore master encryption key is created before creating auto login wallet.   SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ASOALAM_123 with backup ; keystore altered.   SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;   WRL_TYPE        STATUS         WALLET_TYPE    WALLET_OR    FULLY_BAC              CON_ID                  WRL_PARAMETER ---------------      ------------------ --------------------        ---------------      -----------------         ------------              ------------------------------ FILE                   OPEN                PASSWORD           SINGLE                    NO                           0                       /u01/app/oracle/product/12.1.0                                                                                                                                                                                /db_1/network/admin/wallet

This will change wallet status from open_no_master_key to open.   SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ASOALAM_123 with backup ; keystore altered.   SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;   WRL_TYPE    STATUS    WALLET_TYPE    WALLET_OR    FULLY_BAC            CON_ID                  WRL_PARAMETER ---------------    ------------- ------------------     ---------------      -----------------                ------------------              ------------------------------ FILE                OPEN      PASSWORD      SINGLE                           NO                              0                   /u01/app/oracle/product/12.1.0                                                                                                                                                                                                      /db_1/network/admin/wallet

6. Finally create the auto login key store so that key store is auto opened when the database starts   SQL> ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/product/12.1.0/db_1/network/admin/wallet' identified by ASOALAM_123;   keystore altered.  Result of this is a key store file (wallet file). [oracle@wiz12c wallet]$ ll total 12 -rw-r--r-- 1 oracle oinstall 4112 Dec 12 15:29 ewallet.p12 SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;   WRL_TYPE             STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID          WRL_PARAMETER -------------------- ------------------------------ -------------------- ---------------------------------    -------- --------- --    -------- ------------------------------ FILE                 OPEN                           PASSWORD                            SINGLE                NO                 0              /u01/app/oracle/product/12.1.0                                                                                                                                                                                 /db_1/network/admin/wallet   SQL> select * from v$ENCRYPTION_WALLET;       WRL_TYPE             WRL_PARAMETER                  STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID -------------------- ------------------------------ ------------------------------       --------------------          --------------       ---------------     ---------- FILE                 /u01/app/oracle/product/12.1.0 OPEN                           PASSWORD             SINGLE                   NO                 0                      /db_1/network/admin/wallet   This will create the cwallet.sso file and wallet type will be changed to local_autologin    [oracle@wiz12c wallet]$ ll total 20 -rw-r--r-- 1 oracle oinstall 4165 Dec 12 15:33 cwallet.sso -rw-r--r-- 1 oracle oinstall 2408 Dec 12 15:29 ewallet_2018021209590844.p12 -rw-r--r-- 1 oracle oinstall 4112 Dec 12 15:29 ewallet.p12 [oracle@wiz12c wallet]$

 

Following test could be used to verify TDE is working. In this case a tablespace is created with encryption enabled. Few rows are inserted to tables created in those table spaces. At times buffer cache flushing or manual check points may be required to force database writer to write to data files   Create two tablespace 1) noencrypt_data                                           2) encrypt_data SQL> create tablespace noencrypt_data datafile '/u01/app/oracle/oradata/wiz12c/noencrypt_data.dbf' size 100M; Tablespace created.   SQL> create tablespace encrypt_data datafile '/u01/app/oracle/oradata/wiz12c/encrypt_data.dbf' size 100m ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT); Tablespace created.   SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;                                                     TABLESPACE_NAME           ENC ------------------------------        --- NOENCRYPT_DATA              NO ENCRYPT_DATA                   YES   2 rows selected.   Create table enctable for tablespace encrypt_data and nonecntbale tablespace noencrypt_data create table enctable (a varchar2(100)) tablespace encrypt_data; create table nonecntbale (a varchar2(100)) tablespace noencrypt_data;   Few rows are inserted to tables created in those table spaces   SQL> begin   2    for i in 1 .. 5   3    loop   4    insert into enctable values ('top secret text');   5    insert into nonecntbale values ('top secret text');   6    end loop;   7    commit;   8    end;   9  /   PL/SQL procedure successfully completed.   SQL> select * from enctable;   A ---------------------------------------------------------------------------------------------------- top secret text top secret text top secret text top secret text top secret text   5 rows selected.   SQL> select * from nonecntbale; A ---------------------------------------------------------------------------------------------------- top secret text top secret text top secret text top secret text top secret text   5 rows selected. SQL> alter system checkpoint;   System altered.   Strings command is used to look into the data file content. First the data file belonging to non-encrypted tablespace   strings /u01/app/oracle/oradata/wiz12c/noencrypt_data.dbf strings /u01/app/oracle/oradata/wiz12c/encrypt_data.dbf   SQL> !strings /u01/app/oracle/oradata/wiz12c/noencrypt_data.dbf }|{z WIZ12C NOENCRYPT_DATA top secret text, top secret text, top secret text, top secret text, top secret text,   Secondly the data file belonging to the encrypted tablespace   SQL> !strings /u01/app/oracle/oradata/wiz12c/encrypt_data.dbf }|{z WIZ12C ENCRYPT_DATA .FWW P*L&. 4S97 XY?U& +Z_0 GCIbT NQJR YdLr@9 @_r6 d/Y5 d!N;)         Q1>   As seen above encrypted tablespace doesn't show it's content in clear text.

Risk when using Transparent Data Encryption

LOST OF AUTOLOGIN WALLET   Deleted the file cwallet.sso (the autologin wallet) on the o.s.-level. Result:   SQL> select * from emp; –> no problem reading the data, as expected, it’s just the auto-login wallet.   - Shutdown , startup database: no problem with starting the database   - SQL> select * from emp; ---> ORA-28365: wallet is not open.   - SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “<password>”;   - SQL> select * from emp; --> works again.

LOST OF WALLET WITH MASTER KEY.   Deleted the ewallet.p12 too. Result:   SQL> select * from emp; --> No problem in reading the data, key is read from the database (but when will I find out I’ve lost my wallet?).   - SQL> Shutdown, startup database: --> No problem to startup. No errors in alert-file also.     - SQL> select * from emp; –> This gives ORA-28365: wallet is not open

Backup is done of ewallet.p12 by OS Backup Backup is done every day. -     So Restore can be done.   Backup is done of cwallet.sso. Separated from ewallet.p12 -     So Restore can be done.

Physical standby database • Yes it works • As long as the wallet is available on standby site. After creating wallet for primary database redo apply on standby stops immediately. You see the following in the alert file of the standby database.       Apply redo for database master key re-key failed: new master key does not exist in the keystore MRP0: Background Media Recovery terminated with error 28374 Errors in file /u01/app/oracle/diag/rdbms/test1_01/TEST1_1/trace/TEST1_1_pr00_8912.trc: ORA-28374: typed master key not found in wallet Mon DEC 20 16:32:17 2020 Managed Standby Recovery not using Real Time Apply Recovery interrupted!       solution: copy wallet to standby site’s

Rekey Wallet -- How do I change (rotate, re-key) the encryption keys? . First copy the current wallet files to backup directory . change wallet password   $ orapki wallet change_pwd -wallet /u01/app/oracle/admin/TEST1_02/tde_wallet   Oracle PKI Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: New password:   . Now change the master key SYS@TEST1_1 SQL> alter system set encryption key identified by "Secret"; System altered.   . Now copy wallet files to other nodes for Rac or candidate servers for Rac-One.