DB_SECUREFILE Options

db_securefile parameter could be set to one of the five permitted values. They are
PERMITTED :- allows SECUREFILE LOBs to be created provided ASSM tablespace is used. This is the default value.

sqlplus / as sysdba
SQL> show parameter db_secure

NAME           TYPE        VALUE
-------------- ----------- ---------
db_securefile  string      PERMITTED 

SQL> create table seclob (a number, b blob)  lob(b) store as securefile;
create table seclob (a number, b blob)  lob(b) store as securefile
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "SYSTEM"

NEVER :- disallow SECUREFILE LOBs from being created. If securefile option is used then table create command still succeed but secure file is not used. Error is thorwn only if any of the securefile options are specified in the create statement.

SQL> conn asanga/*****
Connected.

SQL> alter session set db_securefile='NEVER';

Session altered.

SQL> create table seclob (a number, b blob)  lob(b) store as securefile;

Table created.

SQL> select table_name,column_name,securefile from user_lobs;

TABLE  COLUMN SEC
------ ------ ---
SECLOB B      NO 

SQL> drop table seclob;

Table dropped.

SQL> create table seclob (a number, b blob)  lob(b) store as securefile(compress);
create table seclob (a number, b blob)  lob(b) store as securefile(compress)
                                                                           *
ERROR at line 1:
ORA-43854: use of a BASICFILE LOB where a SECUREFILE LOB was expected

ALWAYS :- creates securefiles even if basicfile is specified.

SQL> alter session set db_securefile='ALWAYS';

SQL> create table seclob (a number, b blob)  lob(b) store as basicfile;

Table created. 

SQL> select table_name,column_name,securefile from user_lobs;

TABLE  COLUMN SEC
------ ------ ---
SECLOB B      YES

On the securefile and large object guide it is stated "ALWAYS attempts to create all LOBs as SECUREFILE LOBs but creates any LOBs not in ASSM tablespaces as BASICFILE LOBs, unless SECUREFILE is explicitly specified. Any BASICFILE LOB storage options specified will be ignored, and the SECUREFILE defaults are used for all storage options not specified."

SQL> create tablespace mssm datafile '+DATA(datafile)' segment space management manual;

Tablespace created.  

SQL> alter session set db_securefile='ALWAYS';

Session altered.

SQL> create table seclob (a number, b blob)  lob(b) store as securefile (tablespace mssm);
create table seclob (a number, b blob)  lob(b) store as securefile (tablespace mssm)
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "MSSM"

This was tested on 11.1.0.7.4 database and it seem when MSSM tablespace is specified rather than creating basicfile it throws an error.

Securefile and large object guide states "If FORCE is specified, all LOBs created in the system will be created as SECUREFILE LOBs. If the LOB is being created in an MSSM tablespace, an error will be thrown. Any BASICFILE LOB storage options specified will be ignored, and the SECUREFILE defaults are used for all storage options not specified.". This behavior is same as "ALWAYS"

SQL> alter session set db_securefile='FORCE';

Session altered.

SQL> create table seclob (a number, b blob)  lob(b) store as basicfile;

Table created.

SQL> select table_name,column_name,securefile from user_lobs;

TABLE_ COLUMN SEC
------ ------ ---
SECLOB B      YES 

SQL> drop table seclob;

SQL> create table seclob (a number, b blob)  lob(b) store as securefile (tablespace mssm);
create table seclob (a number, b blob)  lob(b) store as securefile (tablespace mssm)
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "MSSM"

Last option is ignore which will ignore securefile keyword and any securefile options specified in the create statement. This is true for all options except encrypt, where instead of being ignored an error is thrown if the wallet is not open.

SQL> alter session set db_securefile='IGNORE';

Session altered.

SQL> create table seclob (a number, b blob)  lob(b) store as securefile;

Table created.

SQL> select table_name,column_name,securefile from user_lobs;

TABLE_ COLUMN SEC
------ ------ ---
SECLOB B      NO


SQL> drop table seclob;

SQL> create table seclob (a number, b blob)  lob(b) store as securefile(compress deduplicate);

Table created.

SQL> select table_name,column_name,securefile,compression,deduplication from user_lobs;

TABLE_ COLUMN SEC COMPRE DEDUPLICATION
------ ------ --- ------ -------------
SECLOB B      NO  NONE   NONE

As soon as the encrypt option is specified following error is thrown. Encrypt option could be specified in two places but not at the same time

SQL> create table seclob (a number, b blob)  lob(b) store as securefile(encrypt using 'AES128');
create table seclob (a number, b blob)  lob(b) store as securefile(encrypt using 'AES128')
*
ERROR at line 1:
ORA-28365: wallet is not open 


SQL> create table seclob (a number, b blob encrypt using 'AES128')  lob(b) store as securefile;
create table seclob (a number, b blob encrypt using 'AES128')  lob(b) store as securefile
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> create table seclob (a number, b blob)  lob(b) store as securefile(compress encrypt identified by asanga);
create table seclob (a number, b blob)  lob(b) store as securefile(compress encrypt identified by asanga)
*
ERROR at line 1:
ORA-28365: wallet is not open

If a wallet is created and if it is open then this create statement will succeed and encrypt option will be ignored.

Create the default wallet directory if does not exists. If this directory doesn't exists "ORA-28368: cannot auto-create wallet" will be thrown

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet

sqlplus / as sysdba

SQL> alter system set encryption key identified by "asanga321";

System altered.

Create the securefile with all options

create table seclob (a number, b blob)  lob(b) store as securefile(compress encrypt using 'AES128' deduplicate);

Table created.  

SQL> select table_name,column_name,securefile,compression,deduplication,encrypt from user_lobs;

TABLE_ COLUM SEC COMPRE DEDUPLICATION   ENCR
------ ----- --- ------ --------------- ----
SECLOB B     NO  NONE   NONE            NONE

Closing the wallet and trying to create the table will fail as before

alter system set encryption wallet close;

SQL>drop table seclob;

SQL> create table seclob (a number, b blob)  lob(b) store as securefile(compress encrypt using 'AES128' deduplicate);
create table seclob (a number, b blob)  lob(b) store as securefile(compress encrypt using 'AES128' deduplicate)
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> alter system set encryption wallet open identified by "asanga321";

SQL> create table seclob (a number, b blob)  lob(b) store as securefile(compress encrypt using 'AES128' deduplicate);

Table created.