Controlling Access to External Files

The OLAP DML contains three types of commands that read from and write to external files:

• File read commands that copy data between flat files and workspace objects.
• Import and export commands that copy workspace objects and their contents to files for transfer to another database instance.
• File input and output commands that read and execute DML commands from a file and redirect command output to a file.

These commands control access to files by using BFILE security. This database security mechanism creates a directory alias to represent a physical disk directory. Permissions are assigned to the alias, which control access to files within the associated physical directory.

You use PL/SQL statements to create a directory alias and grant permissions. The relevant syntax of these SQL statements is provided in this chapter.

Creating a Directory Alias

To create a directory alias, you must have CREATE ANY DIRECTORY system privileges.

Use a CREATE DIRECTORY statement to create a new directory alias, or a REPLACE DIRECTORY statement to redefine an existing directory alias, using the following PL/SQL syntax:

{CREATE | REPLACE | CREATE OR REPLACE} DIRECTORY alias AS 'pathname';

Where:

alias is the name of the directory alias.

pathname is the physical directory path.

Granting Access Rights to a Directory Alias

After you create a directory alias, grant users and groups access rights to the files contained in that directory, using the following PL/SQL syntax:

GRANT permission ON DIRECTORY alias TO {user | role | PUBLIC};

Where:

permission is one of the following:

READ for read-only access

WRITE for write-only access

ALL for read and write access

alias is the name of the directory alias.

user is a database user name. That user gets immediate access rights.

role is a database role. All users who have been granted that role get immediate access rights.

PUBLIC is all database users. All users gets immediate access rights.

Example: Creating and Using a Directory Alias

The following SQL commands create a directory alias named olapdemo to control access to a directory named /users/oracle/OraHome1/demo and grant read access to all users.

CREATE DIRECTORY olapdemo as '/users/oracle/OraHome1/demo';
GRANT READ ON DIRECTORY olapdemo TO PUBLIC;

Users access files located in /users/oracle/OraHome1/demo with DML commands such as this one:

funit = FILEOPEN('olapdemo/units.dat' READ)