Quick & Easy Setup of Transparent Data Encryption

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.