Database Design

Secure Files

User Rating:

/ 0

Details: Parent Category: Articles; Created on Thursday, 25 March 2010 20:37; Last Updated on Saturday, 26 October 2013 20:07; Published on Thursday, 25 March 2010 20:37; Written by Guy Lambregts; Hits: 3662

Secure Files

If I am not wrong Oracle introduced Large Objects, LOBS in release 8.0. Prior to that release one could use LONG or LONGRAW.

External files can be stored as BFILES, the database only stores a pointer to a file in a database dircetory. CLOBS ( character ) and BLOBS ( binary data ) are stored in the database itself. There are a bunch of storage attributes one can use like

CACHE - NOCACHE, ENABLE STORAGE IN ROW - DISABLE STORAGE IN ROW, CACHE - NOCACHE, PCTINCREASE - RETENTION, CHUNCK

SECURE FILES is a newer technology to store large amounts of data. Some of the enhancements are

1. ENCRYPT - DECRYPT : related to security adn TDE

2. COMPRESS MEDIUM - COMPRESS HIGH - NOCOMPRESS : can save space

3. DEDUPLICATE - KEEP_DUPLICATES : deduplication can save storage

Syntax is similar to the "ordinary" lob storage clause. Note that lobs are from now on also called BASIC FILES

PLATINUM_PM > create table T_SEC_FILES 2 ( PK_T_SEC number primary key, 3 T_SEC_PICTURE blob) 4 tablespace users 5 lob (t_sec_picture) store as securefile t_sec_picture_secfile 6 ( tablespace users 7 retention AUTO 8 deduplicate 9 compress high 10 decrypt 11 nocache 12 logging 13 disable storage in row 14 );PLATINUM_PM >/ Table created.

SQL> alter table T_SEC_FILES modify lob(t_sec_picture) (keep_duplicates);

Table altered.

SQL> alter table T_SEC_FILES modify lob(t_sec_picture) (compress medium);

Table altered.

Note the user_lobs.securefile column.

Encryption is possible if the wallet is open.

SQL> alter table T_SEC_FILES modify lob(t_sec_picture) (encrypt using 'AES128'); alter table T_SEC_FILES modify lob(t_sec_picture) (encrypt using 'AES128') * ERROR at line 1: ORA-28365: wallet is not open

There is also a new instance initialization parameter db_securefile

PLATINUM_PM >show parameter db_securefile

NAME TYPE VALUE

db_securefile string PERMITTED

The below listed values can be used

ALWAYS : all the basic file attributes are converted to secure file attributes ( exception when specific BASICFILE option is used in a NON ASSM tablespace )

NEVER : if secure file option is used then an error is raised

IGNORE : if secure file option is used then secure file specific attributes are ignored ( without error )

FORCE : all the basic file attributes are converted to secure file attributes ( without exception )

PERMITTED : this defaults to traditional LOBS, BASIC FILES and SECURE FILES can be used