How to encrypt data in Oracle SecureFiles

Oracle SecureFiles was introduced in Oracle 11g. It has some advantages that traditional large objects (which have been renamed BasicFile LOB) do not have. Oracle SecureFile LOB supports Smart Data Compression. It is a deduplication method that eliminates multiple storage of identical data copies. It also supports other features that use large objects to improve performance. One of the most useful new features is the transparent data encryption feature of Oracle, which has been extended and added to Oracle SecureFiles.

When we enable TDE on one or more LOB fields, oracle automatically encrypts and decrypts fields at the block level of data files, backup files, and redo log files. After being enabled, LOB can only encrypt fields one by one. All LOB of this field (across all available partitions) will be encrypted. The application and the user accessing the data table have no idea about the transparent data encryption operation. However, because the traditional import and export tools and export operations based on convertible tablespace do not support TDE, you must use the Data Pump Import and Export tools to move the Data containing encrypted fields.

TDE supports the following encryption algorithms:

3DES16 -- uses the 168-bit key's Triple Data Encryption Standard.

AES128-Advanced Encryption Standard that uses a 128-bit key.

AES192 -- use the Advanced Encryption Standard of the 192-bit key. This is the default algorithm.

AES256-use the Advanced Encryption Standard of the 256-bit key.

Before using transparent data encryption, the security administrator must create a wallet and set a master key. We can use the default database wallet shared with other Oracle Database components. In addition, we can create an independent wallet dedicated to TDE. Oracle strongly recommends that you use an independent wallet to store the master encryption key. If the ENCRYPTION_WALLET_LOCATION parameter is not set in the sqlnet. ora file, it uses the WALLET_LOCATION value. If the sqlnet. ora file does not set WALLET_LOCATION, it uses the default database wallet. The default database wallet is ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet.

If you use a TDE-specific wallet, you must use the ENCRYPTION_WALLET_LOCATION parameter to specify the wallet location in the sqlnet. ora file. The master password is used to protect the data table password and tablespace encryption key, and is stored in the external security module. By default, TDE generates a random key. In addition, it may also be an existing key pair dedicated to encryption for PKI certificates.

Use the following command to set the master encryption key:

Alter system set encryption key ["certificate_ID"] identified by "password ";

Certificate_ID -- this is an optional string that contains the unique identifier of the certificate stored in Oracle wallet. If you want to use the private key of PKI as the master encryption key, you can use this parameter.

Password -- this is the wallet password that the security module must use. It is case sensitive.

This database must load the master encryption key into the memory before it can encrypt or decrypt the character/tablespace. The following alter system command will explicitly open wallet:

Alter system set encryption wallet open identified by "password"

The "password" value indicates the password used to create the encryption key. This password string must be added to the double number. After the wallet is enabled, it will remain open until the database instance is closed or the wallet is explicitly closed. The following command will explicitly disable wallet:

Alter system set encryption wallet close identified by "password"

Disabling wallet will disable all encryption and decryption operations. Every time we restart a database instance, we must enable the wallet and re-enable the encryption and decryption operations. Once the wallet is created and enabled, we can use the create table and alter table commands in the ENCRYPT clause.

The transparent data encryption syntax used to encrypt non-LOB fields is exactly the same as that used to enable encryption in Oracle SecureFiles. There is an important difference between them. Non-LOB fields can use the no salt parameter to prevent TDE from adding a random string to the data before encrypting the data. The Oracle SecureFile LOB field does not support the no salt option. We can use the data dictionary view USER_ENCRYPTED_COLUMNS to determine which fields should be encrypted and the status of these fields. Below are some examples of ENCRYPT clauses:

Create table tab_3DES (col1 clob encrypt using '3des168 ')

LOB (col1) store as securefile (

CACHE

NOLOGGING

);

Use the default encryption algorithm (AES192) and a password to create an Oracle SecureFile LOB field:

Create table tab_enc_pw (col1 clob encrypt identified by badpassword)

LOB (col1) store as securefile (

CACHE

);

Enable LOB encryption using AES256:

Alter table tab_nocrypt MODIFY

(Col1 clob encrypt using 'aes256 ');

Modify an Oracle SecureFiles field by regenerate the encryption key:

Alter table tab_3DES rekey using 'aes256 ';

Disable LOB encryption:

Disable LOB encryption:

Alter table tab_enc_pw MODIFY

(Col1 clob decrypt );

The storage capacity of large object data stored in the Oracle database will continue to grow, but the storage cost will continue to decrease. There are many sensitive data, such as medical records, employee data, and intellectual property rights. It is very valuable to encrypt this information, and you can avoid investing a lot of money and energy in the future to achieve security.