REM: Transparent Data Encryption (TDE) in Oracle Database 12cR2 & 19c
REM: This document explains how to enable TDE in Oracle 12c/19c Database
REM: This is a sample document, and cannot be used in production without proper verification
REM: For any clarification please contact hayathk@hotamil.com
REM: Dated: 19-Apr-2022
REM: Version#1
>>Step#0
Create directory
---------------
Create Directory:
D:\db_home\admin\orcl\wallet
>>Step#1
Modify keystore path in sqlnet.ora of DB instance
-----------------
File: sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=D:\db_home\admin\orcl\wallet)))
>>Step#2
---------------------
Create a keystore :
sqlplus / as sysdba
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'D:\db_home\admin\orcl\wallet' IDENTIFIED BY wallet1234;
keystore altered.
ls -l ewallet.p12
--------------------
Open the keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet1234;
keystore altered.
To close keystore:
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY wallet1234; --if CDB db:: CONTAINER=ALL;
>>Step#4
---------------------
Verify if key is activated using v$encryption_keys
SQL> SET LINESIZE 100
SQL> SELECT con_id, key_id FROM v$encryption_keys;
no rows selected
---------------------
Set the encryption key :
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY wallet1234 WITH BACKUP;
keystore altered.
--two files now in wallet location
ls ewallet.p12 ewallet_2022041907033592.p12
SQL> SET LINESIZE 100
SQL> SELECT con_id, key_id FROM v$encryption_keys;
CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
0 Adm2nZKRqk8vvx7aRKRPq3cAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL>
>>Step#5
---------------------------
enable encryption at column level
SQL> CREATE TABLE emp (ename VARCHAR2(50),emp_no NUMBER(5) ,dname VARCHAR2(50) ENCRYPT);
CREATE TABLE emp (ename VARCHAR2(50),emp_no NUMBER(5) ,dname VARCHAR2(50) ENCRYPT)
*
ERROR at line 1:
ORA-28336: cannot encrypt SYS owned objects
SQL> create user hayat identified by oracle123;
SQL> grant connect, resource to hayat;
SQL> alter user hayat quota unlimited on users;
SQL> conn hayat/oracle123
Connected.
SQL> CREATE TABLE emp (ename VARCHAR2(50),emp_no NUMBER(5) ,dname VARCHAR2(50) ENCRYPT);
SQL> insert into emp values ('Hayat',101,'Information Security');
1 row created.
select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='EMP';
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG
----------------------------- ------------------------------------
HAYAT EMP DNAME AES 192 bits key
Hint: AES128, AES192, AES256, 3DES168, ARIA128, ARIA192, ARIA256, SEED128, and GOST256. The default value is AES128.
>>Step#6
Shutdown DB, and without an opening the wallet, see what happens:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5100272704 bytes
Fixed Size 9038912 bytes
Variable Size 922746880 bytes
Database Buffers 4160749568 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.
SQL> select * from hayat.emp;
select * from hayat.emp
*
ERROR at line 1:
ORA-28365: wallet is not open
Now open the wallet:
------------------
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet1234;
keystore altered.
SQL> select count(*) from hayat.emp;
COUNT(*)
----------
1
---------------------------- Auto Wallet Open during DB restart option :::
SQL> select wallet_type, wrl_type, status, keystore_mode from v$encryption_wallet;
WALLET_TYPE WRL_TYPE STATUS KEYSTORE
-------------------- -------------------- ------------------------------ --------
PASSWORD FILE OPEN NONE
--extra switch AUTO_LOGIN
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'D:\db_home\admin\orcl\wallet' IDENTIFIED BY wallet1234;
keystore altered.
---- extra sso file
cd D:\db_home\admin\orcl\wallet
cwallet.sso
ewallet.p12
ewallet_2022041907033592.p12
-------Now wallet keystore manual open is not required
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5100272704 bytes
Fixed Size 9038912 bytes
Variable Size 922746880 bytes
Database Buffers 4160749568 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.
SQL> select count(*) from hayat.emp;
COUNT(*)
----------
1
--value changed for wallet_type
SQL> select wallet_type, wrl_type, status, keystore_mode from v$encryption_wallet;
WALLET_TYPE WRL_TYPE STATUS KEYSTORE
-------------------- -------------------- ------------------------------ --------
AUTOLOGIN FILE OPEN NONE
>>Step#7
Table space Level Encryption:
SQL> CREATE TABLESPACE my_encrypted_ts DATAFILE 'D:\DB_HOME\ORADATA\ORCL\encrypt_ts.dbf' SIZE 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
alter user hayat quota unlimited on my_encrypted_ts;
SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name=upper ('my_encrypted_ts');
TABLESPACE_NAME ENC
------------------------------ ---
MY_ENCRYPTED_TS YES
CONN HAYAT/oracle123
------------------------
SQL> CREATE TABLE emp_ts_enc (ename VARCHAR2(50),emp_no NUMBER(5) ,dname VARCHAR2(50) ENCRYPT) tablespace my_encrypted_ts;
Table created.
SQL> insert into emp_ts_enc values ('Abdullah',201,'Database Security');
1 row created.
SQL> commit;
Commit complete.
----------Restart DB and verify that data exist in Encrypted TBS:
SQL> select count(*) from hayat.emp_ts_enc;
COUNT(*)
----------
1
Step#8
-----Verify if keystore is closed, and we can fetch data for non-encrypted tables:
Remove the auto-open wallet cwallet.sso physically:
mv cwallet.sso cwallet.sso.bkp
SQL> administer key management set keystore close;
keystore altered.
SQL> select ename from hayat.emp;
ENAME
--------------------------------------------------
Hayat
Abdullah
SQL> select ename, dname from hayat.emp;
select ename, dname from hayat.emp
*
ERROR at line 1:
ORA-28365: wallet is not open
-----the reason is that dname is encrypted column
Now copy back the sso and backup file to wallet location:::
SQL> administer key management set keystore open identified by wallet1234;
keystore altered.
SQL> select ename, dname from hayat.emp;
ENAME DNAME
-------------------------------------------------- --------------------------------------------------
Hayat Information Security
Abdullah Database Security
----------------------------------------------------------------------
Existing unencrypted Table space into encrypted tablespace (12cR2 and onward):
----------------------------------------------------------------------
alter tablespace dummy_data encryption online using 'aes192' encrypt
file_name_convert=(
'/u01/dummydata/test_data01.dbf',
'/u01/dummydata/test_data01_enc.dbf'
);
alter tablespace dummy_data encryption online using 'aes192' encrypt
file_name_convert=(
'/u01/dummydata/dummy_data_01.dbf',
'/u01/dummydata/dummy_data_01_enc.dbf',
---next file name
'/u01/dummydata/dummy_data_02.dbf',
'/u01/dummydata/dummy_data_02_enc.dbf'
);
-----------------------
Offline decryption
This feature is supported from Oracle TDE 12.2.0.1 onwards.
-----------------------
1.Take the tablespace offline.
SQL> alter tablespace offline_test offline normal;
2.Decrypt the tablespace using the ALTER TABLESPACE command.
alter tablespace offline_test encryption offline decrypt;
---
--ALTER DATABASE DATAFILE ‘<file name>’ DECRYPT;
3.Bring the tablespace back online.
SQL>alter tablespace offline_test online;
-----------------------
Online decryption
TDE 12.2 : Encryption and Decryption of Existing Tablespaces (Doc ID 2255611.1)
-----------------------
1.Ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0.
2.Decrypt the tablespace using the alter tablespace command.
alter tablespace online_test encryption online decrypt file_name_convert = ('test_enc.dbf', 'test.dbf');
--datafile status checking 11.2.0.4
Doc ID 2458267.1
1. To check tablespaces which are in 'Encrypting' status:
SQL>SELECT t.name, e.encryptedts, e.status FROM v$tablespace t, v$encrypted_tablespaces e WHERE t.ts#=e.ts# ;
2.To check the encryption status of data files in a tablespace:
SQL>select tablespace_name,name,encrypted from v$datafile_header where tablespace_name like '%<TABLESPACE NAME from above query>%';
SQL>select count(*) from v$datafile_header where tablespace_name like '%<TABLESPACE NAME from above query>%' and encrypted='NO';
Useful Views:
SELECT * FROM V$ENCRYPTION_WALLET;
SELECT * FROM V$ENCRYPTION_KEYS; SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET; SELECT KEY_ID,KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS; SELECT KEY_ID FROM V$ENCRYPTION_KEYS; SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS; SELECT WRL_PARAMETER FROM V$ENCRYPTION_WALLET; SELECT STATUS FROM V$ENCRYPTION_WALLET; SELECT * FROM V$ENCRYPTED_TABLESPACES; SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES; SELECT * FROM DBA_ENCRYPTED_COLUMNS;
Dynamic Script for 12.2 Tablespace datafile encryption:
select 'alter tablespace ' || ts.name || ' encryption online using ''AES192'' encrypt
file_name_convert =
('||chr(39)||df.name||chr(39)||','||chr(39)||REPLACE(df.name, '.dbf', '_enc.dbf')||chr(39)||');' command
from v$tablespace ts, v$datafile df where ts.ts#=df.ts# and
(ts.name in ('USERS') and ts.name not in
(select value from gv$parameter where name='undo_tablespace'))
Change below hidden parameter to cater ORA-46663: Master Keys Not Created 12R1 sql> select ksppinm from sys.x$ksppi where ksppinm like '_db_discard%'; sql> alter system set "_db_discard_lost_masterkey"=true scope=memory;
References and Credit goes to below websites for valuable information on this topic:
https://oracledbwr.com/oracle12c-transparent-data-encryption-tde-tips/
https://oracle-base.com/articles/12c/multitenant-transparent-data-encryption-tde-12cr1
http://oracle-help.com
https://thalesdocs.com/ctp/ig/oracle/tde/tde-cryptographic_operation/index.html
https://docs.oracle.com/en/database/oracle/oracle-database/21/asoag/encryption-conversions-tablespaces-and-databases1.html#GUID-1CA947A5-164A-4AA9-AD75-F7D9D4F6FFF8
TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login wallet is present (Doc ID 1944507.1)
ORA-46632: password-based keystore does not exist' but the file ewallet.p12 exists (Doc ID 2463440.1)