Oracle allows TDE (Transparent Data Encryption) for specific (i.e. selected) columns or a full Tablespace.
(I have also presented the steps for TDE in SQL Server).
Here is a quick demo of TDE for a Tablespace.
First, I setup a target tablespace with some data
SQL> connect hemant/hemant Connected. SQL> create tablespace TDE_TARGET_TBS datafile '/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf' size 100M; Tablespace created. SQL> SQL> create table TDE_TARGET_TABLE 2 (id_col number, 3 data_col varchar2(50)) 4 tablespace TDE_TARGET_TBS 5 / Table created. SQL> insert into TDE_TARGET_TABLE 2 select rownum, 3 'MY DATA CONTENT : ' || rownum 4 from dual 5 connect by level "less than" 1001 -- the "less than" symbol replaced by string to preserve formatting for HTML 6 / 1000 rows created. SQL> commit; Commit complete. SQL> oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS.TXT 12673+0 records in 202768+0 records out 103817216 bytes (104 MB) copied, 1.10239 s, 94.2 MB/s oracle19c> oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' |head -5 MY DATA CONTENT : 944, MY DATA CONTENT : 945, MY DATA CONTENT : 946, MY DATA CONTENT : 947, MY DATA CONTENT : 948, oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' | wc -l 1000 oracle19c>
I inserted 1000 rows with the text "MY DATA CONTENT" and it is visible as plain-text when I dump the datafile.
Note how not all the inserted rows appear to be in physical order -- the "last" 57 "records" (i.e. rows) seem to appear before the first "record" (row) as I show in this video recording of a viewing of the dump. Never assume physical ordering of data in a datafile or when retrieving output (for ordering the results of a SELECT statement, *always* use the ORDER BY clause)
So, I now intend to encrypt the tablespace.
Step 1 : Specify the ENCRYPTION WALLET LOCATION
In earlier releases, this is specified in the sqlnet.ora file like this :
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/home/oracle/wallet))) -- or this could be any other folder, or defaulting to $ORACLE_BASE/admin/db_unique_name/wallet
However, in 19c, Oracle recommends using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter after setting the WALLET_ROOT.
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string
SQL>
SQL> alter system set wallet_root='/opt/oracle/product/19c/dbhome_1/TDE_WALLETS' scope=SPFILE;
System altered.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /opt/oracle/product/19c/dbhome
_1/TDE_WALLETS
SQL>
SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=SPFILE;
System altered.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /opt/oracle/product/19c/dbhome
_1/TDE_WALLETS
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL>
In the case of this database, since I had not earlier configured these parameters, I had to do a manual restart for them to take effect (Note : "WALLET_ROOT" must have been configured before "TDE_CONFIGURATION" can be set, that is why I had to do an additional restart between setting the two parameters).
I have deliberately configured WALLET_ROOT to a non-default/standard location.
Step 2 : Create the KEYSTORE (under WALLET_ROOT)
This is where I actually create the Wallet. The syntax specifies KEYSTORE location, but can default to WALLET_ROOT as I have already defined it. I can also create an Auto-Login Keystore
SQL> administer key management create keystore identified by mysecretpassword; keystore altered. SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde total 4 -rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12 SQL> SQL> administer key management create LOCAL auto_login keystore 2 from keystore '/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde' 3 identified by mysecretpassword 4 / keystore altered. SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde total 8 -rw-------. 1 oracle oinstall 2600 Nov 21 18:49 cwallet.sso -rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12 SQL> SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER ------------------------------------------------------------------------------------------------------------------------------------ STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ------------------------------ -------------------- --------- -------- --------- ---------- FILE /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/ OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 0 SQL>
In this case, "ewallet.p12" is the Password Protected Keystore and "cwallet.sso" is the Auto-Login Keystore (created LOCALly only, not for remote servers/clients).
Step 3 : OPEN the Keystore (only if it is NOT already OPEN)
I can see that the Keystore is already OPEN (from the query on v$encryption_wallet) but I could attempt OPENing it with :
SQL> administer key management set keystore open 2 identified by mysecretpassword 3 / administer key management set keystore open * ERROR at line 1: ORA-28354: Encryption wallet, auto login wallet, or HSM is already open SQL> SQL> !oerr ora 28354 28354, 0000, "Encryption wallet, auto login wallet, or HSM is already open" // *Cause: Encryption wallet, auto login wallet, or HSM was already opened. // *Action: None. // SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER ------------------------------------------------------------------------------------------------------------------------------------ STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ------------------------------ -------------------- --------- -------- --------- ---------- FILE /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/ OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 0 SQL>
Step 4 : Setup the Master Encryption Key
Now, I setup the Master Key (and also backup the existing key file)
SQL> administer key management set key 2 using tag 'For_Tablespace_TDE' 3 force keystore -- because I am using LOCAL_AUTOLOGIN 4 identified by mysecretpassword 5 with backup using 'tde_key_backup' 6 / keystore altered. SQL> SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde total 20 -rw-------. 1 oracle oinstall 4232 Nov 21 19:04 cwallet.sso -rw-------. 1 oracle oinstall 2555 Nov 21 19:04 ewallet_2020112111043216_tde_key_backup.p12 -rw-------. 1 oracle oinstall 4171 Nov 21 19:04 ewallet.p12 SQL> SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER ------------------------------------------------------------------------------------------------------------------------------------ STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ------------------------------ -------------------- --------- -------- --------- ---------- FILE /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 0 SQL> SQL> select key_id, creation_time, keystore_type, tag from v$encryption_keys; KEY_ID ------------------------------------------------------------------------------ CREATION_TIME KEYSTORE_TYPE --------------------------------------------------------------------------- ----------------- TAG ------------------------------------------------------------------------------------------------------------------------------------ AaHRyuP8yE+ivzI/hZHcOdoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 21-NOV-20 07.04.32.347090 PM +08:00 SOFTWARE KEYSTORE For_Tablespace_TDE SQL>
Now I am ready the encrypt my Tablespace.
Step 5 : Online Encryption of Tablespace
This is a 19c feature, earlier versions required Offline Encryption.
This method creates a new datafile with encrypted data
SQL> alter tablespace TDE_TARGET_TBS
2 encryption online
3 using 'AES192'
4 encrypt file_name_convert = ('/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf','/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf')
5 /
Tablespace altered.
SQL>
oracle19c>pwd
/opt/oracle/oradata/HEMANT
oracle19c>ls -l TDE*
-rw-r-----. 1 oracle oinstall 104865792 Nov 21 19:42 TDE_TARGET_TBS_encrypted.dbf
oracle19c>
oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 0.414517 s, 250 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT |grep 'CONTENT' |head -5
oracle19c>
SQL> l
1 select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status
2 from v$tablespace t, v$encrypted_tablespaces e
3* where t.ts#=e.ts#
SQL> /
NAME TS# ENCRYPT ENC KEY_VERSION STATUS
------------------------------ ---------- ------- --- ----------- ----------
TDE_TARGET_TBS 8 AES192 YES 1 NORMAL
SQL>
Oracle has replaced the TDE_TARGET_TBS.dbf file with TDE_TARGET_TBS_encrypted.dbf file. The new file does NOT have any plain-text values "CONTENT"
For subsequent Tablespaces, Steps 1 to 4 would not be required.
2 comments:
Maybe it is important to further highlight differences and steps required in multi-tenant arch.
Yogi,
See the documentation on WALLET_ROOT : https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/WALLET_ROOT.html#GUID-37347728-3D24-444A-A9ED-5B981C4EA2D3
On the other hand,TDE_CONFIGURATION is modifiable at the PDB level https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/TDE_CONFIGURATION.html#GUID-285A9BCE-22AE-4DE4-A76E-1319B7BB91BC
So, it shouldn't be difficult or very different to setup TDE in a Multi-Tenant Environment.
Post a Comment