Experience: using the TDE feature of Oracle for encryption (1)

Use the transparent data encryption (TDE) of the Oracle database introduced as part of Oracle Advanced Security Option Version 10.2 and later ), you can selectively Encrypt sensitive database data stored in the underlying database data files and all downstream file components, such as online redo logs, archived redo logs, and Database backups. The basic goal of TDE is to protect sensitive data found in these original operating system files and prevent malicious people from snoop on the data when they access the disk or back up the tape, then try to restore the database or scan the data in the original operating system file, such as personal identifiable information or credit card information.

As part of my consulting practices, I have implemented TDE multiple times. However, before one of the most recent contracts, I used TDE to encrypt new columns in the existing table or columns in the new table. In both cases, it is very simple to use TDE because the target column is empty, so there is no high risk due to the lack of data and the relevance of existing applications.

I recently implemented a different TDE experience. I helped a large company encrypt existing columns in a table with more than 1 million rows. There is also a column-dependent key task application, so you can imagine that there are many important things to consider before you start working. After searching for similar situations that provide experience on the Internet, I found that only a few excellent resources can help me.

This topic describes the lessons learned when using TDE to encrypt existing data. If you try to use TDE for existing column data, I hope the information provided here can help you quickly and effectively perform similar work.

Determine possible limits

When studying the customer's system, the first thing I do is to find the data model features related to the target column that will forbid us to encrypt the column, or, you can find out the items related to the columns that may have a negative impact on the existing operations. This study includes column indexing and integrity constraints.

As the Oracle documentation clearly states, you need to understand many restrictions when you want to encrypt a column with an index. Oracle does not allow encryption of Columns with bitmap indexes, which is not closely related to our situation. However, the target column has multiple common B-trees) indexes. Although Oracle allows encryption of Columns with common indexes, Oracle prohibits the encryption of "salt processing" on index columns. Salt processing improves the security of duplicate data by adding random strings to the data before encryption. Therefore, it is more difficult for thieves to crack encrypted data by using pattern matching recognition technology. All in all, after the initial analysis, we will encounter a situation where we can encrypt the columns, but not perform salt processing.

After analyzing the column indexes, I could have reached this point, but the next question I want to answer is "is it appropriate to use these indexes ?" My thinking process is like this: if the index is useless, I will delete it to reduce the system overhead necessary to maintain the index entry, especially considering the extra burden of encryption. To determine whether the index is useful, I use the index monitoring feature of the Oracle database. I found that the index is actually in use, so we must continue to maintain it.

Next, I checked whether the target column is involved in the reference integrity constraints. Since each table has its own encryption key, Oracle does not allow you to use columns involved in the TDE foreign key relationship for encryption. In our case, the reference integrity constraints do not involve the target column.

Evaluate performance overhead

One of the first questions my customers asked was, "How Does TDE affect the general performance of my applications ?" A small part of the Oracle document describes the impact of TDE on the performance of related applications. However, my customers want to obtain specific statistics to help them understand how TDE affects the daily data loading process with strict time requirements.

To meet the customer's needs, I calculated the average number of rows inserted into the target table every day when there are strict time requirements. Then, I created a similar test table and index in the same sandbox environment on the client, measuring the time it took to insert the same number of rows before and after the encrypted target column. The difference in time consumption makes us better understand the "performance loss" caused by column data encryption in this process ". List 1 is an example of how I use SQL * Plus to perform this operation.

SQL> CONNECT system
Enter password:
Connected.
SQL> -- Configure Oracle-Managed (Data) Files
SQL> ALTER SYSTEM
2 SET db_create_file_dest = '/data01/oracle /'
3 SCOPE = MEMORY;

System altered.

SQL> -- Create two new tablespaces for the demo,
SQL> -- one for data segments, one for index segments
SQL> CREATE TABLESPACE data_001
2 datafile size 1G;

Tablespace created.

SQL> CREATE TABLESPACE indx_001
2 datafile size 500 M;

Tablespace created.

SQL> -- Create a user for the demo
SQL> CREATE USER app_001 IDENTIFIED BY app
2 default tablespace data_001
3 temporary tablespace temp
4 quota unlimited on data_001
5 quota unlimited on indx_001;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO app_001;

Grant succeeded.

SQL> -- Work as the demo user
SQL> CONNECT app_001/app;
Connected.
SQL> -- Create the demo table in the default tablespace
SQL> CREATE TABLE app_001.transactions (
2 trans_id INTEGER
3 CONSTRAINT transactions_pk PRIMARY KEY
4 using index tablespace indx_001,
5 credit_card INTEGER NOT NULL
6 );

Table created.

SQL> -- Create an index in the INDX_001 tablespace
SQL> CREATE INDEX app_001.transactions_ndx1
2 ON app_001.transactions (credit_card)
3 TABLESPACE indx_001;

Index created.

SQL> -- Time how long it takes to load data in the clear
SQL> SET TIMING ON;
SQL> BEGIN
2 -- AMEX
3 FOR I IN 1 .. 100000 LOOP
4 insert into app_001.transactions (trans_id, credit_card)
5 VALUES (
6 I,
7'34' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
8 );
9 end loop;
10 COMMIT;
11 -- VISA
12 FOR I IN 100001 .. 400000 LOOP
13 insert into app_001.transactions (trans_id, credit_card)
14 VALUES (
15 I,
16 '4' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 999999999999999 ))
17 );
18 end loop;
19 COMMIT;
20 -- MASTERCARD
21 FOR I IN 400001 .. 500000 LOOP
22 insert into app_001.transactions (trans_id, credit_card)
23 VALUES (
24 I,
25 '54' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
26 );
27 end loop;
28 COMMIT;
29 END;
30/

PL/SQL procedure successfully completed.

Elapsed: 00:00:56. 14
SQL> SET TIMING OFF;
SQL> -- Remove existing synthetic data
SQL> TRUNCATE TABLE app_001.transactions;

Table truncated.

SQL> -- Enable encryption of the credit card column
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card encrypt no salt );

Table altered.

SQL> -- Time how long it takes to load encrypted data
SQL> SET TIMING ON;
SQL> BEGIN
2 -- AMEX
3 FOR I IN 1 .. 100000 LOOP
4 insert into app_001.transactions (trans_id, credit_card)
5 VALUES (
6 I,
7'34' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
8 );
9 end loop;
10 COMMIT;
11 -- VISA
12 FOR I IN 100001 .. 400000 LOOP
13 insert into app_001.transactions (trans_id, credit_card)
14 VALUES (
15 I,
16 '4' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 999999999999999 ))
17 );
18 end loop;
19 COMMIT;
20 -- MASTERCARD
21 FOR I IN 400001 .. 500000 LOOP
22 insert into app_001.transactions (trans_id, credit_card)
23 VALUES (
24 I,
25 '54' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
26 );
27 end loop;
28 COMMIT;
29 END;
30/

PL/SQL procedure successfully completed.

Elapsed: 00:01:16. 31
SQL> SET TIMING OFF;

List 1Using the same sandbox environment as your production environment, you can easily compare the time it takes to enable column encryption before and after loading representative datasets, so that you can better understand the impact of column encryption on production system performance.

Like all performance tests, I suspect that the performance loss caused by column encryption may vary with the system, depending on the CPU and average load of common variables ). In list 1, you notice that the computing performance loss is 36% (56.14-76.31)/56.14) * 100). However, using the experimental evidence we collect in the customer system, it is estimated that the data loading process will take about 11% more time, which is exactly the same as using TDE in production.

In this example, I focus on estimating the performance loss of data encryption during data loading with indexes. If your system has different types of key processes, such as demanding report generation cycles, we recommend that you use a sandbox environment to compare the time taken before and after data encryption. The "determine potential query plan changes" section after this article will discuss special considerations for queries and data encryption.

Handle downtime and maintenance time

Another concern of my customers is that production applications that need to be disabled when encrypting existing column data in tables with approximately 1 million rows. I initially thought that theoretically no application needs to be stopped-after all, the Oracle document clearly states that encrypting the data in the existing column is essentially a multirow update of the entire table. Without more consideration, I will not understand why new rows cannot be inserted to tables concurrently and why existing row updates cannot be continued. When I muttered the familiar Oracle slogan "the reader will not stop the writer, nor will the writer stop the reader", I did not expect column encryption will affect the query.

However, after working as a DBA for a long time, I concluded that to make the final practical changes to the production system, we need to test the theory to avoid unexpected problems, this is very important. You see, when I tested the application itself for the sandbox database during the encryption column, I found a lot of problems. Most importantly, I found that encryption in progress extended the response time of some queries, so that the application may encounter response timeout. These timeouts can lead to connection disconnections and subsequent transaction failures, which in turn will be more troublesome-I will provide you with detailed information.

It must be mentioned that, after the test, I learned that stopping the application is definitely not without a reason. But the next question is, how long does the production application take offline? Can columns be encrypted within the normal two-hour maintenance period planned for each weekend? Or does it take a longer downtime? To solve this problem, I only need to measure the time it takes to encrypt columns in the sandbox environment because the sandbox environment has the same server hardware and dataset as the production environment. I found that column encryption takes more than an hour to complete. Frankly speaking, it took me less than five minutes to simulate and test the encrypted operation of similar data on my laptop, so I was shocked when it took so long. However, when we encrypt columns in the production database system, the most important thing is to use outdated server hardware.

Knowing that it takes more time to execute other tasks during normal maintenance time, I decided to find a way to reduce the time spent on encryption columns. My first instinct is to delete two indexes that contain the target column. In this way, Oracle only needs to encrypt the column data in the table itself, and then I can effectively re-index the data without the overhead of logging. After some new tests, I reduced the time required for encryption columns and related indexes from 70 minutes during encryption) to 20 minutes for encryption columns and then re-indexing ). List 2 is the test example I used to draw conclusions from where we stop in List 1 ). In addition, note that the time in the list comes from the testing system used to compile this article, rather than the actual system used by my client.

SQL> -- Remove existing synthetic data
SQL> TRUNCATE TABLE app_001.transactions;

Table truncated.

SQL> -- Disable encryption of the credit card column
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card DECRYPT );

Table altered.

SQL> -- Load new synthetic data
SQL> BEGIN
2 -- AMEX
3 FOR I IN 1 .. 100000 LOOP
4 insert into app_001.transactions (trans_id, credit_card)
5 VALUES (
6 I,
7'34' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
8 );
9 end loop;
10 COMMIT;
11 -- VISA
12 FOR I IN 100001 .. 400000 LOOP
13 insert into app_001.transactions (trans_id, credit_card)
14 VALUES (
15 I,
16 '4' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 999999999999999 ))
17 );
18 end loop;
19 COMMIT;
20 -- MASTERCARD
21 FOR I IN 400001 .. 500000 LOOP
22 insert into app_001.transactions (trans_id, credit_card)
23 VALUES (
24 I,
25 '54' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
26 );
27 end loop;
28 COMMIT;
29 END;
30/

PL/SQL procedure successfully completed.

SQL> -- Time how long it takes to encrypt credit card data
SQL> -- with corresponding indexes in place
SQL> SET TIMING ON;
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card encrypt no salt );

Table altered.

Elapsed: 00:02:27. 18
SQL> SET TIMING OFF;
SQL> -- Remove existing synthetic data
SQL> TRUNCATE TABLE app_001.transactions;

Table truncated.

SQL> -- Drop all indexes that correspond to the credit card column
SQL> DROP INDEX app_001.transactions_ndx1;

Index dropped.

SQL> -- Disable encryption of the credit card column
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card DECRYPT );

Table altered.

SQL> -- Load new synthetic data
SQL> BEGIN
2 -- AMEX
3 FOR I IN 1 .. 100000 LOOP
4 insert into app_001.transactions (trans_id, credit_card)
5 VALUES (
6 I,
7'34' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
8 );
9 end loop;
10 COMMIT;
11 -- VISA
12 FOR I IN 100001 .. 400000 LOOP
13 insert into app_001.transactions (trans_id, credit_card)
14 VALUES (
15 I,
16 '4' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 999999999999999 ))
17 );
18 end loop;
19 COMMIT;
20 -- MASTERCARD
21 FOR I IN 400001 .. 500000 LOOP
22 insert into app_001.transactions (trans_id, credit_card)
23 VALUES (
24 I,
25 '54' | TRUNC (DBMS_RANDOM.VALUE (low => 0, high => 99999999999999 ))
26 );
27 end loop;
28 COMMIT;
29 END;
30/

PL/SQL procedure successfully completed.

SQL> -- Time how long it takes:
SQL> -- 1. Encrypt credit card data without corresponding indexes in place
SQL> -- 2. Recreate corresponding indexes
SQL> SET TIMING ON;
SQL> ALTER TABLE app_001.transactions
2 MODIFY (credit_card encrypt no salt );

Table altered.

Elapsed: 00:01:15. 48
SQL> CREATE INDEX app_001.transactions_ndx1
2 ON app_001.transactions (credit_card)
3 TABLESPACE indx_001
4 PARALLEL 2
5 NOLOGGING;

Index created.

Elapsed: 00:00:02. 98
SQL> SET TIMING OFF;

List 2To quickly encrypt existing data, you only need to delete the underlying index of the column before encrypting it, and then re-create the index.

Note: The create index statement is used in the simulated environment in this article. In actual settings, you can use the DBMS_METADATA utility package of the Oracle database to generate the create index statement. You can use these statements to re-CREATE an INDEX after data encryption.

In short, the new policy for re-indexing after column encryption allows more time to deal with the most challenging problems in the process, which will be described in the next section.