8 DBFS SecureFile Store

Create and Register a New SecureFile Store

Create a new SecureFile Store file system store store_name in schema schema_name (defaulting to the current schema) as table tbl_name, with the table (and internal indexes) in tablespace tbl_tbs (defaulting to the schema's default tablespace), and its lob column in tablespace lob_tbs (defaulting to tbl_tbs).

If use_bf is true, a BasicFile LOB is used, otherwise a SecureFile LOB is used.

props is a table of (name, value, typecode) tuples that can be used to configure the store properties. Currently, no such properties are defined or used, but the placeholder exists for future versions of the reference implementation.

If the create_only argument is true, the file system is created, but not registered with the current user -- a separate call to DBMS_DBFS_SFS_ADMIN.registerFilesystem (by the same users or by other users) is needed to make the file system visible for provider operations.

The procedure executes like a DDL (auto-commits before and after its execution). createStore is a wrapper around createFilesystem.

Initialize or Re-initialize A SecureFile Store File System Store

Procedure initFS truncates and re-initializes the table associated with the SecureFile Store store_name. The procedure executes like a DDL (auto-commits before and after its execution).

Unregister and Drop A SecureFile Store File System Store

All stores referring to the file system are removed from the metadata tables, and the underlying file system table is itself dropped. The procedure executes like a DDL (auto-commits before and after its execution).

Permissions Management

1. Create (or pick) your DBFS Content API target users. For example, we will assume that you wish to use the DBFS Content API as database user/password "foo/***", "bar/***", and "scott/tiger". At minimum, these database users should have the following privileges:

- create session

- resource

- create view

Always use a regular database user for all operational access to the Content API and stores. Never use the SYS or SYSTEM users, or the SYSDBA / SYSOPER roles for DBFS Content API operations.

2. Grant the dbfs_role to each of these users. The dbfs_role controls who is authorized to use the DBFS Content API, and indirectly confers additional privileges to the target users.

connect / as sysdba
grant dbfs_role to foo;
grant dbfs_role to bar;
grant dbfs_role to scott;

3. Without this role, the DBFS Content API is not available to a database user. A user with suitable administrative privileges (or SYSDBA) can grant the role to additional users as needed.

At the end of these steps, the DBFS Content API has been set up for use by any database user who has the dbfs_role.

Given the way roles, access control, and definer/invoker rights interact in the database, it may be necessary to explicitly grant various permissions (almost always execute permissions) on DBFS Content API types (SQL types with the DBMS_DBFS_CONTENT_ prefix) and packages (typically only DBMS_DBFS_CONTENT and DBMS_DBFS_SFS) to users who might otherwise have the dbfs_role.

These explicit, direct grants are normal and to be expected, and can be provided as needed and on demand.

Create a SecureFile Store File Systems Example

As a dbfs_role enabled user (for example, foo or sfs_demo), do the following:

1. Creating SecureFile Store Stores (file systems) for access via the DBFS CAPI.

declare
begin
  dbms_dbfs_sfs.createFilesystem(
  store_name => 'FS1',   /* any arbitrary, user-unique name */
  tbl_name => 'T1', /* valid table name, to be created in the current schema */
  tbl_tbs => null, /* valid tablespace name to be used for the store table
                      and its dependent segments (indexes, lob, nested table).
                      Default NULL, i.e., using the default  tablespace Of the
                                            current schema */
  use_bf => false /* use basicfile lobs? false=>securefiles Default FALSE,
                                          that is, using SecureFiles */
);
  commit;
end;
/

2. Register these file systems with the DBFS CAPI as new stores managed by the SecureFile Store.

connect sfs_demo/*
declare
begin
/* Associate the SecureFile Store 'FS1' with the 'dbms_dbfs_sfs' provider */
   dbms_dbfs_content.registerStore(
   store_name    => 'FS1',       /* files ystem 'FS1', table "SFS_DEMO"."T1" */
   provider_name => 'anything',    /* ignored */
   provider_package => 'dbms_dbfs_sfs' /* for the SecureFile Store ref provider */
);
 commit;
end;
/

3. Mount the stores at suitable mount-points.

connect sfs_demo/*
declare
begin
   dbms_dbfs_content.mountStore(
       store_name    => 'FS1',   /* file system 'FS1', table "SFS_DEMO"."T1" */
       store_mount   => 'mnt1'   /* mount point */
);
    commit;
 end;
 /

4. Various listing functions:

connect sfs_demo/*
-- verify SecureFile Store tables and file systems
select * from table(dbms_dbfs_sfs.listTables);
select * from table(dbms_dbfs_sfs.listFilesystems);
 
-- verify ContentAPI Stores and mounts
select * from table(dbms_dbfs_content.listStores);
select * from table(dbms_dbfs_content.listMounts);
 
-- verify Store features
var fs1f number;
exec :fs1f := dbms_dbfs_content.getFeaturesByName('FS1');
select * from table(dbms_dbfs_content.decodeFeatures(:fs1f)); 
 
-- verify resource and property views
select * from dbfs_content;
select * from dbfs_content_properties;

The tables backing SecureFile Store file systems should never be accessed directly or even through the DBMS_DBFS_SFS package methods. The correct way to access the file systems is via the DBFS Content API, (DBMS_DBFS_CONTENT methods) for procedural operations, and through the resource/property views (dbfs_content and dbfs_content_properties) for SQL operations.

SecureFiles or BasicFiles?

SecureFiles are only available in releases 11gR1 and higher. They are not available in 10gR2.

Compatibility should be at least 11.1.0.0 to use SecureFiles.

Specify use_bf => false in DBMS_DBFS_SFS.CREATEFILESYSTEM to use SecureFiles.

Specify use_bf => true in DBMS_DBFS_SFS.CREATEFILESYSTEM to use BasicFiles.

Initialize SecureFile Store File Systems

Initialize and reinitialize a SecureFile Store file system store.

connect sfs_demo/*;
exec dbms_dbfs_content.initFS(store_name => 'FS1' /* file system 'FS1', table
                                                    "SFS_DEMO"."T1" */);

Drop SecureFile Store File Systems

1. Unmount the Stores.

connect sfs_demo/*;
declare
begin
    dbms_dbfs_content.unmountStore(
         store_name    => 'FS1',     /* file system 'FS1', table "SFS_DEMO"."T1" */
         store_mount   => ‘mnt1’    /* mount point */
);
   commit;
end;
/

2. Unregister the stores.

connect sfs_demo/*;
exec  dbms_dbfs_content.unregisterStore(store_name => 'FS1'  /* file system 
                                             'FS1', table "SFS_DEMO"."T1" */);
commit;

3. Drop the file system.

connect sfs_demo/*;
exec  dbms_dbfs_sfs.dropFilesystem(store_name => 'FS1' /* file system 'FS1', table 
                                                          SFS_DEMO"."T1" */);
commit;

Working with DBFS Content API

Assuming the above steps have been executed to set up the DBFS Content API, and to create and mount at least one SecureFile Store reference file system under the mount point /mnt1, you can create a new file and directory elements as follows:

connect foo/***
 
declare
   ret integer;
   b   blob;
   str varchar2(1000)  := '' || chr(10) ||
 
'#include <stdio.h>' || chr(10) ||
'' || chr(10) ||
'int main(int argc, char** argv)' || chr(10) ||
'{' || chr(10) ||
'    (void) printf("hello world\n");' || chr(10) ||
'    return 0;' || chr(10) ||
'}' || chr(10) ||
'';
 
    begin
        ret := dbms_fuse.fs_mkdir('/mnt1/src');
        ret := dbms_fuse.fs_creat('/mnt1/src/hello.c', content => b);
        dbms_lob.writeappend(b, length(str), utl_raw.cast_to_raw(str));
        commit;
    end;
    /
    show errors;
 
    -- verify newly created directory and file
    select pathname, pathtype, length(filedata),
        utl_raw.cast_to_varchar2(filedata)
        from dbfs_content
            where pathname like '/mnt1/src%'
            order by pathname;

The file system/store can be populated and accessed from PL/SQL with DBMS_DBFS_CONTENT. The file system/store can be accessed read-only from SQL using the dbfs_content and dbfs_content_properties views.

The file system/store can be populated and accessed via FUSE using regular file system APIs and UNIX utilities, or by the standalone dbfs_client tool (in environments where FUSE is either unavailable or not set up).