Re: UTL_FILE Package

On Thu, 3 Dec 1998 11:08:58 +0100, "Philippe Dansereau" <pdansereau_at_eic.ca> wrote:

>Hello,
>
> I'm trying to use the UTL_File package to create a bunch of batch files
>for SQL*Loader. Feurstein's book on PL/SQL says that accessible directories
>must be defined in the init.ora for the instance. Alas it does not define
>the format of those entries. Is there a PL/SQL guru in this news group?
>

I don't know if I'm a guru, but I do know the answer to your question.

From the Oracle8 Server Application Developers Guide

<quote>

Server Security

Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (INIT.ORA).

You specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter, as follows:

UTL_FILE_DIR = <directory name>

For example, if the initialization file for the instance contains the line

UTL_FILE_DIR = /usr/jsmith/my_app

then the directory /usr/jsmith/my_app is accessible to the FOPEN function. Note that a directory named /usr/jsmith/My_App would not be accessible on case-sensitive operating systems.

The parameter specification

UTL_FILE_DIR = * has a special meaning. This entry in effect turns off directory access checking, and makes any directory accessible to the UTL_FILE functions.

Warning: The ’*’ option should be used with great caution. For obvious security reasons, Oracle does not recommend that you use this option in production systems. Also, do not include ’.’ (the current directory for UNIX) in the accessible directories list.

Warning: To ensure security on file systems that allow symbolic links, users must not be allowed WRITE permission to directories accessible by PL/SQL file I./O functions. The symbolic links and PL/SQL file I/O could be used to circumvent normal operating system permission checking, and allow users read/write access to directories to which they would not otherwise have access.

File Ownership and Protections

On UNIX systems, a file created by the FOPEN function has as its owner the owner of the shadow process running the instance. In the normal case, this owner is oracle. Files created using FOPEN are always writable and readable using the UTL_FILE routines, but non-privileged users who need to read these files outside of PL/SQL might have to get their system administrator to give
them access.

Examples (UNIX-Specific)

If the parameter initialization file contains only

UTL_FILE_DIR=/appl/gl/log
UTL_FILE_DIR=/appl/gl/out

then the following file locations and filenames are valid:

FILE LOCATION       FILENAME
/appl/gl/log        L10324.log
/appl/gl/out        O10324.out

but the following file locations and filename are invalid:

FILE LOCATION FILENAME

/appl/gl/log/backup L10324.log # subdirectory
/APPL/gl/log        L10324.log # uppercase
/appl/gl/log        backup/L10324.log #dir in name
/usr/tmp            T10324.tmp # not in INIT.ORA

There are no user-level file permissions. All file locations specified by the UTL_FILE_DIR parameters are valid, for both reading and writing, for all users of the file I/O procedures. This can override operating system file permissions.

</quote>

hope this helps

chris.

>Thanks in advance
>

--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation