Skip Headers
Oracle® Database SecureFiles and Large Objects Developer's Guide
12c Release 1 (12.1)

E17605-10
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 DBFS SecureFiles Store

This chapter describes the DBFS SecureFiles Store and how to set up and use it.

This chapter contains the following topics:

Setting Up a SecureFiles Store

This section shows how to set up a SecureFiles Store.

This section contains these topics:

Managing Permissions

You must use a regular database user for all operational access to the Content API and stores. Do not use SYS or SYSTEM users or SYSDBA or SYSOPER system privileges. For better security and separation of duty, only allow specific trusted users the ability to manage DBFS Content API operations.

You must grant each user the DBFS_ROLE role. Otherwise, the user is not authorized to use the DBFS Content API. A user with suitable administrative privileges (or SYSDBA) can grant the role to additional users as needed.

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 (typically execute permissions) on DBFS Content API types (SQL types with the DBMS_DBFS_CONTENT_ xxx prefix) and packages (typically only DBMS_DBFS_CONTENT and DBMS_DBFS_SFS) to users who might otherwise have the DBFS_ROLE role.

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

To manage permissions:

  1. Create or determine DBFS Content API target users.

    This example uses this user and password: sfs_demo/password

    At minimum, this database user must have the CREATE SESSION, CREATE RESOURCE, and CREATE VIEW privileges.

  2. Grant the DBFS_ROLE role to the user.

    CONNECT / as sysdba
    GRANT dbfs_role TO sfs_demo;
    

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

Creating a SecureFiles File System Store

This section describes how to create a SecureFiles file system store.

The CREATEFILESYSTEM procedure auto-commits before and after its execution (like a DDL). The method CREATESTORE is a wrapper around CREATEFILESYSTEM.

See Oracle Database PL/SQL Packages and Types Reference for DBMS_DBFS_SFS syntax details.

To create a SecureFiles File System Store:

  1. Create the necessary stores to be accessed using the DBFS Content API:

    DECLARE
      BEGIN
        DBMS_DBFS_SFS.CREATEFILESYSTEM(
          store_name => 'FS1',
          tbl_name => 'T1',
          tbl_tbs => null,
          use_bf => false 
        );
        COMMIT;
      END;
    /
    

    where:

    • 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, LOBs, or nested tables. The default is NULL and specifies a tablespace of the current schema.

    • use_bf specifies that BasicFiles LOBs should be used, if true, or not used, if false.

  2. Register this store with the DBFS Content API as a new store managed by the SecureFiles Store provider.

    CONNECT sfs_demo
    Enter password:password
    DECLARE
      BEGIN
        DBMS_DBFS_CONTENT.REGISTERSTORE(
          store_name    => 'FS1',
          provider_name => 'anything',
          provider_package => 'dbms_dbfs_sfs'
        );
        COMMIT;
      END;
    /
    

    where:

    • store_name is SecureFiles Store FS1, which uses table SFS_DEMO.T1.

    • provider_name is ignored.

    • provider_package is DBMS_DBFS_SFS, for SecureFiles Store reference provider.

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

  3. Mount the store at suitable a mount-point.

    CONNECT sfs_demo
    Enter password: password
    DECLARE
      BEGIN
        DBMS_DBFS_CONTENT.MOUNTSTORE(
          store_name    => 'FS1',
          store_mount   => 'mnt1'
        );
        COMMIT;
      END;
    /
    

    where:

    • store_name is SecureFiles Store FS1, which uses table SFS_DEMO.T1.

    • store_mount is the mount point.

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

    • To verify SecureFiles Store tables and file systems:

      SELECT * FROM TABLE(DBMS_DBFS_SFS.LISTTABLES);
      SELECT * FROM TABLE(DBMS_DBFS_SFS.LISTFILESYSTEMS);
      
    • To verify ContentAPI Stores and mounts:

      SELECT * FROM TABLE(DBMS_DBFS_CONTENT.LISTSTORES);
      SELECT * FROM TABLE(DBMS_DBFS_CONTENT.LISTMOUNTS);
       
      
    • To verify SecureFiles Store features:

      var fs1f number;
      exec :fs1f := dbms_dbfs_content.getFeaturesByName('FS1');
      select * from table(dbms_dbfs_content.decodeFeatures(:fs1f)); 
      
    • To verify resource and property views:

      SELECT * FROM DBFS_CONTENT;
      SELECT * FROM DBFS_CONTENT_PROPERTIES;
      

You should never directly access tables that hold data for a SecureFiles Store file systems, even through the DBMS_DBFS_SFS package methods. The correct way to access the file systems is as follows:

  • For procedural operations: Use the DBFS Content API (DBMS_DBFS_CONTENT methods).

  • For SQL operations: Use the resource and property views (DBFS_CONTENT and DBFS_CONTENT_PROPERTIES).

Initializing SecureFiles Store File Systems

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

The following example uses file system FS1 and table SFS_DEMO.T1.

CONNECT sfs_demo;
Enter password: password
EXEC DBMS_DBFS_SFS.INITFS(store_name => 'FS1');

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.

You must use BasicFiles LOB storage for LOB storage in tablespaces that are not managed with Automatic Segment Space Management (ASSM).

Compatibility must be at least 11.1.0.0 to use SecureFiles LOBs.

Additionally, you need to specify the following in DBMS_DBFS_SFS.CREATEFILESYSTEM:

  • To use SecureFiles LOBs (the default), specify use_bf => false.

  • To use BasicFiles LOBs, specify use_bf => true.

Using a DBFS SecureFiles Store File System

This section describes how to use a SecureFiles Store file system.

This section covers these topics:

Working with DBFS Content API

Assuming the steps in "Setting Up a SecureFiles Store" have been executed the DBFS Content API permissions set, and at least one SecureFiles Store reference file system is created and mounted under the mount point /mnt1, you can create a new file and directory elements as demonstrated in Example 6-1.

Example 6-1 Working with DBFS Content API

CONNECT tjones
Enter password: password
 
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 also be populated and accessed using regular file system APIs and UNIX utilities when mounted using FUSE, or by the standalone dbfs_client tool (in environments where FUSE is either unavailable or not set up).

Dropping SecureFiles Store File Systems

Use the unmountStore method to drop SecureFiles Store file systems.

This method removes all stores referring to the file system from the metadata tables, and drops the underlying file system table. The procedure executes like a DDL, auto-committing before and after its execution.

To drop a SecureFiles Store file system

  1. Unmount the store.

    CONNECT sfs_demo
    Enter password: password
    DECLARE
      BEGIN
        DBMS_DBFS_CONTENT.UNMOUNTSTORE(
          store_name    => 'FS1',
          store_mount   => 'mntl';
        );
        COMMIT;
    END;
    /
    

    where:

    • store_name is SecureFiles Store FS1, which uses table SFS_DEMO.T1.

    • store_mount is the mount point.

  2. Unregister the stores.

    CONNECT sfs_demo
    Enter password: password
    EXEC DBMS_DBFS_CONTENT.UNREGISTERSTORE(store_name => 'FS1');
    COMMIT;
    

    where store_name is SecureFiles Store FS1, which 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, which uses table SFS_DEMO.T1.

About DBFS SecureFiles Store Package, DBMS_DBFS_SFS

The DBFS SecureFiles Store package (DBMS_DBFS_SFS) is a store provider for DBMS_DBFS_CONTENT that supports SecureFiles LOB storage for DBFS content. To use the DBMS_DBFS_SFS package, you must be granted the DBFS_ROLE role.

The SecureFiles Store provider is a default implementation of the DBFS Content API (and is a standard example of a store provider that conforms to the Provider SPI) that enables applications that already use LOBs as columns in their schema, to access the BLOB columns. This enables existing applications to easily add PL/SQL provider implementations and provide access through the DBFS Content API without changing their schemas or their business logic. Additionally, applications can read and write content that is stored in other (third party) stores through the standard DBFS Content API interface. See Chapter 9, "Creating Your Own DBFS Store" and Oracle Database PL/SQL Packages and Types Reference for more information about the Provider SPI defined in DBMS_DBFS_CONTENT_SPI.

In a SecureFiles Store, the underlying user data is stored in SecureFiles LOBs and metadata such as pathnames, IDs, and properties are stored as columns in relational tables. See "SecureFiles LOB Storage" for advanced features of SecureFiles LOBs.

See Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_DBFS_SFS package.