UTL_FILE_DIR versus Directories

Table of contents

Preamble

One of the generic security best practices is to move your UTL_FILE_DIR implementation (deprecated) to Oracle Directories. Even if it looks huge from applicative standpoint, we will see in this small blog post that migration is easy and straightforward.

The added value of directories, and in my point of view the biggest one is not security oriented:

  • You can know granularly grant to whom you want to give read or write on your directories.
  • Improved flexibility as directories are dynamic and you do not need to stop/start your database to add a new one.

Directories are Oracle objects you also use when working with Data Pump (expdp / impdp).

Testing has been done on Oracle Linux Server release 6.3 using Oracle 11.2.0.3.

UTL_FILE_DIR

First I create a filesystem/mount point and give OS ownership to oracle Unix account running my database:

[root@server1 ~]# cd /tmp
[root@server1 tmp]# mkdir oracle
[root@server1 tmp]# chown oracle:dba oracle
[root@server1 tmp]# ll -d oracle
drwxr-xr-x 2 oracle dba 4096 Jan 29 14:20 oracle

Second I change utl_file_dir on my test instance using:

SQL> show parameter utl_file_dir
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string
 
SQL> ALTER SYSTEM SET utl_file_dir='/tmp/oracle' scope=spfile;
 
SYSTEM altered.

As it is static parameter you must bounce your database, then you should see:

SQL> show parameter utl_file_dir
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /tmp/oracle

I have used the small PL/SQL block:

DECLARE
  file1 UTL_FILE.FILE_TYPE;
BEGIN
  file1 := UTL_FILE.FOPEN('/tmp/oracle','test.txt','W');
  UTL_FILE.PUT_LINE(file1,'Test of UTL_FILE');
  UTL_FILE.FCLOSE(file1);
END;
/

When you execute it the expected file is there and you can display it:

[oracle@server1 ~]$ ll /tmp/oracle
total 4
-rw-r--r-- 1 oracle dba 17 Nov 29 12:53 test.txt
[oracle@server1 ~]$ cat /tmp/oracle/test.txt
Test of UTL_FILE

All is pretty simple and is what you most probably use since ages…

Directories

I have reverted utl_file_dir setting to its default value (NULL):

SQL> show parameter utl_file_dir
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string

Let’s display available default directories:

SQL> SET lines 200
SQL> col DIRECTORY_PATH FOR a60
SQL> SELECT * FROM dba_directories;
 
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SYS                            DATA_PUMP_DIR                  /u01/app/oracle/admin/orcl/dpdump/
SYS                            XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

Then I create a directory in same filesystem I used in previous chapter (i.e. /tmp/oracle which has still oracle Unix account privileges set) which I call TMP_ORACLE:

SQL> CREATE OR REPLACE DIRECTORY TMP_ORACLE AS '/tmp/oracle';
 
DIRECTORY created.
 
SQL> SELECT * FROM dba_directories;
 
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SYS                            DATA_PUMP_DIR                  /u01/app/oracle/admin/orcl/dpdump/
SYS                            XMLDIR                         /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
SYS                            TMP_ORACLE                     /tmp/oracle

The PL/SQL block used to work with directory is:

DECLARE
  file1 UTL_FILE.FILE_TYPE;
BEGIN
  file1 := UTL_FILE.FOPEN('TMP_ORACLE','test.txt','W');
  UTL_FILE.PUT_LINE(file1,'Test of DIRECTORY');
  UTL_FILE.FCLOSE(file1);
END;
/

As you see huge difference isn’t it ? The only, simple modification is directory name instead of filesystem full path…

Remark:
In real life you should grant read/write on corresponding directory to Oracle accounts that will use it (creator is granted read/write by default). Owner is always SYS account but you need CREATE ANY DIRECTORY to create a directory (no CREATE DIRECTORY privilege).

The file has been created as expected:

[root@server1 ~]# ll /tmp/oracle
total 4
-rw-r--r-- 1 oracle dba 17 Nov 29 12:53 test.txt
[root@server1 ~]# cat /tmp/oracle/test.txt
[oracle@server1 oracle]$ cat test.txt
Test of DIRECTORY
This entry was posted in Oracle and tagged . Bookmark the permalink.

2 thoughts on “UTL_FILE_DIR versus Directories

  1. If the UTL_file_dir valur is blank in my vparameter and init.ora does not define UTL, does that mean that my Oracle 11g installation is safe from this security risk

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>