Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Beginner
May 30, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

How can I write to OS files from Oracle?

How can I write to OS files from Oracle?

Oracle version 7.3 supports direct reading and writing of operating system files from within Oracle. In particular, the utl_file package enables one to do file I/O from within a pl/sql procedure (for that matter, even a plain SQL call can do file I/O via a trigger or a user-defined function written 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 parameter file for the instance (i.e., in the init[sid].ora file):

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

Protecting against security problems

Due to the way this file access is implemented, there are several security concerns. First, every Oracle user with access to the utl_file package can access every file in allof the utl_file_dir directories. While this is perhaps not a serious problem, the DBA and/or sys admin should set the appropriate owner/group permissions such that unauthorized persons can not find out file names at the OS level and use that knowledge to access the files via Oracle. However, another potential security problem is serious: Anyone who has OS-level write access to the utl_file_dir directories can modify or even delete any Oracle file owned by the "Oracle" user.

For example, suppose John Doe has a UNIX account "jdoe" and an oracle account "jd" where the "jd" oracle account has no DBA privileges but can execute utl_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.


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.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date