Database Design

Secure Files

User Rating:  / 0
PoorBest 
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: 3718

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