UTL_FILE: Understanding how it is used...... A practical approach

In my previous post I talked about Auditing UTL_FILE_DIR parameter file entry. If you haven't read it, you can check it out here.

In this post I will be focusing on how the UTL_FILE package is actually used to transfer files to the host operating system from the database. This post will be showing you exactly how dangerous the package can be in the hands of a malicious person.

Note that EXECUTE on the UTL_FILE package is granted to PUBLIC by default. What this means is that any user created in the database automatically has the right to use this package.

Enough of the stories. Now lets get straight to business


I will perform this exercise with steps for easy understanding. So lets go

What is needed for this exercise
a. Oracle Database 10g and above
b. DBA privilege


1. Create a directory object. Let us call the directory "utl_dir". in this post I will use the location 'C:\Users\05992\Favorites\Desktop\testing'

create or replace directory utl_dir
as 'C:\Users\05992\Favorites\Desktop\testing'

This is the only way oracle can recognise any directory in the operating system.
Directory objects are database object, and database object names are stored in UPPERCASE by default. Even if the ‘create directory’ command you issued had the directory name in lowercase, unless you put it within quotes, the directory name will be stored in UPPERCASE.

2. Confirm that directory object is created

select * from dba_directories

3. Grant read write on the utl_dir directory object to Public.

grant read, write on directory utl_dir to public;

Note in the real world sense it is best practice to grant object permissions to specific entities that require it.

Now that our directory object is ready and granted to all necessary parties, its time to write our plsql to write into the operating system.

-- PLSQL to open a file and write a line into the file then close the file
declare 
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen(
                'UTL_DIR'     -- File location
              , 'ITCASXP_test_file.txt' -- File name
              , 'w' -- Open mode: w = write. 
                  );
  utl_file.put(fhandle, 'This is a tutorial from ITCASXP'
                      || CHR(100));
  utl_file.fclose(fhandle);
exception
  when others then 
    dbms_output.put_line('ERROR: ' || SQLCODE 
                      || ' - ' || SQLERRM);
    raise;
end;
/

Notice that the file named ITCASXP_test_file.txt has been created in the location 'C:\Users\05992\Favorites\Desktop\testing'.

Also notice that the test 'This is a tutorial from ITCASXP' has been added to the text file.

Now you can understand why this package, if not well protected can be really dangerous if accessed by malicious persons.

I hope you enjoyed this little exercise.