Pre 9iR2 (9.2)

Each directory to be accessed by UTL_FILE must be listed (comma separated) in the UTL_FILE_DIR init.ora parameter. This cannot be done with ALTER SYSTEM (ORA-02095) - you have to bounce the database. The directory must be explicitly specified again in UTL_FILE.FOPEN. And the number of directories that can be listed is limited by the 255 character buffer size.

From 9iR2

Each directory to be accessed by UTL_FILE can be specified via the CREATE DIRECTORY command. And specified via this level of indirection in UTL_FILE.FOPEN.

It is the responsibility of the system and database administrators to implement appropriate file and directory security on the database host. UTL_FILE won't attempt to check for permission before executing an open/read/write/delete request. We expect that the operating system will deny the request where appropriate. UTL_FILE will blindly issue any action requested and look for success or failure return status from the operating system.

By default UTL_FILE will have no file access because it will have no directory access, until granted access by CREATE DIRECTORY by SYS or SYSTEM or a user with DBA privileges. Since access privileges are granted on a per directory basis, the DBA can control directory access by either:

1. creating separate directories for users with differing access requirements
2. use operating system utilities and features for controling read and write access by users

sqlplus /nolog

set echo on
spool crdir02.lis

!mkdir /tmp/public_access
CONNECT sys/change_on_install AS sysdba;

DROP USER crdir02 CASCADE;
CREATE USER crdir02 IDENTIFIED BY crdir02;
GRANT connect, resource TO crdir02;
GRANT public TO crdir02;

CREATE OR REPLACE DIRECTORY public_access AS '/tmp/public_access';
GRANT read, write ON DIRECTORY public_access TO public;

CONNECT crdir02/crdir02;

DECLARE
  f1 UTL_FILE.FILE_TYPE;
BEGIN
    f1 := UTL_FILE.FOPEN('PUBLIC_ACCESS','exists.dat','w');
    UTL_FILE.FCLOSE(f1);
END;
/