Oracle Secure Files Encryption TDE Transparent Tablespace Deduplication SQL Compression

Oracle SecureFiles

Version 11.1

General

Related Data Dictionary Objects

ts$

dba_tablespaces

dba_lobs

gv$parameter

Manage securefile initialization parameter

Parameter Options:

FORCE
PERMITTED (default)

conn uwclass/uwclass

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%secure%';

ALTER SYSTEM SET db_securefile = 'FORCE' SCOPE=MEMORY;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%secure%';

ALTER SYSTEM SET db_securefile = 'PERMITTED' SCOPE=BOTH;

Create Auto Management ASSM Tablespace

conn / as sysdba

desc dba_tablespaces

SELECT tablespace_name, segment_space_management
FROM dba_tablespaces;

desc dba_data_files

SELECT file_name
FROM dba_data_files;

CREATE TABLESPACE securefiletbs
DATAFILE 'c:\temp\securefile01.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

desc dba_tablespaces

SELECT tablespace_name, extent_management, allocation_type, segment_space_management
FROM dba_tablespaces
ORDER BY 1;

Allocate quota

conn / as sysdba

ALTER USER uwclass QUOTA 23M ON securefiletbs;

SELECT username, max_bytes, max_blocks
FROM dba_ts_quotas
WHERE tablespace_name = 'SECUREFILETBS';

Create Wallet directory in operating system

-- Note: This step is identical with the one performed with TRANSPARENT -- DATA ENCRYPTION. if a wallet already exists skip this step.

host

-- mkdir $ORACLE_BASE\admin\<SID>\wallet
mkdir $ORACLE_BASE\admin\orabase\wallet

exit

Alter SQLNET.ORA file

-- Note: This step is identical with the one performed with TRANSPARENT -- DATA ENCRYPTION. if a wallet already exists skip this step.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=FILE) (METHOD_DATA = (DIRECTORY = c:\oracle\product\admin\orabase\wallet)))

-- Note: if you do not use this wallet location you will likely
-- receive ORA-28368: cannot auto-create wallet when setting the key

Set Encryption Key

conn uwclass/uwclass

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "N0way!";

At startup ... or if the Wallet is not open

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "N0way!";

Create table with LOB column in securefile tablespace

conn uwclass/uwclass

CREATE TABLE secure_file_tab (
rid NUMBER(5),
bcol BLOB)
LOB (bcol_lob) STORE AS SECUREFILE bcol (
TABLESPACE securefiletbs
RETENTION MIN 3600
KEEP_DUPLICATES NOCOMPRESS DECRYPT CACHE READS);

conn uwclass/uwclass

CREATE TABLE reg_tab (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS REGFILE (
TABLESPACE uwdata)
TABLESPACE uwdata;

CREATE TABLE sec_tab_kd (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (
TABLESPACE securefiletbs
RETENTION MIN 3600
KEEP_DUPLICATES NOCOMPRESS DECRYPT CACHE READS)
TABLESPACE uwdata;

CREATE TABLE sec_tab_dd (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol2 (
TABLESPACE securefiletbs
RETENTION MIN 3600
COMPRESS ENCRYPT CACHE READS)
TABLESPACE uwdata;

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY 1;

desc user_lobs

col table_name format a10
col column_name format a10

SELECT table_name, column_name, chunk, retention, cache, encrypt, compression, deduplication, in_row, format, securefile
FROM user_lobs;

Load BLOBs

set linesize 121
col owner format a10
col directory_path format a70

SELECT *
FROM all_directories;

/*
conn / as sysdba

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

GRANT read ON DIRECTORY ctemp TO uwclass;

conn uwclass/uwclass
*/

CREATE OR REPLACE PROCEDURE load_blob (filein IN VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('CTEMP', filein);

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);

FOR i IN 1..3 LOOP
    INSERT INTO reg_tab
    (rid, bcol)
    VALUES
    (i, EMPTY_BLOB())
    RETURNING bcol INTO dst_file;

    SELECT bcol
    INTO dst_file
    FROM reg_tab
    WHERE rid = i
    FOR UPDATE;

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    UPDATE reg_tab
    SET bcol = dst_file
    WHERE rid = i;

    INSERT INTO sec_tab_kd
    (rid, bcol)
    VALUES
    (i, EMPTY_BLOB())
    RETURNING bcol INTO dst_file;

    SELECT bcol
    INTO dst_file
    FROM sec_tab_kd
    WHERE rid = i
    FOR UPDATE;

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    UPDATE sec_tab_kd
    SET bcol = dst_file
    WHERE rid = i;

    INSERT INTO sec_tab_dd
    (rid, bcol)
    VALUES
    (i, EMPTY_BLOB())
    RETURNING bcol INTO dst_file;

    SELECT bcol
    INTO dst_file
    FROM sec_tab_dd
    WHERE rid = i
    FOR UPDATE;

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    UPDATE sec_tab_dd
    SET bcol = dst_file
    WHERE rid = i;
END LOOP;
COMMIT;

dbms_lob.fileclose(src_file);
END load_blob;
/

-- with an mpg file
exec load_blob('sphere.mpg');

SELECT COUNT(*)
FROM reg_tab;

SELECT COUNT(*)
FROM sec_tab_kd;

SELECT COUNT(*)
FROM sec_tab_dd;

SELECT dbms_lob.getlength(bcol) FROM reg_tab;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_kd;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_dd;

-- with a doc file
exec load_blob('sphere.html');

SELECT COUNT(*)
FROM reg_tab;

SELECT COUNT(*)
FROM sec_tab_kd;

SELECT COUNT(*)
FROM sec_tab_dd;

SELECT dbms_lob.getlength(bcol) FROM reg_tab;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_kd;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_dd;

Examine Results

col segment_name format a30

SELECT segment_name, segment_type, tablespace_name, blocks
FROM user_segments
WHERE segment_name IN ('REG_TAB', 'SEC_TAB_KD', 'SEC_TAB_DD', 'BCOL', 'BCOL2', 'REGFILE');

set serveroutput on

DECLARE
b    BOOLEAN;
bvar BLOB;
BEGIN
SELECT bcol
INTO bvar
FROM sec_tab2
WHERE rownum = 1;

b := dbms_lob.issecurefile(bvar);

IF b THEN
    dbms_output.put_line('Stored in a securefile');
ELSE
    dbms_output.put_line('Not stored in a securefile');
END IF;
END;
/

DECLARE
bvar BLOB;
BEGIN
SELECT bcol
INTO bvar
FROM sec_tab_kd
WHERE rownum = 1;

dbms_output.put_line(dbms_lob.getoptions(bvar, 1));
dbms_output.put_line(dbms_lob.getoptions(bvar, 3));
dbms_output.put_line(dbms_lob.getoptions(bvar, 4));

SELECT bcol
INTO bvar
FROM sec_tab_dd
WHERE rownum = 1;

dbms_output.put_line(dbms_lob.getoptions(bvar, 1));
dbms_output.put_line(dbms_lob.getoptions(bvar, 3));
dbms_output.put_line(dbms_lob.getoptions(bvar, 4));
END;
/

Another SecureFiles Demo

CREATE TABLE comp_high (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (COMPRESS HIGH)
TABLESPACE securefiletbs;

Related Topics

DBMS_LOB

Large Objects (LOBs)