BFILE Security

This section introduces the BFILE security model and associated SQL statements. The main SQL statements associated with BFILE security are:

• SQL DDL: CREATE and REPLACE or ALTER a DIRECTORY object

• SQL DML: GRANT and REVOKE the READ system and object privileges on DIRECTORY objects

Ownership and Privileges

The DIRECTORY object is a system owned object. For more information on system owned objects, see Oracle8i SQL Reference. Oracle8i supports two new system privileges, which are granted only to DBA:

• CREATE ANY DIRECTORY -- for creating or altering the directory object creation

• DROP ANY DIRECTORY -- for deleting the directory object

Read Permission on Directory Object

READ permission on the DIRECTORY object allows you to read files located under that directory. The creator of the DIRECTORY object automatically earns the READ privilege.

If you have been granted the READ permission with GRANT option, you may in turn grant this privilege to other users/roles and add them to your privilege domains.

Note: The READ permission is defined only on the DIRECTORY object, not on individual files. Hence there is no way to assign different privileges to files in the same directory.

The physical directory that it represents may or may not have the corresponding operating system privileges (read in this case) for the Oracle Server process.

It is the DBA's responsibility to ensure the following:

• That the physical directory exists

• Read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it

• The directory remains available, and read permission remains enabled, for the entire duration of file access by database users

The privilege just implies that as far as the Oracle Server is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL DBMS_LOB package and OCI APIs at the time of the actual file operations.

WARNING: Because CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges potentially expose the server filesystem to all database users, the DBA should be prudent in granting these privileges to normal database users to prevent security breach.

SQL DDL for BFILE Security

Refer to the Oracle8i SQL Reference for information about the following SQL DDL statements that create, replace, and drop directory objects:

• CREATE DIRECTORY

• DROP DIRECTORY

SQL DML for BFILE Security

Refer to the Oracle8i SQL Reference for information about the following SQL DML statements that provide security for BFILEs:

• GRANT (system privilege)

• GRANT (object privilege)

• REVOKE (system privilege)

• REVOKE (object privilege)

• AUDIT (new statements)

• AUDIT (schema objects)

Catalog Views on Directories

Catalog views are provided for DIRECTORY objects to enable users to view object names and corresponding paths and privileges. Supported views are:

• ALL_DIRECTORIES (OWNER, DIRECTORY_NAME, DIRECTORY_PATH)

  This view describes all directories accessible to the user.

• DBA_DIRECTORIES(OWNER, DIRECTORY_NAME, DIRECTORY_PATH)

  This view describes all directories specified for the entire database.

Guidelines for DIRECTORY Usage

The main goal of the DIRECTORY feature is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server filesystem. But to realize this goal, it is very important that the DBA follow these guidelines when using DIRECTORY objects:

• Do Not Map DIRECTORY to Directories of Data Files Etc. A DIRECTORY should not be mapped to physical directories which contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could potentially corrupt the database or the server operating system.

• Only DBA Should Have System Privileges. The system privileges such as CREATE ANY DIRECTORY (granted to the DBA initially) should be used carefully and not granted to other users indiscriminately. In most cases, only the database administrator should have these privileges.

• Use Caution When Granting DIRECTORY Object Privilege. Privileges on DIRECTORY objects should be granted to different users carefully. The same holds for the use of the WITH GRANT OPTION clause when granting privileges to users.

• Do not Drop or Replace DIRECTORY Objects When Database is in Operation. DIRECTORY objects should not be arbitrarily dropped or replaced when the database is in operation. If this were to happen, operations from all sessions on all files associated with this directory object will fail. Further, if a DROP or REPLACE command is executed before these files could be successfully closed, the references to these files will be lost in the programs, and system resources associated with these files will not be released until the session(s) is shutdown.

  The only recourse left to PL/SQL users, for example, will be to either execute a program block that calls DBMS_LOB.FILECLOSEALL() and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.

• Caution When Revoking User's Privilege on DIRECTORY Objects. Revoking a user's privilege on a DIRECTORY object using the REVOKE statement causes all subsequent operations on dependent files from the user's session to fail. Either you must re-acquire the privileges to close the file, or execute a FILECLOSEALL() in the session and restart the file operations.

In general, using DIRECTORY objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have READ privileges for the Oracle process.

DIRECTORY objects can be created with READ privileges that map to these physical directories, and specific database users granted access to these directories.

BFILEs in Multi-Threaded Server (MTS) Mode

Oracle8i does not support session migration for BFILEs in Multi-threaded Server (MTS) mode. This implies that operations on open BFILEs can persist beyond the end of a call to an MTS server.

In MTS, sessions involving BFILE operations will be bound to one shared server, they cannot migrate from one server to another. This restriction will be removed in the next release.

External LOB (BFILE) Locators

For BFILEs, the value is stored in a server-side operating system file; i.e., external to the database. The BFILE locator that refers to that file is stored in the row.

When Two Rows in a BFILE Table Refer to the Same File

If a BFILE locator variable that is used in a DBMS_LOB.FILEOPEN() (for example L1) is assigned to another locator variable, (for example L2), both L1 and L2 point to the same file. This means that two rows in a table with a BFILE column can refer to the same file or to two distinct files -- a fact that the canny developer might turn to advantage, but which could well be a pitfall for the unwary.

BFILE Locator Variable

A BFILE locator variable behaves like any other automatic variable. With respect to file operations, it behaves like a file descriptor available as part of the standard I/O library of most conventional programming languages. This implies that once you define and initialize a BFILE locator, and open the file pointed to by this locator, all subsequent operations until the closure of this file must be done from within the same program block using this locator or local copies of this locator.

Guidelines

• Open and Close a File From Same Program Block at Same Nesting Level. The BFILE locator variable can be used, just as any scalar, as a parameter to other procedures, member methods, or external function callouts. However, it is recommended that you open and close a file from the same program block at the same nesting level.

• Set the BFILE Value Before Flushing Object to Database. If the object contains a BFILE, you must set the BFILE value before flushing the object to the database, thereby inserting a new row. In other words, you must call OCILobFileSetName() after OCIObjectNew() and before OCIObjectFlush().

• Indicate Directory Alias and Filename Before INSERT or UPDATE of BFILE. It is an error to INSERT or UPDATE a BFILE without indicating a directory alias and filename.

  This rule also applies to users using an OCI bind variable for a BFILE in an insert/update statement. The OCI bind variable must be initialized with a directory alias and filename before issuing the insert or update statement.

• Initialize BFILE Before INSERT or UPDATE

  Note: OCISetAttr() does not allow the user to set a BFILE locator to NULL.

General Rule

Before using SQL to insert or update a row with a BFILE, the user must initialize the BFILE to one of the following:

• NULL (not possible if using an OCI bind variable)

• A directory alias and filename