back

next

 

 

 

 

Break Down Transparent Data Encryption 12c

 

 

Nowadays keeping the data safe is not an option for a DBA. It is a must and there are various regulation standards that require for a company to keep all the sensitive data secure. In security perspective, tables containing sensitive data requires special attention.  These are such as : Credit card numbers, Social security numbers, customer addresses, phone number etc.

 

For security, there are various tools in Oracle's toolbox. In this article, I will show Transparent Data Encryption feature. These are the topic I will cover:

  • How to setup TDE with software keystore
  • Check if the encryption is working  properly
  • Try different ways to Break Down TDE
  • Extend Break Down tests on the dataguard

 

 

What is it really ?

 

We can secure the data, using the transparent Data Encryption with minimum overhead. Security provided by the TDE provides the security over the data written on the datafiles.  If your datafiles are compromised, data can be retrieved using special tools. When TDE is applied it will be impossible for someone to hack into the secret data on the datafile. Without TDE, data is stored almost clear text and can be retrieved.

 

If the users are authenticated, they can access the data after establishing a connect using any client tool. Data they retrieve from TDE, will be transparently decrypted for their use on the fly. When they update the data on TDE, the data is written encrypted on the fly without any user intervention.

 

 

Oracle uses Encryption keys. Data is encrypted using those keys. There is a two-tiered key encryption architecture. TDE table key is used for encrypting and decrypting the sensitive data on tables (figure-1). TDE tablespace encryption key is used for encrypting and decrypting the sensitive data on tablespaces (figure 2). These keys are stored inside the database in encrypted form. Oracle uses the TDE master encryption key to encrypt or decrypt these keys so they can be kept inside database in encrypted form. This is the fundamentals of the two-tiered, key-based architecture.

 

TDE master encryption key is not stored inside database, it is an external module. Master key can be software or hardware based. In this article I will use software based master key.

TDE can be used on individual columns of a table or on a tablespace. All the objects in the TDE tablespace is automatically encrypted. In this article,  I will use TDE tablespace encryption.

 

TDE Table Encryption - Figure  1

 

 

TDE Tablespace Encryption- Figure  2

 

 

Setup TDE

 

TDE master encryption key is located outside the database.  In Oracle, the master key location can be defined as a keystore file. Keystore are created with a password and can be opened by that password. This password is a way to manage the master key. Without the password the master key cannot be open and other keys to encrypt/decrypt data cannot be used.

 

Setting the keystore location

Keystore file is located outside the database at the keystore location. The  keystore location can be defined for a database as follows : when there is an entry ENCRYPTION_WALLET_LOCATION in the sqlnet.ora file it will be used. If there is none then the  WALLET_LOCATION  will  be checked. If none of them is set then the default locations will be  checked $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet  or $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet. Be careful on the sqlne.ora that you use, it must be the one at the $ORACLE_HOME not $GRID_HOME.

 

Keystore  location can be on disk or ASM. We will use the disk for location.

 

cat $ORACLE_HOME/network/admin/sqlnet.ora

 

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=

   (METHOD=FILE)

    (METHOD_DATA=  (DIRECTORY=/u01/app/oracle/wallet_location)))

 

If multiple databases need keystore on the same host you can differentiate  the keystore locations  using $ORACLE_SID parameter in the sqlnet.ora.

/u01/app/oracle/wallet_locations/$ORACLE_SID

 

Create the Software Keystore

In 12c TDE master encryption key is stored in Keystores and the management is done using sqlplus. We will create a keystore and define a password to open / close / manage the master key.

Lets create the keystore. The password you choose to create the keystore is required for key management because of that it is very important. Do not lose it :)

 

In this example my password is mypa$$word

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/wallet_location' IDENTIFIED BY "mypa$$word";

keystore altered.

 

In 11g we were using the owm (oracle wallet manager) or orapki (command line utility). In 12c key management is simplified. It is managed using the "ADMINISTER KEY MANAGEMENT" commands inside sqlplus. Also in 12c role separation is provided for key management. Users need to have  ADMINISTER KEY MANAGEMENT or SYSKM privileges to be granted to manage the master key.

Now the keystore  is created and this file should be included in backup strategies otherwise the data encrypted with it cannot be retrieved. Let's check the keystore location and what it contains :

SQL> col WRL_PARAMETER format a40

SQL> col WRL_TYPE format a10

SQL> col STATUS format a10

SQL> select WRL_PARAMETER, WRL_TYPE, STATUS from V$ENCRYPTION_WALLET;

WRL_PARAMETER                          WRL_TYPE   STATUS

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

/u01/app/oracle/wallet_location/                 FILE      OPEN

 

[oracle@eserhost ~]$ ls -l /u01/app/oracle/wallet_location

-rw-r--r-- 1 oracle dba 2400 Feb 23 20:46 ewallet.p12

 

In 11g ewallet.p12 is called a wallet file. In 12c it is called a keystore file and it is used to keep the master keys.

After every start of the database, keystore should be manually open. Otherwise the master key cannot be accessed and the table keys or tablespace encryption keys cannot be decrypted. This will lead the inaccessibility of the encrypted data in the database. Basically, If the keystore is not open,  You cannot access the encrypted data.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypa$$word;

keystore altered.

 

Reopening the master key after every restart of the database is a tedious task. If you accidentally forgot to open it then you may face inaccessibility of data and eventually some downtime. To overcome this issue, you can create the AUTO_LOGIN ,single sign on, file. With auto login, The keystore will be open automatically after a restart.  This auto-login information is kept in a separate file. A file having "sso" extension, is created. It is called single sign on file.

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/wallet_location' IDENTIFIED BY "mypa$$word";

keystore altered.

 

[oracle@eserhost ~]$ ls -l /u01/app/oracle/wallet_location

-rw-r--r-- 1 oracle dba 2453 Feb 23 20:56 cwallet.sso

-rw-r--r-- 1 oracle dba 2400 Feb 23 20:46 ewallet.p12

 

Backups for Keystore

It is pretty good practice to use the "WITH BACKUP USING" syntax every time you make a change on the keys. This way you can have a previous copy in the keystore location. It is a pretty good feature and must be used as a best practice. Backup filenames utilizes user provided format combined with a timestamp.

SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'secure tablespace key' IDENTIFIED BY mypa$$word WITH BACKUP USING 'TBS' ;

keystore altered.

 

[oracle@eserhost ~]$ md5sum  *

399c15a23ada16d3ebbbf372fbb6981b   cwallet.sso

8398ecf91f8da774a03884da5cba58df    ewallet_2015022318595801_TBS.p12

e744a5b4c9b0e8786dfd703df9d33937  ewallet.p12

 

What privilege do we need to administer the keys.

You need to be ADMINISTER KEY MANAGEMENT or SYSKM privileges granted to operate on the keystore. Otherwise you will get permission errors.

SQL> create user eseruser identified by oracle;

User created.

SQL> grant connect, resource to eseruser;

Grant succeeded.

SQL> conn eseruser

Enter password:

Connected.

 

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypa$$word;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypa$$word

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

After the user is granted the SYSKM role and connects with this role and then able to administer the master key.

SQL> grant SYSKM to eseruser;

Grant succeeded.

SQL>  conn eseruser as syskm

Enter password:

Connected.

SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY mypa$$word;

keystore altered.

 

 

Testing the Encryption

 

Create encrypted tablespace and a secret table on that tablespace

Since we setup the master key, now we can encrypt some data and see the capabilities of the TDE tablespace encryption. To see which tablespaces are encrypted, we can use the V$ENCRYPTED_TABLESPACES view ;

Open the keystore if it is not already open ;

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypa$$word;

keystore altered.

 

Create the encrypted tablespace and create a table in that tablespace.

SQL> CREATE TABLESPACE encrypted_ts DATAFILE '+DATA_DB' SIZE 128K AUTOEXTEND ON NEXT 64K ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

 

SQL> create table eseruser.table_secret tablespace encrypted_ts as select * from dba_users ;

Table created.

 

Test encryption

Open the keystore if it is not already open ;

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypa$$word;

keystore altered.

 

Since the keystore is open, the encrypted table must be accessible. Let's see if it is.

SQL> col username format a20

SQL> col user_id format 9999

SQL> select username, user_id from eseruser.table_secret where username = 'ESERUSER';

USERNAME             USER_ID

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

ESERUSER                  85

 

Yes it seems like it is created perfectly. Now our goal is to see if the datafile really contains the data in encrypted format. First of all, we need to make sure everything on buffer is written on the datafile. We will flush buffer cache.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system checkpoint ;

System altered.

 

Let's assume somebody stole our datafile. Maybe they copied on the /tmp location then copied to a USB drive.

SQL> col name format a60

SQL> col file# format 999

SQL> select file#, name from v$datafile where ts# in (select ts# from v$tablespace where name = 'ENCRYPTED_TS');

FILE# NAME

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

    5 +DATA_DB/DBC/DATAFILE/encrypted_ts.289.872460007

RMAN> BACKUP AS COPY DATAFILE 5 FORMAT '/tmp/bck_encrypted_ts';

 

To see if there are any clear text information on the datafile they can use the linux's strings command. For the sake of simplicity, I put only a portion of the output. As you can see there are no meaningful secret clear text on the output.

[oracle@eserhost ~]$ strings /tmp/bck_encrypted_ts

}|{z

AFoIDBC

TAG20150223T220949

ENCRYPTED_TS

6M(3

[HCw

h%)<

%68<+

xuFqp

.Z      T

&6v!

bI^j-

o6-`

H9m/3

Lwep

 

OK it looks nice but how can we sure it is really working ? We approach scientifically and setup a control environment  similar to the last one where the only difference is a using a non-encrypted tablespace.

SQL> CREATE TABLESPACE nonencrypted_ts DATAFILE '+DATA_DB' SIZE 128K AUTOEXTEND ON NEXT 64K;

Tablespace created.

SQL> create table eseruser.table_secret_notsecure tablespace nonencrypted_ts as select * from dba_users ;

Table created.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system checkpoint ;

System altered.

 

SQL> col name format a60

SQL> col file# format 999

SQL>  select file#, name from v$datafile where ts# in (select ts# from v$tablespace where name = 'NONENCRYPTED_TS');

FILE# NAME

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

    6 +DATA_DB/DBC/DATAFILE/nonencrypted_ts.292.872460851

RMAN> BACKUP AS COPY DATAFILE 6 FORMAT '/tmp/bck_nonencrypted_ts';

 

As you can see the contents of the datafile can be read by a simple linux command. Someone having that datafile can have a clue of that sensitive data. If they further process, they may be able to recreate the secret table data.

[oracle@eserhost ~]$ strings /tmp/bck_nonencrypted_ts

}|{z

AFoIDBC

TAG20150223T221740

NONENCRYPTED_TS

OPEN

SYSTEM

TEMP

DEFAULT SYS_GROUP

10G 11G 12C

PASSWORD

SYSTEM

OPEN

SYSTEM

TEMP

DEFAULT SYS_GROUP

10G 11G 12C

PASSWORD

OUTLN

 

 

 advertisement

 

 

 

Break Down TDE

 

TDE is a great tool and can be implemented quickly. But as a good DBA it should be tested, before it is deployed on production. In this section, I will try to demonstrate the points that are important so that we do not risk the data we encrypt. I will simply try to break TDE down.

 

Lost single sign on file but keystore is available

What happens when we lost the single sign on file but the wallet file is still available. To simulate that, I will rename the file, while the database and the keystore are open.

[oracle@eserhost ~]$ cd /u01/app/oracle/wallet_location/

[oracle@eserhost wallet_location]$ ls

cwallet.sso    ewallet.p12

[oracle@eserhost wallet_location]$ mv cwallet.sso cwallet.sso.MV

[oracle@eserhost wallet_location]$ ls

cwallet.sso.MV    ewallet.p12

 

Since the wallet is open, we can still select the data .

SQL> col username format a20

SQL> col user_id format 9999

SQL> select username, user_id from eseruser.table_secret where username = 'ESERUSER';

USERNAME             USER_ID

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

ESERUSER                  85

 

There will be no problem until the next restart of the database. Let's see what happens after a restart.

SQL> startup force ;

ORACLE instance started.

.

.

Database opened.

SQL> col username format a20

SQL> col user_id format 9999

SQL> select username, user_id from eseruser.table_secret where username = 'ESERUSER';

select username, user_id from eseruser.table_secret where username = 'ESERUSER'

                                       *

ERROR at line 1:

ORA-28365: wallet is not open

 

 

 

SQL>  col WRL_PARAMETER format a40

SQL>  select WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

WRL_PARAMETER                              STATUS

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

/u01/app/oracle/wallet_location/         CLOSED

 

But don't worry the keystore file is still there, we can manually open the keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypa$$word;

keystore altered.

 

SQL>  select username, user_id from eseruser.table_secret where username = 'ESERUSER';

USERNAME               USER_ID

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

ESERUSER                   85

 

SQL> select WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

WRL_PARAMETER                              STATUS

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

/u01/app/oracle/wallet_location/           OPEN

 

Single sign on file is available but the keystore is lost

Let's see What happens when we lost the keystore file but the single sign on file is still available. Again we rename the file, while the database and the keystore are both open.

 [oracle@eserhost wallet_location]$ mv ewallet.p12 ewallet.p12.MV

[oracle@eserhost wallet_location]$ ls

cwallet.sso    ewallet.p12.MV

 

SQL> startup force ;

ORACLE instance started.

.

.

Database opened.

SQL>  col WRL_PARAMETER format a40

SQL> select WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

 

WRL_PARAMETER                      STATUS

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

/u01/app/oracle/wallet_location/   OPEN

 

As you can see the keystore is open. TDE survived even when the keystore is lost as long as you have the single sign on file available.

 

What if we do some management activity on the keystore, such as closing the keystore.

 

SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY mypa$$word;

 ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY mypa$$word

*

ERROR at line 1:

ORA-28417: password-based keystore is not open

 

If we try to operate on the keystore we will get an error. We cannot work on the keystore. We cannot manage the keys. We can clearly see that we need the keystore file for master key management.

 

Lost both of the files

What if we lost both files. It is a case similar to someone steal the keystore password but neither keystore file nor single sign on file. Let's try to be a little smart and recreate the keystore with the same password.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/wallet_location' IDENTIFIED BY "mypa$$word";

keystore altered.

 

Newly created keystore file will be  totally different than the previous one. I can show this checking the md5sum computations of the files.

[oracle@eserhost wallet_location]$ md5sum ewallet.p12*

884b7a44e0f639f2a3f11906981d005e  ewallet.p12

a755c8e9b45d9337ea43f1d38e91baec  ewallet.p12.MV

 

Remove the single sign on file also. The original keystore and sso files are the ones ending with "MV". ewallet.p12   is the one that we just created.

[oracle@eserhost wallet_location]$ ls

cwallet.sso.MV    ewallet.p12    ewallet.p12.MV

 

Let's see if we can open the keystore after restart.

 SQL> startup force ;

ORACLE instance started.

.

.

Database opened.

 

SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypa$$word;

keystore altered.

 

SQL> select username, user_id from eseruser.table_secret where username = 'ESERUSER';

select username, user_id from eseruser.table_secret where username = 'ESERUSER'

                                       *

ERROR at line 1:

ORA-28374: typed master key not found in wallet

 

Opps... You can open the new keystore but you cannot retrieve the data which is encrypted using the original master key. Unless we restore the original keystore file, it is impossible to access the encrypted data. Moral of the story : make sure to backup every file generated on the keystore location and also do not lose the keystore password.

 

 

Break Down TDE on Dataguard

 

I want to explain how TDE feature works with the dataguard environment. In many organizations dataguards are used not only for a disaster solution but also for reporting purposes as read only or read write. You can use a dataguard as active dataguard (read only) or as snapshot standby (read write).

In a reporting environment authenticated users can use tables that are protected using the TDE feature. For end users, To make everything to work on the dataguard we need to setup TDE on the dataguard part. In this section I will explain how to make the setup. Then try to find ways to break it down.

 

Setup TDE on Dataguard

Create the keystore directory  and define the directory in the sqlnet.ora file.

[oracle@remotehost dbs]$ mkdir /u01/app/oracle/wallet_location

[oracle@remotehost dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=

   (METHOD=FILE)

    (METHOD_DATA=

     (DIRECTORY=/u01/app/oracle/wallet_location)))

 

Copy or restore the production keystore on the dataguard keystore location. In my case, I will just remote copy the keystore to the Dataguard. eserhost is the primary DB server and remotehost is the dataguard server.

[oracle@remotehost dbs]$ scp eserhost:/u01/app/oracle/wallet_location/ewallet.p12  /u01/app/oracle/wallet_location/.

 

Now let's start the apply process. This part is from the alertlog of the dataguard. In the documentation it is indicated that the redo logs are also encrypted. For the apply process the decryption should occur using the master key. Since the keystore is not open, We must expect an error.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT  PARALLEL 16

Mon Feb 23 21:41:47 2015

Media Recovery Log +DATA_DB/DBGUARD/ARCHIVELOG/2015_02_23/thread_1_seq_69.297.872458399

Apply redo for TSE master key re-key failed: wallet error 28365

Errors with log +DATA_DB/DBGUARD/ARCHIVELOG/2015_02_23/thread_1_seq_69.297.872458399

MRP0: Background Media Recovery terminated with error 28365

Mon Feb 23 21:41:53 2015

Errors in file /u01/app/oracle/diag/rdbms/dbguard/DBGUARD/trace/DBGUARD_pr00_4811.trc:

ORA-28365: wallet is not open

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

 

Opss ... we got master key error. As we can see we need the keystore to be open. Now let's open the keystore using the same password we used on the production keystore.

SQL>  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN  IDENTIFIED BY mypa$$word;

keystore altered.

 

SQL> col WRL_PARAMETER format a40

SQL>  select WRL_PARAMETER, STATUS from V$ENCRYPTION_WALLET;

WRL_PARAMETER                                      STATUS

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

/u01/app/oracle/wallet_location/                   OPEN

 

After we manually open the keystore then we start the apply process and we can see that the encrypted datafile is added without any problem.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT  PARALLEL 16

Mon Feb 23 21:47:42 2015

Media Recovery Log +DATA_DB/DBGUARD/ARCHIVELOG/2015_02_23/thread_1_seq_69.297.872458399

Successfully added datafile 5 to media recovery

Datafile #5: '+DATA_DB/DBGUARD/DATAFILE/encrypted_ts.307.872459263'

 

If you we start the dataguard as active dataguard we can query the secret table as long as the keystore on the guard is open.

SQL> select username, user_id from eseruser.table_secret where username = 'ESERUSER'

USERNAME             USER_ID

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

ESERUSER             85

 

Lost the keystore file on the dataguard

Let's assume we lost the keystore on the dataguard part. If it happens we always have a chance to copy it from the primary site or from backup.

Let's rename the keystore to simulate the file lost.

[oracle@remotehost wallet_location]$ mv ewallet.p12 ewallet.p12.MV

SQL> startup force;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT  PARALLEL 16;

 

In my experiments, I saw that the apply process didn't interrupted as long as the encrypted tables didn't  have any DML . When there is any redo activity the apply process hangs and cannot apply the logs. After we insert a row to the encrypted table the apply process will give error.

Insert a row on the encrypted table at the primary database.

SQL>  insert into eseruser.table_secret select * from dba_users where rownum < 2;

1 row created.

SQL> commit;

Commit complete.

 

As seen in the alert log of the dataguard the apply is interrupted.

 

Primary database is in MAXIMUM PERFORMANCE mode

RFS[12]: Assigned to RFS process (PID:5256)

RFS[12]: Selected log 5 for thread 1 sequence 86 dbid 1232029249 branch 872246529

Mon Feb 23 21:59:11 2015

RFS[13]: Assigned to RFS process (PID:5258)

RFS[13]: Selected log 4 for thread 1 sequence 85 dbid 1232029249 branch 872246529

Mon Feb 23 21:59:11 2015

Expanded controlfile section 11 from 28 to 83 records

Requested to grow by 55 records; added 2 blocks of records

Mon Feb 23 21:59:11 2015

Archived Log entry 29 added for thread 1 sequence 85 ID 0x496f4441 dest 1:

Mon Feb 23 21:59:12 2015

Media Recovery Waiting for thread 1 sequence 86 (in transit)

Mon Feb 23 21:59:12 2015

Recovery of Online Redo Log: Thread 1 Group 5 Seq 86 Reading mem 0

  Mem# 0: +DATA_DB/DBGUARD/ONLINELOG/group_5.270.872256879

Mon Feb 23 22:00:38 2015

kcrf_decrypt_redokey: wallet is not opened..(err 28365)

MRP0: Background Media Recovery terminated with error 28365

Mon Feb 23 22:00:39 2015

Errors in file /u01/app/oracle/diag/rdbms/dbguard/DBGUARD/trace/DBGUARD_pr00_5165.trc:

ORA-28365: wallet is not open

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

 

In my experiments I saw that DDL activity on the encrypted table does not affect the apply process. The apply process continues without any problem.

SQL> alter table  eseruser.table_secret add dummy_col date;

Table altered.

SQL> alter system switch logfile

System altered.

 

 

Conclusion

 

TDE is a great feature. It is not hard to implement. It is transparent. you do not need to change anything in the application.  If you know the logic behind the encryption, it is pretty easy to manage. Documentation is pretty straight forward and with 12c things are getting more structured.

 

TDE is useful for securing the most important and valuable data in a database. If something goes wrong during the implementation you may never decrypt the data and lose it forever.  I advise you to test it thoroughly and get familiar with this feature. Design a solid, tested, proven backup strategy for the keystores and enjoy this great feature.

 

 

 

OTECH MAGAZINE #7  spring 2015        copyright otech magazine 2015

www.otechmag.com