Usage of oracle tde transparent Data Encryption

This article will introduce you to some self-knowledge about tde in oracle. If you are interested in tde, please refer to this tutorial.

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:

The Code is as follows:	Copy code
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = D: Oracle11.2.0NETWORKADMINencryption _ wallet )))

Create a Wallet key in SQL * Plus:

The Code is as follows:	Copy code
SQL> connect/as sysdba Connected. SQL> alter system set encryption key authenticated by "eygle "; System altered.

Close and open Wallet:

The Code is as follows:	Copy code
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:

The Code is as follows:

Copy code

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:

The Code is as follows:	Copy code
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:

The Code is as follows:

Copy code

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:

The Code is as follows:	Copy code
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:

The Code is as follows:

Copy code

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:

The Code is as follows:

Copy code

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: TEMPTDE2.DMP Job "EYGLE". "SYS_EXPORT_TABLE_01" completed with 1 error (s) at 15:35:23