Oracle Transparent Data Encryption--column encryption ____oracle

All we need to do is to declare a column to be encrypted and the rest to be done by Oracle. When the user inserts or updates data, the column values are intercepted, encrypted, and then saved in the encrypted format. Then, when the column is queried, the value of the column is automatically decrypted, and the decrypted text (plaintext) is returned to the user. Users do not even need to know that there has been encryption and decryption--the so-called transparency. All are done internally by Oracle code and do not require any triggers or complex process logic.


TDE aims to encrypt sensitive data with minimal cost and avoid possible damage to data file theft. Note, however, that the emphasis is on transparency-that is, encryption is done automatically and decryption is the same. In the database. Oracle does not differentiate users. When a user has permission to query an object, Oracle gives the data value in clear text
TDE total three layers of encryption, the first layer, wallet key authentication password, this value is manually set the password, the second layer of wallet key, the third layer, table key, so as long as any layer is not stolen, your data are relatively safe.
Encryption and decryption process


Usage Restrictions:
1. Cannot use TDE in primary key, foreign key column
2. For columns using TDE, we can only create a B-tree index.
3. Large object data types are not available, Blob,clob
4.import/export
5. Other tools that directly Access database data files are not available

Supported cryptographic algorithms:

algorithm	Key Size	Parameter Name
Triple DES (Data encryption Standard)	Bits	3des168
AES (Advanced encryption Standard)	128 Bits	AES128
Aes	(default)	AES192
Aes	256 bits	AES256

Data types that support encryption

CHAR

DATE

INTERVAL Day to SECOND

INTERVAL year to MONTH

NCHAR

Number

NVARCHAR2

RAW

TIMESTAMP (includes TIMESTAMP with time ZONE and TIMESTAMP with local time ZONE)

VARCHAR2

Reference command Description

Task	SQL Command
ADD encrypted column to existing table	ALTER TABLE table_name ADD (column_name datatypeencrypt);
Create table and Encrypt column	CREATE TABLE <table_name> (column_name datatypeencrypt);
Encrypt Unencrypted Existing column	ALTER TABLE table_name MODIFY (column_name ENCRYPT);
Master Key:set or reset	ALTER SYSTEM SET Encryption KEY identified by <password>;
Master key:set or reset to use PKI certificate	ALTER SYSTEM SET Encryption KEY <certificate_id>identified by <password>;
Wallet:open to access master keys	ALTER SYSTEM SET Encryption WALLET OPEN identified by <password>;

For example: The system creates the wallet directory, specifies wallet location mkdir-p/opt/oracle/admin/skydb/wallet Modify Sqlnet.ora Add the following enryption_wallet_location = ( Create wallet in the source= (method=file) method_data= (directory=/opt/oracle/admin/skydb/wallet) database and set access password alter system set

Encryption key authenticated by "123456";
Create a table with an encrypted column, insert related data CREATE TABLE En_tab (ID int,name varchar (), salary number (10,2) encrypt using ' AES256 ');
INSERT into En_tab values (1, ' dabing ', 50000.00);

INSERT into En_tab values (2, ' NewBoy ', 50000.00);
After you set the password, you can manually open or close wallet open wallet alter system set encryption wallet open authenticated by "123456";

Closes wallet alter system set encryption wallet close;
 If you have query permissions, unencrypted column can query, encrypted cannot query, must open wallet can sql> desc hank.en_tab;    Name Null?                                                 Type-----------------------------------------------------------------------------ID                               Number () NAME VARCHAR2 (SALARY)              Number (10,2) ENCRYPT sql> select Id,name from Hank.en_tab;
ID NAME------------------------------1 dabing 2 newboy sql> select SALARY from Hank.en_tab; Select SALARY from Hank.en_tab * ERROR on line 1:ora-28365:wallet are not open to open wallet to normal access to add

The secret column sql> alter system set encryption wallet open authenticated by "123456";

System altered.

    Sql> select SALARY from Hank.en_tab;
SALARY----------50000 50,000 can only create no Salt btree index sql> CREATE index Ind_sar on En_tab (SALARY); Create INDEX Ind_sar on En_tab (Salary) * ERROR in line 1:ora-28338:cannot encrypt index

Ed column (s) with salt sql> ALTER TABLE En_tab Modify (Salary ENCRYPT NO salt);

Table altered.

Sql> CREATE index Ind_sar on En_tab (salary); Index created.

Refer to the data dictionary to see those columns being encrypted
sql> select * from User_encrypted_columns;
table_name                     column_name                    encryption_alg                SAL
---------------------------------------------------- ----------------------------------------
en_tab                         SALARY                         AES 256 bits key              NO

Reference: https://docs.oracle.com/cd/B19306_01/network.102/b14268/asotrans.htm#ASOAG600