Question:
How can I write to OS files from Oracle?

Answer:
Oracle version 7.3 supports directreading and writing of operating system files from within Oracle.In particular, the utl_file package enables one to do file I/Ofrom within a pl/sql procedure (for that matter, even a plainSQL call can do file I/O via a trigger or a user-defined functionwritten in pl/sql).

Making directories accessible for Oracle file I/O

To enable Oracle file I/O on the files in particular directories, say “/orafiles/email” and”/orafiles/other_dir” for example, one needs to have the following in the parameterfile for the instance (i.e., in the init[sid].ora file):

utl_file_dir=/orafiles/emailutl_file_dir=/orafiles/other_dir

Refer to the documentation addenda for your OS for any platform peculiarities. Note that only files, notsubdirectories, in those specified directories are accessible (but see below).

Protecting against security problems

Due to the way this file access is implemented, there areseveral security concerns. First, every Oracle user with accessto the utl_file package can access every file in allof the utl_file_dirdirectories. While this is perhaps not a serious problem, the DBA and/orsys admin should set the appropriate owner/group permissions suchthat unauthorized persons can not find out file names at the OS leveland use that knowledge to access the files via Oracle. However,another potential security problem is serious: Anyone whohas OS-level write access to the utl_file_dir directoriescan modify or even delete any Oracle file owned by the “Oracle” user.

For example, supposeJohn Doe has a UNIX account “jdoe” and an oracle account “jd” wherethe “jd” oracle account has no DBA privileges but can executeutl_file package procedures. Further, suppose “jdoe” can write to the “/orafiles/other_dir” directory. There is nothing to prevent John from logging in as jdoe and making a symbolic link like:

  ln -s $ORACLE_HOME/dbs/tbs1.dbf /orafiles/other_dir/dummy

then logging into Oracle as jd and opening the “dummy” file for writing, thus overwriting crucial data files.

Recommendation

Give OS-level write access to the utl_file_dirs to “root” and “oracle” only (i.e. the prototypical sys admin and DBA). It may happen that other users need to be able to move files generated outside of Oracle into one of the utl_file_dir directories. In that case, write a program or script that will execute periodically for the “Oracle” user, and move only files that have been verified as regular files, not symbolic links.