Encryption and Caching features of Oracle Securefile LOB segments

Encryption

You can enable block level encryption for LOB segments using ENCRYPT USING ‘encryption_algorithm” syntax. If you want to prevent the encryption explicitly, use DECRYPT option.

SecureFiles supports 4 encryption algorithms: 3DES168, AES128, AES192, AES256.

The following error will appear if you try to encrypt an object that is owned by SYS user:

ORA-28336: cannot encrypt SYS owned objects

 

The following error will appear if you didn’t create and open a wallet:

ORA-28365: wallet is not open

 

Check the following documentation to configure the wallet properly:

Reference:

Database Advanced Security Administrator’s Guide -> 3 Securing Stored Data Using Transparent Data Encryption-> 3.3.1.4 Using Transparent Data Encryption in a Multi-Database Environment

 

Edit sqlnet.ora file and specify the folder to store the wallet as follows:

 

ENCRYPTION_WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/product/11.2.0/dbhome_1/wallet)))

 

Make sure to create a wallet folder under $ORACLE_HOME and run the following command to create a wallet:

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY mypassword;

 

You can query V$ENCRYPTION_WALLET view to get the status of the wallet:

SQL> COL wrl_parameter format a50

SQL> SELECT status,wrl_parameter FROM V$ENCRYPTION_WALLET;

 

STATUS WRL_PARAMETER

—————— ————————————————–

OPEN /u01/app/oracle/product/11.2.0/dbhome_1/wallet

SQL>

 

Use the following command to open the wallet again after the database is restarted:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY mypassword;

 

Now use ENCRYPT option to encrypt the LOB segment as follows:

SQL> CREATE TABLE dbsnmp.tbl_lob_encrypt (id number, lob_data clob) LOB (lob_data)

STORE AS SECUREFILE (TABLESPACE users ENCRYPT USING ‘AES128’);

 

Table created.

SQL>

 

Caching

 

With caching feature you can bring the LOB segment to the buffer cache. You have to think twice before enabling caching option for the LOB segments, because it can flush necessary block from the buffer cache and can affect the performance of the database.

To put LOB segment to the cache, use CACHE clause as follows:

 

SQL> CREATE TABLE tbl_lob_cache (lob_data CLOB)

LOB (lob_data) STORE AS SECUREFILE (tablespace users CACHE);

Table created.

SQL>

 

To enable cache feature for a LOB segment, use ALTER TABLE .. MODIFY LOB command as follows:

 

SQL> ALTER TABLE tbl_lob_cache MODIFY LOB (lob_data)(CACHE);

Table altered.

SQL>

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>