Skip to content

Transparent Data Encryption (TDE) – Oracle Database Cloud

Transparent Data Encryption (TDE) has been a standard feature of Oracle database for quite a long time. Since database in cloud is increasingly becoming popular so this feature has taken a great importance keeping in mind that primary concerns that administrators have in keeping data in cloud (remote) servers is security.

Oracle Advanced Security Transparent Data Encryption (TDE) stops would-be attackers from bypassing the database and reading sensitive information from storage by enforcing data-at-rest encryption in the database layer. In this post we are going to demonstrate how the Transparent Data Encryption ensures that the data stored in the Oracle Database is protected and cannot be accessed from malicious users that have gained access at the OS level.

We already have 12c database service created in the Oracle Cloud for this example. Our goal is to use Transparent Data Encryption (TDE) to protect sensitive data by moving a table from an unprotected tablespace to a new tablespace encrypted with the TDE features of the Oracle Database.

 

STEP 1: CONNECT TO ORACLE DATABASE CLOUD SERVER

Since we have windows client so we will connect through putty

 

STEP 2:  CONNECT TO YOUR PLUGGABLE DATABASE

Now open a session to the PDB using sqlplus. It can be done directly using the service for pdb1 or going first into the CDB and then changing the session as shown below.

[oracle@brijesh ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 31 17:54:12 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container = pdb1;

Session altered.

 

 

STEP 3: CREATE SAMPLE UNENCRYPTED TABLE

CREATE TABLE SCOTT.ACCT (
ACCTNO NUMBER(2,0),
ANAME VARCHAR2(14),
CITY VARCHAR2(13))
TABLESPACE TEST;

insert into SCOTT.ACCT (ACCTNO,ANAME,CITY) values (11,'ACCOUNT1','CHICAGO');
insert into SCOTT.ACCT (ACCTNO,ANAME,CITY) values (22,'ACCOUNT2','INDIANAPOLIS');
insert into SCOTT.ACCT (ACCTNO,ANAME,CITY) values (33,'ACCOUNT3','CALIFORNIA');
insert into SCOTT.ACCT (ACCTNO,ANAME,CITY) values (44,'ACCOUNT4','BOSTON');
commit;

-- also verify the tablespace status

COL TABLE_NAME FORMAT A25
COL TABLESPACE_NAME FORMAT A20
select TABLESPACE_NAME, STATUS, ENCRYPTED, CONTENTS from dba_tablespaces;SQL> SQL>

TABLESPACE_NAME       STATUS  ENC    CONTENTS
-------------------- --------- --- ---------------------
SYSTEM               ONLINE    NO    PERMANENT
SYSAUX               ONLINE    NO    PERMANENT
UNDOTBS1             ONLINE    NO    UNDO
TEMP                 ONLINE    NO    TEMPORARY
USERS                ONLINE    YES   PERMANENT
TEST                 ONLINE    NO    PERMANENT

As can be seen above “TEST” tablespace is unencrypted tablespace with encryption as “No”.

 

STEP 4: QUERY SENSITIVE DATA FROM DATABASE AND OS LEVEL DATA FILE

From Database SQL Query:

SQL> set lines 132
SQL> select * from SCOTT.ACCT;

ACCTNO          ANAME          CITY
---------- -------------- -------------
 11          ACCOUNT1        CHICAGO
 22          ACCOUNT2        INDIANAPOLIS
 33          ACCOUNT3        CALIFORNIA
 44          ACCOUNT4        BOSTON

 

From OS level datafile

$ strings /u02/app/oracle/oradata/brijesh/PDB1/TEST1.DBF
}|{z
6BRIJESH
TEST

--
-

TYPE
WORKSPACE_IDENTIFIER
 POOL_NAME
J&--------------------------------------
ACCOUNT4
BOSTON,
ACCOUNT3
CALIFORNIA,
ACCOUNT2
INDIANAPOLIS,
ACCOUNT1
CHICAGO

As you can see the data is visible by running simple strings OS command on the datafile. This essentially means that anybody having access to OS can read data without even connecting to database.

 

STEP 5: CREATE ENCRYPTED TABLESPACE USING TDE

In this step, we are going to create a new tablespace (TEST_ENCRYTPTED) encrypted with AES192

SQL> CREATE TABLESPACE "TEST_ENCRYPTED" DATAFILE '/u02/app/oracle/oradata/brijesh/PDB1/TEST_ENCRYPTED1.DBF'
     SIZE 10M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED, CONTENTS from dba_tablespaces;

TABLESPACE_NAME       STATUS   ENC    CONTENTS
-------------------- --------- --- ---------------------
SYSTEM               ONLINE   NO      PERMANENT
SYSAUX               ONLINE   NO      PERMANENT
UNDOTBS1             ONLINE   NO      UNDO
TEMP                 ONLINE   NO      TEMPORARY
USERS                ONLINE   YES     PERMANENT
TEST                 ONLINE   NO      PERMANENT
TEST_ENCRYPTED       ONLINE   YES     PERMANENT

7 rows selected.

As you can see, the new TABLESPACE named TEST_ENCRYPTED is encrypted, online and permanent. Note that we didn’t mention explicitly that we meant to encrypt the tablepspace but Oracle by default created this tablespace as encrypted tablespace.

It is important to note that the power of the Oracle Cloud allows you to have the information encrypted and protected as soon as you start to use the Oracle Database Cloud Service databases.

All new tablespaces you created in a Database as a Service database are encrypted by default. The tablespaces that were created when you created the service instance are not encrypted though. In an Oracle Database 12c database, this includes the tablespaces in the root (CDB$ROOT), the seed (PDB$SEED), and the PDB that is created when the database is created.

 

Controlling Default Tablespace Encryption

The ENCRYPT_NEW_TABLESPACES initialization parameter controls default encryption of new tablespaces.
In Database as a Service databases, this parameter is set to CLOUD_ONLY.

Values of this parameter are as follows:

ALWAYS Any tablespace created will be transparently encrypted with the AES128 algorithm unless
       a different algorithm is specified on the ENCRYPTION clause.

CLOUD_ONLY Tablespaces created in a Database Cloud Service database will be transparently encrypted
           with the AES128 algorithm unless a different algorithm is specified on the ENCRYPTION clause. 
           For non-Database Cloud Service databases, tablespaces will only be encrypted if the ENCRYPTION 
           clause is specified. This is the default value. 

DDL  Tablespaces are not transparently encrypted and are only encrypted if the ENCRYPTION clause is specified.

 

STEP 6: MOVE UNENCRYPTED TABLE TO ENCRYPTED TABLESPACE

In this step we will move the SCOTT.ACCT table to our newly created encrypted tablespace “TEST_ENCRYPTED”

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME ='ACCT';

TABLE_NAME TABLESPACE_NAME
------------------------- --------------------
ACCT TEST

SQL> ALTER TABLE "SCOTT"."ACCT" MOVE TABLESPACE "TEST_ENCRYPTED";

Table altered.

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME ='ACCT';

TABLE_NAME TABLESPACE_NAME
------------------------- --------------------
ACCT TEST_ENCRYPTED

Table has now been move to the newly created encrypted tablespace

 

STEP 7: QUERY SENSITIVE DATA FROM DATABASE AND OS LEVEL DATA FILE

From Database SQL Query

set lines 132
select * from SCOTT.ACCT;

ACCTNO         ANAME        CITY
---------- -------------- -------------
 11            ACCOUNT1     CHICAGO
 22            ACCOUNT2     INDIANAPOLIS
 33            ACCOUNT3     CALIFORNIA
 44            ACCOUNT4     BOSTON

 

From OS level data file

[oracle@brijesh ~]$ strings /u02/app/oracle/oradata/brijesh/PDB1/TEST_ENCRYPTED1.DBF |grep -i boston
[oracle@brijesh ~]$ <no results received>

 

As you can see after moving the table to the newly created encrypted tablespace, the data can not be seen by OS level commands without connecting to database.

TDE although is not a new feature of Oracle database cloud but its importance has increased in Oracle database cloud and should be implemented.

[Post Views: 95]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *

error: Alert: Content is protected !!