Encrypt data in the Oracle 18c data dictionary

 Do you need to encrypt sensitive credential data? Here’s how you can do so in the Oracle 18c data dictionary.

Oracle Database has two main tables where credential data is stored: SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL. SYS.LINK$ is used to store sensitive credential data for database links and SYS.SCHEDULER$_CREDENTIAL is used to store sensitive credential data for Oracle Scheduler events.  By default, all the data stored in these tables is obfuscated; however, as a best security practice recommended by Oracle, the data should also be encrypted. The functionality to encrypt the data in these tables was introduced in Oracle 18c and is performed using very simple commands.

TIP for Licensing: This feature uses Transparent Data Encryption (TDE); however, the license of Advanced Security Option is not required.

How does it work?

The sensitive credential data is de-obfuscated and then it is encrypted. The sensitive credential data that is already stored is not encrypted at the moment; only future password changes made after this feature is enabled are encrypted. The encryption used by this feature is AES256 (Advanced Encryption Standard) algorithm.

How to implement it?

The following steps are needed to implement this feature:

• Create TDE Wallet
• Open the Wallet
• Check if sensitive credential data is encrypted
• Set ‘compatible’ parameter to 18.1.0.0 at a minumum
• Encrypt sensitive credential data

In this example the following two environments are used:

• Oracle Database 12.2.0.1 with 1 Database Container (CDB1) and 1 Pluggable Database (PDB1)
• Oracle Database 18.3.0.0 with 1 Database Container (CDB1) and 1 Pluggable Database (PDBORCL)

In the 18c environment, a database link will be created towards the 12c environment:

SQL> show pdbs 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           MOUNTED

SQL> alter session set container=pdb1;

Session altered.

SQL> show con_name

CON_NAME

——————————

PDB1

SQL> create database link dblink1 connect to dgomez identified by dgomez using '192.168.1.24:1521/pdborcl';

Database link created.

SQL>

Checking whether encrypting sensitive credential data is enabled:

The view DICTIONARY_CREDENTIALS_ENCRYPT can say whether the sensitive credential data for SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL are encrypted:

SQL> select enforcement from dictionary_credentials_encrypt;

ENFORCEM

——–

DISABLED

This means, any intruder could de-obfuscated the database link password. Let’s see if the data is visible at binary level. First, let’s check the physical address of the row:

In 18c-> CDB1->PDB1:                   

SQL>  select dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid, 'SYS','LINK$') file_num,

dbms_rowid.rowid_block_number(rowid) block_num,

dbms_rowid.rowid_row_number(rowid) row_num,

owner#,

name,

userid, passwordx

from SYS.LINK$;  2    3    4    5    6    7

  FILE_NUM  BLOCK_NUM    ROW_NUM     OWNER# NAME       USERID     PASSWORDX

———- ———- ———- ———- ———- ———- ——————–

         9      16041          0          0 SYS_HUB

         9      16041          1          0 DBLINK1    DGOMEZ     07D5AF755727D71F330A

                                                                  23F9CE3E5199677E8C79

                                                                  7E51B3C3E4792F65B433

                                                                  D0384E1FD9121D4D95FD

                                                                  126D847568DCE7EFAFF9

                                                                  05B67221D1575B526966

                                                                  0CA8D1CA3E820DB71AE7

                                                                  3B2CBF1678CCBF8E1C85

                                                                  307983CC429BBC5A2B21

                                                                  A087484B5467094190CB

                                                                  ED0864DA3AB1BC273830

                                                                  1E8AD1AA54B3A7A3D47C

                                                                  538DB65EFD9F3EE

The row is stored in the File 9 and Block 16041. Let’s identify the file with the “file_num”=9:

In 18c-> CDB1->PDB1:   

SQL> select file_id, file_name from dba_data_files;

   FILE_ID FILE_NAME

———- —————————————————————————

         9 +DATA/CDB1/786275596EF87CF9E0531F64A8C02AD9/DATAFILE/system.290.989690867

        10 +DATA/CDB1/786275596EF87CF9E0531F64A8C02AD9/DATAFILE/sysaux.291.989690869

        11 +DATA/CDB1/786275596EF87CF9E0531F64A8C02AD9/DATAFILE/undotbs1.289.989690867

        12 +DATA/CDB1/786275596EF87CF9E0531F64A8C02AD9/DATAFILE/users.293.989690927

Let’s copy the datafile to our filesystem:

-bash-4.2$ asmcmd

ASMCMD>  cp +DATA/CDB1/786275596EF87CF9E0531F64A8C02AD9/DATAFILE/system.290.989690867 /home/grid/system.dbf

copying +DATA/CDB1/786275596EF87CF9E0531F64A8C02AD9/DATAFILE/system.290.989690867 -> /home/grid/system.dbf

ASMCMD> exit

Since the Row is stored in the Block 16041, that block will be extracted so that the data can be displayed clearly:

[root@db18 ~]# dd if=/home/grid/system.dbf of=/home/grid/block.dbf ibs=8k skip=16041 count=1

1+0 records in

16+0 records out

8192 bytes (8.2 kB) copied, 0.000368307 s, 22.2 MB/s

[root@db18 ~]# strings /home/grid/block.dbf

DBLINK1

192.168.1.24:1521/pdborcl

DGOMEZ

W[Rif

HKTg    A

SYS_HUB

SEEDDATA

[root@db18 ~]#

As you see, there is sensitive credential data stored in the data dictionary.

Creating the TDE Wallet:

The TDE Wallet should exist already and must to be open because this feature uses it. If it doesn’t exist, it must be created beforehand to enable encryption in sensitive credential data; otherwise the following error will be received:

SQL> ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS;

ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS

*

ERROR at line 1:

ORA-28443: cannot access the TDE wallet 

The directory of the wallet has to be created:

[oracle@db18 ~]$ mkdir -p  /u01/app/oracle/cdb1/wallet

The file SQLNET.ORA has to be configured with the wallet directory:

[oracle@db18 ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

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

ENCRYPTION_WALLET_LOCATION=

(SOURCE=

(METHOD=FILE)

(METHOD_DATA=

(DIRECTORY=/u01/app/oracle/cdb1/wallet)))

[oracle@db18 ~]$

To create and open the TDE Wallet, the SYSKM can be used.

[oracle@db18 ~]$ sqlplus / as syskm

SQL*Plus: Release 18.0.0.0.0 – Production on Thu Nov 1 22:57:03 2018

Version 18.3.0.0.0

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

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

Version 18.3.0.0.0

SQL> administer key management create keystore '/u01/app/oracle/cdb1/wallet' identified by "MyPassword123";

keystore altered.

SQL> administer key management set keystore open identified by "MyPassword123" container=all;

keystore altered.

SQL> administer key management set key identified by "MyPassword123" with backup container=all; 

keystore altered.

Checking if the TDE Wallet is open:

SQL> select wrl_parameter, status from v$encryption_wallet

WRL_PARAMETER                  STATUS

—————————— ——————————

/u01/app/oracle/cdb1/wallet/   OPEN

                               CLOSED

                               CLOSED

Set the TDE Wallet for autologin:

SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/cdb1/wallet/' identified by "MyPassword123";

keystore altered.

SQL>

Check if ‘compatible’ database parameter is set to the proper value:

SQL> show parameters compatible

NAME                                 TYPE        VALUE

———————————— ———– ——————————

compatible                           string      18.1.0

Enabling Encrypting sensitive credential data in the data dictionary, this command must be executed with SYSKM:

SQL> ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS CONTAINER=ALL;

Database dictionary altered.

Checking if the feature is enabled:

SQL> select enforcement from dictionary_credentials_encrypt;

ENFORCEM

——–

ENABLED

Conclusion

Oracle Database provides several new features for security in every release. Oracle 18c is also incorporating features to encrypt sensitive credential data specially in the data dictionary, where some passwords and data related to database links are stored. With hybrid environments between on-premises and cloud, it is very important to encrypt data, particularly if data movement is performed frequently to and from the cloud.