How can I write to OS files from Oracle?

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may