|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
External LOBs (BFILEs), 5 of 41
This section introduces the
BFILE security model and associated SQL statements. The main SQL statements associated with
BFILE security are:
READsystem and object privileges on
DIRECTORY object is a system owned object. For more information on system owned objects, see Oracle9i SQL Reference. Oracle9i supports two new system privileges, which are granted only to DBA:
DIRECTORY-- for creating or altering the directory object creation
DIRECTORY-- for deleting the 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
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.
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:
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.
Refer to the Oracle9i SQL Reference for information about the following SQL DDL statements that create, replace, and drop directory objects:
Refer to the Oracle9i SQL Reference for information about the following SQL DML statements that provide security for
Catalog views are provided for DIRECTORY objects to enable users to view object names and corresponding paths and privileges. Supported views are:
This view describes all directories accessible to the user.
This view describes all directories specified for the entire database.
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
DIRECTORYshould not be mapped to physical directories that contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could corrupt the database or the server operating system.
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.
OPTIONclause when granting privileges to users.
DIRECTORYobjects 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
REPLACEcommand 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 shut down.
The only recourse left to PL/SQL users, for example, will be to either execute a program block that calls
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.
DIRECTORYobject using the
REVOKEstatement 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.
Oracle9i 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.
BFILEs, the value is stored in a server-side operating system file; in other words, external to the database. The
BFILE locator that refers to that file is stored in the row.
BFILE locator variable that is used in a
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.
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.
BFILElocator 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.
BFILE, you must set the
BFILEvalue before flushing the object to the database, thereby inserting a new row. In other words, you must call
OCIObjectNew() and before
INSERT or UPDATEa
BFILEwithout 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.
Before using SQL to insert or update a row with a
BFILE, the user must initialize the
BFILE to one of the following