BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 19c TDE Tips

2021-08-18
     

Oracle 19c TDE Tips

TDE(Transparent Data Encryption) as the name suggest transparently encrypts data at rest in Oracle Databases. It stops unauthorized attempts from the operating system to access database data stored in files, without impacting how applications access the data using SQL. So we dont have any impact to Business. If the malicious user tries to open file using a HEX editor (like UltraEdit),then only non-printable characters will be present. TDE can encrypt entire application tablespaces or specific sensitive columns.

  • Oracle TDE is available by default in Oracle RDBMS Enteprise Edition (not in SE or SE2 Edition), but you have to purchase an Oracle Advanced Security license to use it.
  • TDE is part of the Oracle Advanced Security, which also includes Data Redaction.

Test Steps

-- WALLET_ROOT is a static parameter used to specify the base location of wallet.
SYS@cdb1> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SYS@cdb1> show parameter wallet_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string
SYS@cdb1> alter system set wallet_root='$ORACLE_HOME/admin/cdb1/wallet' scope=spfile;

System altered.

SYS@cdb1> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@cdb1> startup
ORACLE instance started.

Total System Global Area 1577055352 bytes
Fixed Size                  9135224 bytes
Variable Size             704643072 bytes
Database Buffers          855638016 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SYS@cdb1> show parameter wallet_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/admin/cdb1/wallet
SYS@cdb1> 

-- tde_configuration is a dynamic parameter, no need to restart the database.
SYS@cdb1> show parameter tde_confi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string
SYS@cdb1> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;

System altered.

SYS@cdb1> show parameter tde_confi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      KEYSTORE_CONFIGURATION=FILE
SYS@cdb1> 


-- Create Password-Protected Keystore
SYS@cdb1> administer key management create keystore identified by oracle;

keystore altered.

SYS@cdb1> 

-- Create Auto-Login Keystore
SYS@cdb1> administer key management create auto_login keystore from keystore identified by oracle;

keystore altered.

SYS@cdb1> 

-- Check Keystore Files
[oracle@ol8-19c ~]$ ls -tlr /u01/app/oracle/product/19.0.0/dbhome_1/admin/cdb1/wallet
total 0
drwxr-x--- 2 oracle oinstall 44 Aug 18 09:46 tde
[oracle@ol8-19c ~]$ ls -tlr /u01/app/oracle/product/19.0.0/dbhome_1/admin/cdb1/wallet/tde
total 8
-rw------- 1 oracle oinstall 2555 Aug 18 09:45 ewallet.p12
-rw------- 1 oracle oinstall 2600 Aug 18 09:46 cwallet.sso
[oracle@ol8-19c ~]$ 

-- Open Keystore
-- To open password-protected keystore, we should use FORCE KEYSTORE clause, no matter which container you're in.
SYS@cdb1> administer key management set keystore open force keystore identified by oracle container=all;

keystore altered.

SYS@cdb1> 
-- there's nothing in the keystore. So next, let's set a TDE master key in the keystore.
SYS@cdb1> select con_id, wallet_type, status from v$encryption_wallet;

    CON_ID WALLET_TYPE          STATUS
---------- -------------------- ------------------------------
         1 PASSWORD             OPEN_NO_MASTER_KEY
         2 PASSWORD             OPEN_NO_MASTER_KEY
         3 PASSWORD             OPEN_NO_MASTER_KEY

SYS@cdb1> 

-- use the master key in all container and additionally backup the old keystore.
SYS@cdb1> administer key management set key force keystore identified by oracle with backup container=all;

keystore altered.

SYS@cdb1> 

-- There is backup for the old password-protected keystore.--> ewallet_2021081801495192.p12
[oracle@ol8-19c ~]$ ls -tlr /u01/app/oracle/product/19.0.0/dbhome_1/admin/cdb1/wallet/tde
total 20
-rw------- 1 oracle oinstall 2555 Aug 18 09:49 ewallet_2021081801495192.p12
-rw------- 1 oracle oinstall 5467 Aug 18 09:49 ewallet.p12
-rw------- 1 oracle oinstall 5512 Aug 18 09:49 cwallet.sso
[oracle@ol8-19c ~]$ 


SYS@cdb1> select con_id, wallet_type, status from v$encryption_wallet;

    CON_ID WALLET_TYPE          STATUS
---------- -------------------- ------------------------------
         1 PASSWORD             OPEN
         2 PASSWORD             OPEN
         3 PASSWORD             OPEN

SYS@cdb1> 

-- To start using the auto-login keystore, we should close the password-protected keystore.

SYS@cdb1> select con_id, wallet_type, status from v$encryption_wallet;

    CON_ID WALLET_TYPE          STATUS
---------- -------------------- ------------------------------
         1 PASSWORD             OPEN
         2 PASSWORD             OPEN
         3 PASSWORD             OPEN

SYS@cdb1> administer key management set keystore close identified by oracle container=all;

keystore altered.

SYS@cdb1> select con_id, wallet_type, status from v$encryption_wallet;

    CON_ID WALLET_TYPE          STATUS
---------- -------------------- ------------------------------
         1 AUTOLOGIN            OPEN
         2 AUTOLOGIN            OPEN
         3 AUTOLOGIN            OPEN

SYS@cdb1> 

SYS@cdb1>  SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                                  STATUS WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC  CON_ID
-------- -------------------------------------------------------------- ------ -------------------- --------- -------- --------- -------
FILE     /u01/app/oracle/product/19.0.0/dbhome_1/admin/cdb1/wallet/tde/ OPEN   AUTOLOGIN            SINGLE    NONE     NO              1
FILE                                                                    OPEN   AUTOLOGIN            SINGLE    UNITED   NO              2
FILE                                                                    OPEN   AUTOLOGIN            SINGLE    UNITED   NO              3

SYS@cdb1>

-- if RAC, need create wallet/tde folder and copy cwallet.sso/ewallet.p12 file to this folder.

-- TEST create tde tablespace

[oracle@ol8-19c ~]$ sqlplus sys/SysPassword1@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 18 09:56:14 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SYS@pdb1> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO
SYS@pdb1> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/pdb1/sample01.dbf
/u02/oradata/CDB1/pdb1/test01.dbf

6 rows selected.

SYS@pdb1> create tablespace no_encry_tbs datafile '/u02/oradata/CDB1/pdb1/no_encry_tbs01.dbf' size 10m autoextend on;

Tablespace created.

SYS@pdb1> 

SYS@pdb1> create tablespace encry_tbs datafile '/u02/oradata/CDB1/pdb1/encry_tbs01.dbf' size 10m autoextend on encryption encrypt;

Tablespace created.

SYS@pdb1> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/pdb1/sample01.dbf
/u02/oradata/CDB1/pdb1/test01.dbf
/u02/oradata/CDB1/pdb1/no_encry_tbs01.dbf
/u02/oradata/CDB1/pdb1/encry_tbs01.dbf

8 rows selected.

SYS@pdb1> 

SYS@cdb1> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
SAMPLE                         NO
TEST                           NO
NO_ENCRY_TBS                   NO
ENCRY_TBS                      YES

9 rows selected.

SYS@cdb1> 

Ref

Configuring Transparent Data Encryption (TDE) with Oracle 12c

Have a good work&life! 2021/08 via LinHong


Similar Posts

Comments