Using an external table containing an encrypted column.

Oracle Database 12c  - external tables containing encrypted columns.

Once the TDE set up has been done for a database, it is possible to create a copy of a table containing sensitive data as an external data pump type file on the disk, having the sensitive data encrypted with a password.

The protected dump file could be copied and Data Pump-ed into a different database.
Having imported it, an external table could be created based on the dmp file, and it by executing a SELECT from that table, it is possible access the protected information by providing the password.

For example, suppose there is a database called NONCDB1 with a schema called HR, containing an employee table.

The idea is to export this table to container database named CDB1, which has its own cryptography set up as well, and to access the data from there.

This employee table has an SSN column which is deemed sensitive.

The plain data looks like this.

SQL> select * from employees;

    EMP_ID JOIN_DATE SSN

---------- --------- --------------------

         1 01-DEC-10 123-456-001

         2 15-JAN-13 123-456-002

         3 25-OCT-05 123-456-003

SQL>

The employee data can be downloaded as an external table to a .DMP file and transported with its sensitive data encrypted. The section below describes the steps to do it.

Stage 1 - Export the table with sensitive columns encrypted with password:

1.      First, create a directory object pointing to the location where the external table data should reside. The HR user should have read/write access to the new directory object. For example…

SQL> conn / as sysdba

Connected.

SQL> create directory dpdir as '/u01/app/oracle/oradata/tmp';

Directory created.

SQL> GRANT read, write on directory dpdir to hr;

Grant succeeded.

2.      Then, using a CREATE TABLE AS SELECT, an external table can be created as shown, with content of sensitive column ssn encrypted with a password.

In the screenshot, the column ssn has been password-encrypted, using the cryptographic keys from the keystore which had been previously set up for this database.

The .DMP file written out to disk is shown below, in the location pointed to by the dpdir directory object (emp_extern.dat) :

Note that the ssn strings can be printed out from the users tablespace datafile which stores the emp heap table, where it is stored in plain but not from the encrypted .DMP file.

Stage 2 – Transport it, and access it in a different database

1. First, create a directory object to point to the .dat file location on the target database.
2. Grant read/write permissions to the directory to the schema which will own the external table which will be created.
3. Copy the .dat file to the destination server.

4. Make sure that TDE is set up and enabled in the target container.

5. Next, create the external table on the destination database. In the screenshot below, the encrypted column’s syntax contains the decryption password.

6. Review the newly created table’s contents.

The column is transparently decrypted and returned.

Conclusion

Once the transparent data encryption has been set up, the external table helps to transport sensitive data to a different location securely. However encryption should be distinguished from access control. Encrypting a column, for example, does not prevent a malicious user with the select/insert/update/delete privilege on the table from corrupting, copying or deleting it.