Select Page

Quick & Easy Setup of Transparent Data Encryption

Chad Cleveland | | September 27, 2018

Transparent data encryption (TDE) allows you to encrypt the sensitive data within the database. It provides a secure method to encrypt your sensitive data at the file level. Access to that data is allowed with a special key and corresponding wallet. PCI DSS regulations, which apply to the financial industry, require a method of protection for at rest data. Here’s a quick lesson to configure TDE at the tablespace level with an Oracle Database.

All work completed below was done with an Oracle 12c Database called ‘DEV’

1) Configure Wallet Location in sqlnet.ora

The default wallet location is $ORACLE_BASE/admin/DB_SID/wallet. It’s better to have a separate encryption wallet.

$ cd $ORACLE_BASE/admin/dev

$ pwd

/u01/oracle/admin/dev

$ mkdir encryption_wallet

Add entry to $ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY=/u01/oracle/admin/dev1/encryption_wallet/)))

2) Create the Wallet

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “Sup3rS3cr3t!”;

System altered.

SQL>

You have now created the wallet.

SQL> !ls -latr /u01/oracle/admin/dev/encryption_wallet

total 12

drwxr-xr-x. 7 oracle oinstall 4096 Aug 1 14:32 ..

drwxr-xr-x. 2 oracle oinstall 4096 Aug 1 14:35 .

-rw-r–r–. 1    oracle oinstall 2848 Aug 1 14:35 ewallet.p12

 

3) Open the Wallet

You must reopen the wallet after you restart the database to allow access to the encrypted data.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “Sup3rS3cr3t!”;

4) Let’s Build a Playground and Show Examples of TDE

SQL> set linesize 185 pages 600

SQL> column tablespace_name format a40

SQL> column file_name format a70

SQL> select tablespace_name, file_name from dba_data_files;

 

TABLESPACE_NAME           FILE_NAME

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

SYSTEM                      /oradata/dev1/system01.dbf

SYSAUX                      /oradata/dev1/sysaux01.dbf

UNDOTBS1                           /oradata/dev1/undotbs01.dbf

USERS                         /oradata/dev1/users01.dbf

SENSITIVE_DATA          /oradata/dev1/SENSTIVE_DATA01.dbf

SENSITIVE_INDEX          /oradata/dev1/SENSITIVE_INDEX01.dbf

SENSITIVE_LOB                   /oradata/dev1/SENSITIVE_LOB01.dbf

DBA_TEST                            /oradata/dev1/dba_test.dbf

AUDIT_AUX                 /oradata/dev1/audit_aux01.dbf

 

–Create a non-encrypted Tablespace

CREATE TABLESPACE ccleveland_non_enc

DATAFILE ‘/oradata/dev1/ccleveland_non_enc.dbf’ SIZE 100M AUTOEXTEND ON NEXT 100M;

 

–Create an Encrypted Tablespace

CREATE TABLESPACE ccleveland_encrypted

DATAFILE ‘/oradata/dev1/ccleveland_encrypted.dbf’ SIZE 100M

AUTOEXTEND ON NEXT 100M

ENCRYPTION USING ‘AES256’

DEFAULT STORAGE(ENCRYPT);

 

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME           ENCRYTED

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

SYSTEM                               NO

SYSAUX                               NO

UNDOTBS1                                    NO

TEMP                                    NO

USERS                                  NO

SENSITIVE_DATA                 NO

SENSITIVE_INDEX               NO

SENSITIVE_LOB                            NO

DBA_TEST                                     NO

AUDIT_AUX                          NO

CCLEVELAND_NON_ENC             NO

CCLEVELAND_ENCRYPTED         YES

5) Let’s Get Some Data

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

———- ———- ——— ———- ——— ———- 7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

 

create table ccleveland.emp_non_enc

tablespace ccleveland_non_enc as select * from scott.emp;

 

 

select * from ccleveland.emp_non_enc;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

 

6) Migrate the Data into the Encrypted Tablespace

 

SQL> alter table ccleveland.emp_non_enc move tablespace CCLEVELAND_ENCRYPTED;

Table altered.

7) Rename the Table and Validate the Data

SQL> alter table ccleveland.emp_non_enc rename to ccleveland.emp_encrypted;

SQL> select * from ccleveland.emp_encrypted;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

 

8) Grant Permissions on the Table to Allow Your Boss to Read It

SQL> grant select on ccleveland.emp_encrypted to scott;

Grant succeeded.

SQL> alter user scott identified by “n0Tr0ubl3” account unlock;

User altered.

SQL> connect scott/n0Tr0ubl3

SQL> show user;

USER is “SCOTT”

SQL> select * from ccleveland.emp_encrypted;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

 

9) Example of Forgetting to Open the Wallet with Your Special Key

 

connect scott

SQL> select * from ccleveland.emp_encrypted;

select * from ccleveland.emp_encrypted

*

ERROR at line 1:

ORA-28365: wallet is not open

Remember that your wallet must be open to access your data. If not, the database will raise an error.

 

10) TDE can be even more fine tuned. Let’s encrypt a single column.

create table ccleveland.emp as select * from scott.emp;

SQL> alter table ccleveland.emp modify (sal ENCRYPT);

Table altered.

SQL>

SQL> column owner format a20

SQL> column table_name format a30

SQL> column column_name format a30

SQL> set linesize 185 pages 600

 

 

SQL> select owner, table_name, column_name from dba_encrypted_columns;

 

OWNER TABLE_NAME COLUMN_NAME

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

CCLEVELAND EMP SAL

SQL>

Transparent Data Encryption is a nifty tool to have in your DBA toolbag. This exercise showed a fairly straightforward way to implement encryption in the database and to satisfy security/audit regulations. Once you’ve implemented TDE, just be careful not to lose your key. You won’t be able to access your encrypted data without it.    

Read This Next

Using Oracle Enterprise Manager and Custom Templates for Alert Optimization

Discover how to further optimize your Oracle database environment by downloading our white paper.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017
oracle , aws

Oracle Cloud vs Amazon Cloud – Which is right for you?

Choosing the right cloud computing vendor for your database needs is difficult. This blog post takes you through the pros and cons of Oracle vs Amazon Cloud.

Megan Elphingstone | July 20, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS