Oracle Transparent Data Encryption-Transparent Data Encryption

 

Oracle's transparent data encryption is part of Oracle's Advanced Security Options and requires additional software fees. This option can be encrypted in combination with multiple means, including using Wallet (PKCS #12 standard) and supporting PKCS #11 RAS hardware devices.
In 10 Gb, transparent encryption supports column-level encryption, while in Oracle 11gR2, table space-based transparent encryption is added.

 

The flowchart of encryption and decryption in the official documents is as follows:

The following is a simple test.

Add the following section to the SQLNET. ora file:

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY = D: \ Oracle \ 11.2.0 \ NETWORK \ ADMIN \ encryption_wallet \)))

Create a Wallet key in SQL * Plus:

SQL> connect/as sysdba
Connected.
SQL> alter system set encryption key authenticated by "eygle ";

 

System altered.

Close and open Wallet:

SQL> alter system set encryption wallet close;
Alter system set encryption wallet close
*
ERROR at line 1:
ORA-28390: auto login wallet not open

 

SQL> alter system set encryption wallet close identified by "eygle ";

System altered.

SQL> alter system set wallet open identified by "eygle ";

System altered.

You can specify encryption when creating a data table:

SQL> connect eygle/eygle
Connected.
SQL> create table tde (id number (10), data varchar2 (50) encrypt );

 

Table created.

SQL> insert into tde select user_id, username from dba_users;

9 rows created.

SQL> commit;

Commit complete.

SQL> connect/as sysdba
Connected.
SQL> select * from eygle. tde;

ID DATA
---------------------
0 SYS
5 SYSTEM
34 EYGLE
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM

Encryption and decryption are performed automatically.

You can query the dba_encrypted_columns view to find the encryption column:

SQL> select * from dba_encrypted_columns;

 

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL
-------------------------------
Eygle tde data aes 192 bits key YES SHA-1

If the Wallet is disabled, the encrypted Column cannot be accessed:

SQL> select * from eygle. tde;
Select * from eygle. tde
*
ERROR at line 1:
ORA-28365: wallet is not open

 

SQL> alter system set encryption wallet close identified by "eygle ";

System altered.

SQL> select * from eygle. tde;
Select * from eygle. tde
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> desc eygle. tde
Name Null? Type
------------------------------------
Id number (10)
DATA VARCHAR2 (50) ENCRYPT

SQL> select id from eygle. tde;

ID
----
0
5
34
9
31
30
32
14
21

9 rows selected.

When you encrypt a column, there are two options: Salt and No Salt.
Salt adds a string to the data before encryption, which increases the difficulty of cracking and makes the same string encryption results different. For NO Salt, the same string can obtain the same encrypted output, its security is relatively low.

In an encrypted column, an index cannot be created if Salt is used. Attributes of Salt encryption and indexing are mutually exclusive and cannot be set at the same time:

SQL> create index idx01 on tde (data );
Create index idx01 on tde (data)
*
ERROR at line 1:
ORA-28338: Column (s) cannot be both indexed and encrypted with salt

When you use the default Salt encryption method, you can create an index for the encrypted column:

SQL> create table tde2 (id number (10) encrypt no salt, data varchar2 (50 ));

Table created.

SQL> insert into tde2 select user_id, username from dba_users;

9 rows created.

SQL> select * from tde2;

ID DATA
---------------------
0 SYS
5 SYSTEM
34 EYGLE
9 OUTLN
31 APPQOSSYS
30 DBSNMP
32 WMSYS
14 DIP
21 ORACLE_OCM

9 rows selected.

SQL> commit;

Commit complete.

SQL> create index idx1 on tde2 (id );

Index created.

When the export is executed, Oracle will prompt:

D: \> expdp eygle/eygle directory = temp dumpfile = tde2.dmp tables = TDE

 

Export: Release 11.2.0.2.0-Production on Thu Sep 8 15:35:19 2011
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "EYGLE". "SYS_EXPORT_TABLE_01": eygle/******** directory = temp dumpfile = tde2.dmp tables = 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
.. Exported "EYGLE". "TDE" 5.562 KB 9 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "EYGLE". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for EYGLE. SYS_EXPORT_TABLE_01 is:
D: \ TEMP \ TDE2.DMP
Job "EYGLE". "SYS_EXPORT_TABLE_01" completed with 1 error (s) at 15:35:23

The prompt indicates that encrypted data is stored in an unencrypted manner in the dump file, but when importing, an encrypted Wallet is required to be able to perform the import, otherwise an exception in the ORA-28362 will occur

 

From: http://www.eygle.com/archives/2011/09/oracle_transparent_data_encryption.html