Summary
Oracle has provided a way to automatically encrypt data in a column of a table and at the same time decrypt it automatically therefore allowing an application to function as if the column was not encrypted. Transparent data encryption will protect the data in a column in a table with the use of a wallet. Transparent data encryption by default uses the algorithm AES with 192-bit key for data encryption. The following is a short test showing the use of Transparent Data Encryption.
There is overhead to transparent data encryption that can not be ignored as the encryption during inserts and updates, and decryption operations during select operations consume CPU cycles. While this overhead is fairly low it is safer to selectively apply encryption to where it is truly needed on a column basis. Therefore proper load tests should be performed incrementally as column encryption is rolled out so that full impact to system can be measured.
There are three significant things to mention about encryption one being that streams does not support the use of encrypted columns the other two have to do with the use of indexes. One you can not index columns that are encrypted with salt therefore columns encrypted that require an index as the column will be used in searches will need to be encrypted without salt. By default transparent data encryption is done with salt and to encrypt a column without salt the “no salt” option must be specified. Second indexes for encrypted columns behave different in a single respect but can have a large impact. Take table emp for example, add column ssn and encrypt it and build an index on it. We will use 2 query examples:
select ename from emp where ssn = ‘123-45-6789’ ;
This query will use the index and will not matter if the column is encrypted or not.
select ename from emp where ssn like ‘123-45-%’ ;
This query however will depend on if the column is encrypted or not to whether the index is used. If the column is encrypted the index will be ignored, however if the column is not encrypted the index would indeed be used. The reason the index is not used it due to the index now the column has an encrypted value and the ability to substring for an encrypted value is not possible. We will show that the index is indeed not used in the test below.
10g (10.2.0.3 Windows) and 11g (11.1.0.6) Test
1. Set the Wallet location for encryption in the sqlnet.ora file.
10g
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=c:\oracle\product\10.2.0\db_1\network\admin\ENCRYPT)))
11g
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=c:\oracle\product\10.2.0\db_1\network\admin\ENCRYPT)))
2. Create the wallet, initial creation of the wallet will automatically open it.
alter system set encryption key authenticated by "xxxxxxxxx" ;
SQL> alter system set encryption key authenticated by "xxxxxxxxx" ;
System altered.
3. Each time you need to use the wallet it will need to be explicitly opened. The wallet will allow the automatic decryption of data as it is read, otherwise the data of encrypted columns will remain encrypted and not able to be viewed. All other columns that are not encrypted work as normal even when the wallet is not open. In this case since it was just created the wallet is already open.
alter system set encryption wallet open authenticated by "kim11ball" ;
SQL> alter system set encryption wallet open authenticated by "xxxxxxxxx" ;
alter system set encryption wallet open authenticated by "xxxxxxxxx"
*
ERROR at line 1:
ORA-28354: wallet already open
Note: Can close the wallet explicitly with the following alter system set encryption wallet close ;
SQL> alter system set encryption wallet close ;
System altered.
4. Lets open the wallet so we can proceed with the test
SQL> alter system set encryption wallet open authenticated by "xxxxxxxxx" ;
System altered.
5. To test our transparent data encryption by using the emp table, first add an ssn column.
SQL> alter table emp add ssn varchar2(11) ;
Table altered.
SQL> update emp set ssn = ‘222-22-2222’ ;
51 rows updated.
SQL> commit ;
6. Encrypt the ssn column
SQL> alter table emp modify (ssn encrypt) ;
Table altered.
Note: Can turn the column encryption off with the following
SQL> alter table emp modify (ssn decrypt) ;
Table altered.
7. Check the emp table structure as we can see the SSN column show encrypt.
SQL> desc emp
Name Null? Type
----------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SSN VARCHAR2(11) ENCRYPT
8. Let verify the wallet is indeed closed.
SQL> alter system set encryption wallet close;
System altered.
SQL> alter system set encryption wallet open authenticated by "nsfjsnfjksfa" ;
alter system set encryption wallet open authenticated by "nsfjsnfjksfa"
*
ERROR at line 1:
ORA-28353: failed to open wallet
SQL> alter system set encryption wallet close;
alter system set encryption wallet close
*
ERROR at line 1:
ORA-28365: wallet is not open
9. As we can see we are going to do the query with a user called temp the only permissions on this user is select on the emp table and create session.
SQL> select user from dual ;
USER
------------------------------
TEMP
10. If we look at the SSN column we see that the column queries unencrypted, 10g is restricting the access to the column with the wallet closed as expected
SQL> select ssn from emp ;
select ssn from emp
*
ERROR at line 1:
ORA-28365: wallet is not open
11. Not open the wallet for some more testing
SQL> alter system set encryption wallet open authenticated by "xxxxxxxxx" ;
System altered.
12. See if an index can be built, as the summary indicates an index can not be built on an encrypted column with salt.
SQL> create index emp_ssn on emp (ssn) ;
create index emp_ssn on emp (ssn)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt
13. Turn off the salt using the alter table statement so that the index can be built.
SQL> alter table emp modify ssn encrypt no salt ;
Table altered.
14. Now build the index
SQL> create index emp_ssn on emp (ssn) ;
Index created.
15. Now check the affect the encryption has on the index usage.
A: Check using the column in the where clause with an =
SQL> explain plan for select ename from emp where ssn = '123-45-6789' ;
Explained.
SQL> @plan
Plan hash value: 1582005447
---------------------------------------------------------------------------
Id Operation Name RowsBytesCost(%CPU)Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 1 38 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWIDEMP 1 38 2 (0) 00:00:01
* 2 INDEX RANGE SCAN EMP_SSN 1 1 (0) 00:00:01
---------------------------------------------------------------------------
B: Check the index use with the LIKE, as we can see by the explain plan the index is suppressed.
SQL> explain plan for select ename from emp where ssn like '123-45-%' ;
Explained.
SQL> @plan
Plan hash value: 3956160932
---------------------------------------------------------------
Id Operation NameRowsBytesCost(%CPU)Time
---------------------------------------------------------------
0 SELECT STATEMENT 2 76 3 (0)00:00:01
* 1 TABLE ACCESS FULL EMP 2 76 3 (0)00:00:01
---------------------------------------------------------------
16. No remove the encryption from the ssn column and see if the like uses the index as expected and indeed the index is used.
SQL> alter table emp modify (ssn decrypt) ;
Table altered.
SQL> explain plan for select ename from emp where ssn like '123-45-%' ;
Explained.
SQL> @plan
Plan hash value: 1582005447
---------------------------------------------------------------------------
Id Operation Name RowsBytesCost(%CPU) Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 2 28 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWIDEMP 2 28 2 (0) 00:00:01
* 2 INDEX RANGE SCAN EMP_SSN 1 1 (0) 00:00:01
---------------------------------------------------------------------------
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment