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 |
AV says:
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
Yannick Jaquier says:
Yes !