Configuring Transparent Data Encryption In Oracle 19c Database

 

Description:- 

  • Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces.
  • Transparent Data Encryption (TDE) ensures that sensitive data is encrypted, meets compliance requirements, and provides functionality that streamlines encryption operations.
  • In a multitenant environment, you can configure keystores for either the entire container database (CDB) or for individual pluggable databases (PDBs).
  • Transparent Data Encryption can be applied to individual columns or entire tablespaces.
  • Wallet configuration in SQLNET.ORA therefore no longer needed
  • Oracle recommends using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter after setting the WALLET_ROOT.
  • New with 19cClose of keystore even if SYSTEM, SYSAUX, TEMP and UNDO is encrypted
    Easier online conversion for non OMF datafiles

Note:-

In previous releases, the SQLNET.ENCRYPTION_WALLET_LOCATION parameter was used to define the keystore directory location. This parameter has been deprecated. Oracle recommends that you use the WALLET_ROOT static initialization parameter and TDE_CONFIGURATION dynamic initialization parameter instead.

In earlier releases, This is specified in the sqlnet.ora file like this :

[oracle@Prod22 ~]$ cd $ORACLE_HOME/network/admin
[oracle@Prod22 admin]$
[oracle@Prod22 admin]$ cat sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet)))

TDE configuration in oracle 19c Database

Step:-1 Configure the Wallet Root

[oracle@Prod22 ~]$ . .19c.env
[oracle@Prod22 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jun 21 18:03:22 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 2936008960 bytes
Fixed Size 8900864 bytes
Variable Size 452984832 bytes
Database Buffers 2466250752 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> select banner from v$version;
BANNER
——————————————————————————–
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

SQL> show parameter tde_configuration
NAME TYPE VALUE
———————————— ———– ——————————
tde_configuration                string

SQL> show parameter wallet_root
NAME TYPE VALUE
———————————— ———– ——————————
wallet_root                     string

Now Going to change this parameter

Backup parameter file:-

SQL> create pfile=’${ORACLE_BASE}/admin/${ORACLE_SID}/pfile/${ORACLE_SID}-`date +%F`.ora’ from spfile;
File created.

[oracle@Prod22 dbs]$ cd /u02/app/oracle/admin/oradbwr/pfile/
[oracle@Prod22 pfile]$ ls -lrt
total 8
-rw-r—–. 1 oracle oinstall 2297 Jun 17 23:05 init.ora.5172021231259

SQL> alter system set WALLET_ROOT=”${ORACLE_BASE}/admin/${ORACLE_SID}/wallet” scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 2936008960 bytes
Fixed Size 8900864 bytes
Variable Size 452984832 bytes
Database Buffers 2466250752 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> alter system set TDE_CONFIGURATION=”KEYSTORE_CONFIGURATION=FILE”;
System altered.

SQL> show parameter tde_configuration
NAME TYPE VALUE
———————————— ———– ——————————
tde_configuration string KEYSTORE_CONFIGURATION=FILE

SQL> show parameter wallet_root
NAME TYPE VALUE
———————————— ———– ——————————
wallet_root string /u02/app/oracle/admin/oradbwr/
wallet

Step 2: Create the password protected key store

SQL> administer key management create keystore identified by oracledbwr;
keystore altered.

SQL> administer key management create LOCAL auto_login keystore from keystore ‘/u02/app/oracle/admin/oradbwr/wallet/tde/’ identified by oracledbwr;
keystore altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.11.0.0.0
[oracle@Prod22 tde]$ ls -lrt
total 8
-rw——-. 1 oracle oinstall 2555 Jun 21 19:02 ewallet.p12
-rw——-. 1 oracle oinstall 2600 Jun 21 19:02 cwallet.sso
[oracle@Prod22 tde]$ pwd
/u02/app/oracle/admin/oradbwr/wallet/tde

Check using the below query:-

select * from v$encryption_wallet;

OPEN_NO_MASTER_KEY -> Keystore is already not OPEN use the below command to open
administer key management set keystore open identified by oracledbwr;

Step:-3 Setup the Master Encryption Key

SQL> administer key management set key using tag ‘oracledbwr_Tablespace_TDE’ force keystore identified by oracledbwr with backup using ‘TDE_backup’;
keystore altered.

[oracle@Prod22 tde]$ ls -lrt
total 20
-rw——-. 1 oracle oinstall 2555 Jun 21 19:12 ewallet_2021062113423541_TDE_backup.p12
-rw——-. 1 oracle oinstall 4187 Jun 21 19:12 ewallet.p12
-rw——-. 1 oracle oinstall 4232 Jun 21 19:12 cwallet.sso

select * from v$encryption_wallet;

select key_id,tag,keystore_type,creation_time from v$encryption_keys;

Step:-4 Now we go for Testing

create tablespace tde_oracledbwr_tbs datafile ‘/u02/app/oracle/oradata/ORADBWR/tde_tbs1.dbf’ size 50M;  -> Without encryption create tablespace

SQL> create user hari identified by hari default tablespace tde_oracledbwr_tbs quota unlimited on tde_oracledbwr_tbs;
User created.

SQL> grant connect,resource to hari;
Grant succeeded.

[oracle@Prod22 ~]$ sqlplus hari/hari
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jun 21 19:30:53 2021
Version 19.11.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.11.0.0.0

SQL> show user
USER is “HARI”

SQL> create table test (snb number, real_exch varchar2(20));
Table created.

insert into test (snb, real_exch)
select 385000000 + level – 1, ‘GSMB’
from dual
connect by level <= 10;
10 rows created.

SQL> commit;
Commit complete.

Now use the OS ‘strings’ command to determine whether the string value inserted in the table is ‘visible’:

SQL> !strings /u02/app/oracle/oradata/ORADBWR/tde_tbs1.dbf | grep GSMB
GSMB,
GSMB,
GSMB,
GSMB,
GSMB,
GSMB,
GSMB,
GSMB,
GSMB,
GSMB

Step:-5  Online Encryption of Tablespace

From 19c onwords no need go for Offline Encryption.This method creates a new datafile with encrypted data

SQL> alter tablespace users encryption online encrypt;
Tablespace altered.

(OR)

Before:-

[oracle@Prod22 ORADBWR]$ ls -lrt
total 2721356
-rw-r—–. 1 oracle oinstall 209715712 Jun 21 18:41 redo02.log
-rw-r—–. 1 oracle oinstall 209715712 Jun 21 19:12 redo03.log
-rw-r—–. 1 oracle oinstall 52436992 Jun 21 20:40 tde_tbs1.dbf
-rw-r—–. 1 oracle oinstall 68165632 Jun 21 20:41 temp01.dbf
-rw-r—–. 1 oracle oinstall 1038098432 Jun 21 21:21 system01.dbf
-rw-r—–. 1 oracle oinstall 692068352 Jun 21 21:26 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 356524032 Jun 21 21:26 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 5251072 Jun 21 21:27 users01.dbf
-rw-r—–. 1 oracle oinstall 209715712 Jun 21 21:27 redo01.log
-rw-r—–. 1 oracle oinstall 10600448 Jun 21 21:27 control01.ctl

SQL> alter tablespace TDE_ORACLEDBWR_TBS encryption online using ‘AES192’
encrypt file_name_convert =(‘/u02/app/oracle/oradata/ORADBWR/tde_tbs1.dbf’,’/u02/app/oracle/oradata/ORADBWR/tde_tbs1_encrypted.dbf’);
Tablespace altered.

After:-

[oracle@Prod22 ORADBWR]$ ls -lrt
total 2721356
-rw-r—–. 1 oracle oinstall 209715712 Jun 21 18:41 redo02.log
-rw-r—–. 1 oracle oinstall 209715712 Jun 21 19:12 redo03.log
-rw-r—–. 1 oracle oinstall 68165632 Jun 21 20:41 temp01.dbf
-rw-r—–. 1 oracle oinstall 1038098432 Jun 21 21:21 system01.dbf
-rw-r—–. 1 oracle oinstall 692068352 Jun 21 21:26 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 356524032 Jun 21 21:26 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 5251072 Jun 21 21:27 users01.dbf
-rw-r—–. 1 oracle oinstall 52436992 Jun 21 21:29 tde_tbs1_encrypted.dbf
-rw-r—–. 1 oracle oinstall 209715712 Jun 21 21:29 redo01.log
-rw-r—–. 1 oracle oinstall 10600448 Jun 21 21:29 control01.ctl

NOW Testing the data!!!!

Now use the OS ‘strings’ command to determine whether the string value inserted in the table is ‘visible’:

 

Connect with me:-

Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/oracledbwr

oracle 19coracle 19c databaseoracle EncryptionTDETransparent Data Encryption

You may also like

In 19c database

Apply Patching on Database and OJVM Patch 32578972: COMBO OF OJVM RU COMPONENT 19.11.0.0.210420 + DB RU 19.11.0.0.210420

View Post

Next Post

Oracle Database Security Assessment Tool-Version…

In 19c database

Oracle Database Security Assessment Tool-Version 2.2.2

View Post

Get updated

Share via
Facebook
Twitter
LinkedIn
Mix
Email
Print
Copy Link
Powered by Social Snap
Copy link
CopyCopied
Powered by Social Snap