Usage of oracle tde transparent Data Encryption

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.