advertisements
_____________________________________________________________________________________________________________________
Oracle 10g: Transparent Data Encryption (TDE) introduced in Oracle 10g. It is feature to encrypt the sensitive confidential data. There is no need of change in the application logic to implement the same. The encryption can be applicable for index and data values on the disk. Oracle uses opened WALLET to generate master key for the entire database.
Once you implement this, the column length changes and it’s length will not report in DUMP or VSIZE functions.
Once you implement this, the column length changes and it’s length will not report in DUMP or VSIZE functions.
Oracle 11g: It is an extension of the 10g TDE. In Oracle 11g, entire tablespace can be encrypted. Tablespace encryption relies on the encryption key in the wallet outside the database. When you apply encryption on a tablespace, the entire tables and associated index in the tablespace will be encrypted. The data remains encrypted when it stored in redo logs.
Note: - it is important to keep or backup the encryption key (master key). If you lose your encryption key it will lead you to lose of data in the encrypted tablespace.
Different types of encryption algorithms.
- AES192 Advanced Encryption Standard (the default).
- 3DES168 Triple Data Encryption Standard 168-bit encryption
- AES128 Advanced Encryption Standard 128-bit encryption
- AES256 Advanced Encryption Standard 256-bit encryption
Restrictions on tablespace encryption
- Traditional exp/imp utilities are not supported for the objects from the encrypted tablespace. You should use data pump (expdp and impdp) for export.
- Encryption cannot be implemented on existing tablespace where as it is applicable for new tablespaces.
- Once you set the encryption key for a tablespace, it cannot be recreated.
- Encryption cannot be applicable for undo and temp tablespaces.
- You cannot transport an encrypted tablespace to a database that has already Oracle wallet configured. You should use expdp with ENCRYPTION_MODE=password and import into target database.
- You should set compatible parameter as 11.1 or higher for your database.
- External tables and BFILEs cannot be encrypted.
- The tablespace efficiency or performance will be lower than the un-encrypted tablespaces.
How Transparent Data Encryption (TDE) Works?
All you have to do is tell oracle about the encrypted column, and when you do that oracle generates an encryption key for that table and stores it in the data dictionary. This key is also encrypted by a master key which is stored outside of the database in a secure location called Wallet.
When a user enters data into the column defined as encrypted, Oracle Database 10g gets the master key from the wallet, decrypts the encryption key for that table from the data dictionary, uses that encryption key on the input value, and stores the encrypted data in the database.
When a user selects encrypted columns, Oracle Database 10g transparently retrieves the encrypted table key from the data dictionary, fetches the master key from the wallet, and decrypts the table key. Then the database decrypts the encrypted data on the disk and returns the clear text to the user.
When a user enters data into the column defined as encrypted, Oracle Database 10g gets the master key from the wallet, decrypts the encryption key for that table from the data dictionary, uses that encryption key on the input value, and stores the encrypted data in the database.
When a user selects encrypted columns, Oracle Database 10g transparently retrieves the encrypted table key from the data dictionary, fetches the master key from the wallet, and decrypts the table key. Then the database decrypts the encrypted data on the disk and returns the clear text to the user.
Since the data is stored in encrypted format, it is encrypted everywhere like datafiles, archive redo log files and backupsets. If any of these are stolen the data will be useless without the Wallet master key which doesn't exist in the database at all, and even if the wallet is also stolen it cannot be opened without a password.
Steps to setup Transparent Data Encryption (TDE)?
- Add an entry to sqlnet.ora file
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/data/oracle/product/11.1.0/wallet)))
- Create the wallet directory and check whether oracle user has read write and execute permission for the directory.
mkdir /data/oracle/product/11.1.0/wallet
- Set the encryption key for the wallet.
SQL> alter system set encryption key authenticated BY "welcome1";
System altered.
- Verify the wallet directory whether the file is created or not.
$ cd /data/oracle/admin/prod9/wallet
oracle@prodserv(4105) prod9 /data/oracle/admin/prod9/wallet
$ ls -ltr
total 4
-rw-r--r-- 1 oracle dba 1573 Jul 15 21:26 ewallet.p12
- Table encryption example(Oracle 10g has introduced table encryption)
Create table example_tde with a column encrypt
SQL> create table example_tde(emp_no number(4),
2 name varchar2(10),
3 card_num varchar2(16) ENCRYPT);
Table created.
SQL> insert into example_tde values (11, 'John', '1234123412341234');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from example_tde;
EMP_NO NAME CARD_NUM
---------- ---------- ----------------
11 John 1234123412341234
Close the wallet and check whether you will be able to access or not
SQL> ALTER SYSTEM SET WALLET CLOSE;
System altered.
SQL> select * from example_tde;
select * from example_tde
*
ERROR at line 1:
ORA-28365: wallet is not open
Only un-encrypted columns can be fetched from the table.
SQL> select emp_no, name from example_tde;
EMP_NO NAME
---------- ----------
11 John
SQL> select CARD_NUM from example_tde;
select CARD_NUM from example_tde
*
ERROR at line 1:
ORA-28365: wallet is not open
- Tablespace encryption examples (Oracle 11g provides tablespace encryption)
a. Open the wallet
SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "Welcome1";
System altered.
b. Create a tablespace test_tde. Tablespace encryption does not allow the NO SALT option that is available in TDE.
SQL> create tablespace test_tde
datafile '/data/oracle/oradata/prod9/test_tde01.dbf' reuse
ENCRYPTION USING 'AES256'
extent management local
segment space management auto
DEFAULT STORAGE(ENCRYPT);
Tablespace created.
c. How to check the tablespace is encrypted or not?
SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces
2 where tablespace_name ='TEST_TDE';
TABLESPACE_NAME ENC
------------------------------ ---
TEST_TDE YES
d. Assign scott user default tablespace as test_tde
SQL> alter user scott default tablespace test_tde;
User altered.
e. Create table on test_tde tablespace and check the encryption
SQL> create table example_tde(emp_no number(4),
name varchar2(10),
card_num varchar2(16))
tablespace TEST_TDE;
Table created.
SQL> insert into example_tde values (11, 'John', '1234123412341234');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from example_tde;
EMP_NO NAME CARD_NUM
---------- ---------- ----------------
11 John 1234123412341234
SQL> ALTER SYSTEM SET WALLET CLOSE;
System altered.
SQL> select * from example_tde;
select * from example_tde
*
ERROR at line 1:
ORA-28365: wallet is not open
f. Flush the buffer cache to write the blocks to the datafile.
SQL> alter system flush buffer_cache;
System altered.
g. Checking the encryption using OS command strings
Tablespace without data encryption will give string values
$ strings /data/oracle/oradata/prod9/test_tde01.dbf |grep 1234123412341234
1234123412341234M{
Tablespace with data encryption will not give the string values
$ strings /data/oracle/oradata/prod9/test_tde01.dbf |grep 1234123412341234
oracle@prodserv(4100) prod9 /data/oracle/oradata/prod9
SALT option with encryption and index:
Lets discuss a scenario where there are 100 employees in an organization with a salary of 5000. salary is an encrypted column, so it will contain the value "@^*/-%" a 100 times in the data files making it vulnerable and guessable for the crackers. That is where the SALT option with the encryption comes into play. By default the encryption is done with SALT. Refer to the query we done above on DBA_ENCRYPTED_COLUMNS where you can see the SALT=YES.
You cannot create index on the column encrypted with SALT option.
SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "Welcome1";
System altered.
SQL> create table example_tde(card_num varchar2(16),
name varchar2(10),
card_num_encr varchar2(16) ENCRYPT); 2 3
Table created.
SQL> insert into example_tde values ('1234123412341234', 'John', '123412341234134');
1 row created.
SQL> commit;
Commit complete.
SQL> select COLUMN_NAME,TABLE_NAME,SALT from DBA_ENCRYPTED_COLUMNS;
COLUMN_NAME TABLE_NAME SAL
------------------------------ ------------------------------ ---
CARD_NUM_ENCR EXAMPLE_TDE YES
SQL> create index example_tde_idx on example_tde(CARD_NUM_ENCR);
create index example_tde_idx on example_tde(CARD_NUM_ENCR)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt
SQL> alter table example_tde modify (CARD_NUM_ENCR encrypt no salt);
Table altered.
SQL> create index example_tde_idx on example_tde(CARD_NUM_ENCR);
Index created.
Index on encrypted column and execution plan
Operations with = symbol use the index
SQL> select * from example_tde where CARD_NUM_ENCR ='1234123412341234';
Execution Plan
----------------------------------------------------------
Plan hash value: 1151984961
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EXAMPLE_TDE | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EXAMPLE_TDE_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CARD_NUM_ENCR"='1234123412341234')
Note
-----
- dynamic sampling used for this statement
Operations with like operator on encrypted column will not use the index. You should decrypt the column if you wanted to use the index on execution plan.
SQL> select * from example_tde where CARD_NUM_ENCR like '1234%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2121816070
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EXAMPLE_TDE | 1 | 61 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("CARD_NUM_ENCR") LIKE '1234%')
Note
-----
- dynamic sampling used for this statement
Decrypt the column and index will consider into execution plan
SQL> alter table example_tde modify (CARD_NUM_ENCR decrypt);
Table altered.
SQL> select * from example_tde where CARD_NUM_ENCR like '1234%';
Execution Plan
----------------------------------------------------------
Plan hash value: 1151984961
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EXAMPLE_TDE | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EXAMPLE_TDE_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CARD_NUM_ENCR" LIKE '1234%')
filter("CARD_NUM_ENCR" LIKE '1234%')
Note
-----
- dynamic sampling used for this statement
Dealing with datapump on encrypted tables
If you perform a normal expdp on encrypted table you will get ORA-39173: Encrypted data has been stored unencrypted in dump file set. You should use encryption_password=welcome1 option to perform the expdp or impdp.
$ expdp scott/scott directory=exp_dir dumpfile=tde.dmp tables=example_tde
Export: Release 11.1.0.7.0 - 64bit Production on Saturday, 16 July, 2011 0:25:52
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=exp_dir dumpfile=tde.dmp tables=example_tde
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SCOTT"."EXAMPLE_TDE" 5.867 KB 1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/scott/tde.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 00:28:16
Perform the expdp with encryption_password option
rm -i /home/oracle/scott/tde.dmp
$ expdp scott/scott encryption_password=Welcome1 directory=exp_dir dumpfile=tde.dmp tables=example_tde
Export: Release 11.1.0.7.0 - 64bit Production on Saturday, 16 July, 2011 0:31:18
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** encryption_password=******** directory=exp_dir dumpfile=tde.dmp tables=example_tde
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SCOTT"."EXAMPLE_TDE" 5.875 KB 1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/scott/tde.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 00:33:42
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment