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.
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
If someone has access on OS level, as the ORACLE_HOME owner (which, really, should be the only user. besides of root, who should be able to read oracle datafiles, from file-level permissions/umask perspective), why would they not just logon to the running database instance as sysdba (which is likely configured with an autologin wallet holding the TDE master encryption key, so that the DB is able to automatically run crash recovery, to reduce downtime), and run whatever select they please, on the encrypted data?
Futher, as the oracle documentation says, TDE caches unencrypted data in the buffer cache, for performance reasons – so if someone has OS level access, as either root, or the oracle instance process owner, what is stopping them from attaching to the respective shared memory holding the buffer cache, and read the encrypted data in unencrypted format from there?
Grepping strings from datafiles is not the only way how to get access to the data in the database if someone has privileged (root or process owner) OS level access ..
Yes, I agree with your points. Grepping strings was just one way.
OS level security on Oracle_Home owner users is of course super important.