8 DBFS SecureFiles Store

Creating and Registering a New SecureFiles Store

Procedure CREATEFILESYSTEM() creates a new SecureFiles 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 BasicFiles LOB is used, otherwise a SecureFiles 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). The method createStore() is a wrapper around createFilesystem().

See Oracle Database PL/SQL Packages and Types Reference, for details on using DBMS_DBFS_SFS.

Initializing or Re-initializing a SecureFiles Store

Procedure INITFS() truncates and re-initializes the table associated with the SecureFiles Store store_name. The procedure executes like a DDL, auto-commiting before and after its execution.

See Oracle Database PL/SQL Packages and Types Reference for details of the DBMS_DBFS_SFS methods.

Unregister and Drop A SecureFiles 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-commiting before and after its execution.

See Oracle Database PL/SQL Packages and Types Reference, for details of the DBMS_DBFS_SFS methods.

Permissions Management

To manage permissions:

1. Create or pick DBFS Content API target users.

   Assume that you use the DBFS Content API as database user/password as user1/password1, user2/password2, and sfs_demo/password3. At minimum, these database users must have the CREATE SESSION, RESOURCE, and CREATE VIEW privileges.

   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.

   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.

   connect / as sysdba
   grant dbfs_role to user1;
   grant dbfs_role to user2;
   grant dbfs_role to sfs_demo;
   

This sets up the DBFS Content API for any database user who has the dbfs_role.

Because of the way roles, access control, and definer and 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.

Creating a SecureFiles File System Store

To create a SecureFiles File System Store:

1. Create the necessary stores for access using the DBFS CAPI:

   declare
     begin
       dbms_dbfs_sfs.createFilesystem(
         store_name => 'FS1',
         tbl_name => 'T1',
         tbl_tbs => null,
         use_bf => false 
       );
       commit;
     end;
   /
   

   were:

   • store_name is any arbitrary, user-unique name.

   • tbl_name is a valid table name, created in the current schema.

   • tbl_tbs is a valid tablespace name used for the store table and its dependent segments, such as indexes, lob, nested tables. The default is NULL, and specifies a tablespace of the current schema.

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

   connect sfs_demo/******
   declare
     begin
       dbms_dbfs_content.registerStore(
         store_name    => 'FS1',
         provider_name => 'anything',
         provider_package => 'dbms_dbfs_sfs'
       );
       commit;
     end;
   /
   

   This operation associates the SecureFiles Store FS1 with the dbms_dbfs_sfs provider.

   where:

   • store_name is SecureFiles Store FS1 that uses table SFS_DEMO.T1.

   • provider_name is ignored.

   • provider_package is dbms_dbfs_sfs, for SecureFiles Store reference provider.

3. Mount the stores at suitable mount-points.

   connect sfs_demo/******
   declare
     begin
       dbms_dbfs_content.mountStore(
         store_name    => 'FS1',
         store_mount   => 'mnt1'
       );
       commit;
     end;
   /
   

   where:

   • store_name is SecureFiles Store FS1 that uses table SFS_DEMO.T1.

   • store_mount is the mount point.

4. [Optional] To see the results of the preceding steps, you can use one of the following statements.

   • verify SecureFiles 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 SecureFiles 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;
     

You should never directly access tables that hold data for SecureFiles Store file systems, even through the DBMS_DBFS_SFS package methods. The correct way to access the file systems is using the DBFS Content API, (DBMS_DBFS_CONTENT methods) for procedural operations, and through the resource and property views (dbfs_content and dbfs_content_properties) for SQL operations.

Comparing SecureFiles LOBs to BasicFiles LOBs

SecureFiles LOBs are only available in Oracle Database 11g Release 1 and higher. They are not available in earlier releases.

Compatibility must be at least 11.1.0.0 to use SecureFiles LOBs.

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

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

Initializing SecureFiles Store File Systems

Initialize and re-initialize a SecureFiles Store file system store. The following example uses file system FS1, and table "SFS_DEMO"."T1".

connect sfs_demo/******;
exec dbms_dbfs_content.initFS(store_name => 'FS1');

Drop SecureFiles Store File Systems

To drop a SecureFiles Store file system

1. Unmount the stores.

   connect sfs_demo/******;
   declare
     begin
       dbms_dbfs_content.unmountStore(
         store_name    => 'FS1',
         store_mount   => ‘mnt1’
       );
       commit;
   end;
   /
   

   where:

   • store_name is SecureFiles Store FS1 that uses table SFS_DEMO.T1.

   • store_mount is the mount point.

2. Unregister the stores.

   connect sfs_demo/******;
   exec dbms_dbfs_content.unregisterStore(store_name => 'FS1');
   commit;
   

   where store_name is SecureFiles Store FS1 that uses table SFS_DEMO.T1.

3. Drop the file system.

   connect sfs_demo/******;
   exec dbms_dbfs_sfs.dropFilesystem(store_name => 'FS1');
   commit;
   

   where store_name is SecureFiles Store FS1 that uses table SFS_DEMO.T1.

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 SecureFiles Store reference file system under the mount point /mnt1, you can create a new file and directory elements as demonstrated in Example 8-1.

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 can be populated and accessed from PL/SQL with DBMS_DBFS_CONTENT. The file system can be accessed read-only from SQL using the dbfs_content and dbfs_content_properties views.

The file system can be populated and accessed through 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).