DBSecWorx: UTL

Oracle UTL_FILE Built-In Package Versions 7.3 - 21c

Security Advisory

This package is used to read and write operating system files in text or binary mode providing a restricted version of operating system stream file I/O according to the Oracle on-line docs: That is a partial truth.

The full truth is that UTL_FILE allows access to any mounted file system to which it is granted access either through the creation of DIRECTORY objects or, for older versions of Oracle., inserting a UTL_FILE_DIR parameter in the startup initialization parameter file (SPFILE).

Recommended Security Rules

NEVER

Let any user or schema without documented justification for escalated privileges to access this package by revoking EXECUTE from PUBLIC

WITH GREAT CARE

Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified users

CAUTIONS

Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES.

How Oracle Works

Reading and writing files All Oracle databases, for all versions, have multiple built-in tools that are capable of reading and writing from file systems: One of those tools is UTL_FILE. At the bottom of this page are a number of demos that demonstrate how the tool is intended to be used.

If UTL_FILE is allowed, through the creation of DIRECTORY objects or entries in the SPFILE, to access locations such as $ORACLE_HOME/rdbms/admin, audit and diag and redo log directories, this tools can be used to create serious compromises.

The first and best thing to do is remove the default grant of EXECUTE to PUBLIC.

Then, to protect database assets from privileged users, be sure that no DIRECTORY objects or utl_file_dir entries in the SPFILE allow access to $GRID_BASE, $GRID_HOME, $ORACLE_BASE, $ORACLE_HOME, or file systems that contain sensitive information.

UTL_FILE Package Information

AUTHID CURRENT_USER

Data Types -- file handle used in the block declaration section

TYPE file_type IS RECORD ( id BINARY_INTEGER, datatype BINARY_INTEGER, byte_mode BOOLEAN);

Dependencies

DBMS_ADVISOR	DBMS_SCHEDULER	KUPM$MCP
DBMS_AW_EXP	DBMS_SQLTCB_INTERNAL	KUPW$WORKER
DBMS_CLOBUTIL	DBMS_STATS_INTERNAL	LTUTIL
DBMS_CMP_INT	DBMS_STREAMS_AUTH	MGMT_DB_LL_METRICS
DBMS_CUBE	DBMS_STREAMS_MT	ORDX_FILE_SOURCE
DBMS_DATAPUMP_UTL	DBMS_STREAMS_SM	ORD_DATASOURCE_PKG
DBMS_DISRUPT	DBMS_SWRF_INTERNAL	ORD_DICOM_ADMIN
DBMS_FILE_GROUP	DBMS_WORKLOAD_REPLAY_I	PLITBLM
DBMS_FILE_GROUP_UTL_INVOK	DBMS_WRR_INTERNAL	SDO_NDM_TRAFFIC
DBMS_ISCHED	DBMS_XDB_ADMIN	SDO_NET
DBMS_ISCHED_REMOTE_ACCESS	DBMS_XMLPARSER	SDO_NET_PARTITION
DBMS_LOGMNR_D	DBMS_XSLPROCESSOR	SDO_ROUTER_PARTITION
DBMS_LOGMNR_INTERNAL	DBMS_XSTREAM_AUTH_IVK	SDO_ROUTER_TIMEZONE
DBMS_METADATA	JVMFCB	SDO_WFS_LOCK
DBMS_PREUP	KU$_DPLOAD	SDO_WFS_PROCESS
DBMS_QOPATCH	KUPF$FILE	UTL_FIL_LIB
DBMS_REGISTRY_SYS

Documented Yes

Exceptions

Error Code	Reason
ORA-29280	Specified path does not exist or is not visible to Oracle
ORA-29281	The open_mode parameter in FOPEN is invalid
ORA-29282	File handle does not exist
ORA-29283	File could not be opened or operated on as requested
ORA-29284	Unable to read file
ORA-29285	Unable to write to file
ORA-29286	Unhandled internal error in the UTL_FILE package
ORA-29287	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767
ORA-29288	A file with the specified name does not exist in the path
ORA-29289	Access to the file has been denied by the operating system
ORA-29290	The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; it should be greater than 0 and less than the total number of bytes in the file
ORA-29291	Unable to delete file
ORA-29292	Unable to rename file
ORA-29298	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE
FILE_OPEN	File is already open

First Available 7.3.4

Intialization Parameters utl_file_dir=<directory_path_and_name>

-- it is recommended that these not be used for but, instead, create a directory object

      utl_file_dir=c:\oraload

      utl_file_dir=c:\temp

      utl_file_dir=*

Open Modes

Syntax	Description
A	Append Text
AB	Append Byte Mode
R	Read Text
RB	Read Byte Mode
W	Write Text
WB	Write Byte Mode

Security Model Owned by SYS with EXECUTE granted to PUBLIC, ORACLE_OCM, ORDPLUGINS, ORDSYS, and WMSYS.

O/S permissions are those of the user 'Oracle' ... not the schema owner or connected user.

Source {ORACLE_HOME}/rdbms/admin/utlfile.sql

Subprograms

FCLOSE

Close named file utl_file.fclose(file IN OUT file_type);

See FOPEN Demo Below

FCLOSE_ALL

Close all open files utl_file.fclose_all;

set serveroutput on

      

      DECLARE

       vInHandle  utl_file.file_type;

       vOutHandle utl_file.file_type;
      

      BEGIN

        vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');

        vOutHandle := utl_file.fopen('ORALOAD', 'out.txt', 'W');

        

        IF utl_file.is_open(vInHandle) THEN

          utl_file.fclose_all;

          dbms_output.put_line('Closed All');

        END IF;

      END;

      /

FCOPY

Copies a contiguous portion of a file to a newly created file utl_file.fcopy( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename IN VARCHAR2, start_line IN BINARY_INTEGER DEFAULT 1, end_line IN BINARY_INTEGER DEFAULT NULL);

BEGIN

        utl_file.fcopy('ORALOAD', 'test.txt', 'ORALOAD', 'dump.txt');

      END;

      /

FFLUSH

Physically writes pending data to the file identified by the file handle utl_file.fflush(file IN file_type);

See WRITE Demo Below

FGETATTR

Reads and returns the attributes of a disk file utl_file.fgetattr( location IN VARCHAR2, filename IN VARCHAR2, fexists OUT BOOLEAN, file_length OUT NUMBER, block_size OUT BINARY_INTEGER);

set serveroutput on

      

      DECLARE

       ex    BOOLEAN;

       flen  NUMBER;

       bsize NUMBER;

      BEGIN

        utl_file.fgetattr('ORALOAD', 'test.txt', ex, flen, bsize);

      

        IF ex THEN

          dbms_output.put_line('File Exists');

        ELSE

          dbms_output.put_line('File Does Not Exist');

        END IF;

        dbms_output.put_line('File Length: ' || TO_CHAR(flen));

        dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));

      END fgetattr;

        /

FGETPOS

Returns the current relative offset position within a file, in bytes utl_file.fgetpos(file IN file_type) RETURN BINARY_INTEGER;

See READ_WRITE Demo Below

FOPEN

Open a file for read operations utl_file.fopen( file_location IN VARCHAR2, file_name IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT NULL) RETURN file_type;

DECLARE

       vInHandle utl_file.file_type;

       vNewLine  VARCHAR2(250);

      BEGIN

        vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');

        LOOP

          BEGIN

            utl_file.get_line(vInHandle, vNewLine);

            dbms_output.put_line(vNewLine);

          EXCEPTION

            WHEN OTHERS THEN

              EXIT;

          END;

        END LOOP;

        utl_file.fclose(vInHandle);

      END fopen;

      /

Open A File For Write Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'W')

Open A File For Read Byte Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'RB')

Open A File For Write Byte Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'WB')

Open A File For Append Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'A')

Open A File For Append Byte Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'AB')

FOPEN_NCHAR

Open a file for multibyte characters

Note: since NCHAR contains mutibyte character, it is recommended that the max_linesize be less than 6400 utl_file.fremove( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT NULL) RETURN file_type;

See FOPEN Demo Above

FREMOVE

Delete a file in the file system utl_file.fremove( location IN VARCHAR2, filename IN VARCHAR2);

-- dump.txt is created in the FCOPY demo

BEGIN

        utl_file.fremove('ORALOAD', 'dump.txt');

      END fremove;

      /

FRENAME

Rename a file system file utl_file.frename ( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename IN VARCHAR2, overwrite IN BOOLEAN DEFAULT FALSE);

BEGIN

        utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt',TRUE);

      END frename;

      /

FSEEK

Adjusts the file pointer forward or backward within the file by the number of bytes specified utl_file.fseek( file IN OUT file_type, absolute_offset IN BINARY_INTEGER DEFAULT NULL, relative_offset IN BINARY_INTEGER DEFAULT NULL);

See Read-Write Demo Below

GET_LINE

Read a Line from a file utl_file.get_line( file IN FILE_TYPE, buffer OUT VARCHAR2, len IN BINARY_INTEGER DEFAULT NULL);

See Read Demos Below

GETLINE_NCHAR

Read a line from a file containing multi-byte characters utl_file.getline_nchar( file IN FILE_TYPE, buffer OUT NVARCHAR2, len IN BINARY_INTEGER DEFAULT NULL);

See Read Demos Below

GET_RAW

Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read utl_file.get_raw( file IN file_type, buffer OUT NOCOPY RAW, len IN BINARY_INTEGER DEFAULT NULL);

See UTL_MAIL Demo Linked At Page Bottom

IS_OPEN

Returns True If A File Handle Is Open: Otherwise False utl_file.is_open(file IN FILE_TYPE) RETURN BOOLEAN;

See FCLOSE_ALL Demo Above

NEW_LINE

Writes one or more operating system-specific line terminators to a file utl_file.new_line( file IN FILE_TYPE, lines IN NATURAL := 1);

See Read Demo Below

PUT

Write a string to a file utl_file.put( file IN FILE_TYPE, buffer IN VARCHAR2);

See WRITE Demo Below

PUTF

A PUT procedure with formatting utl_file.putf( file IN file_type, format IN VARCHAR2, arg1 IN VARCHAR2 DEFAULT NULL, arg2 IN VARCHAR2 DEFAULT NULL, arg3 IN VARCHAR2 DEFAULT NULL, arg4 IN VARCHAR2 DEFAULT NULL, arg5 IN VARCHAR2 DEFAULT NULL);

See WRITE Demo Below

PUTF_NCHAR

Writes a Unicode string to a file utl_file.putf_nchar( file IN file_type, format IN NVARCHAR2, arg1 IN NVARCHAR2 DEFAULT NULL, arg2 IN NVARCHAR2 DEFAULT NULL, arg3 IN NVARCHAR2 DEFAULT NULL, arg4 IN NVARCHAR2 DEFAULT NULL, arg5 IN NVARCHAR2 DEFAULT NULL);

TBD

PUT_LINE

Writes a line to a file. Appends an operating system-specific line terminator utl_file.put_line( file IN FILE_TYPE, buffer IN VARCHAR2, autoflush IN BOOLEAN DEFAULT FALSE);

See READ-WRITE Demo Below

PUT_LINE_NCHAR

Writes a Unicode line to a file utl_file.put_line_nchar( file IN file_type, buffer IN NVARCHAR2);

TBD

PUT_NCHAR

Writes a Unicode string to a file utl_file.put_nchar( file IN file_type, buffer IN NVARCHAR2);

TBD

PUT_RAW

Accepts as input a RAW data value and writes the value to the output buffer utl_file.put_raw( file IN file_type, buffer IN RAW, autoflush IN BOOLEAN DEFAULT FALSE);

See EXTRACT_BLOB Demo Below

UTL_FILE Demos

O/S Directory Creation -- windows
mkdir c:\oraload -- linux su - mkdir /stage chown -R oracle:dba /stage

Oracle Directory Creation -- windows
conn sys@pdbdev as sysdba CREATE OR REPLACE DIRECTORY oraload AS 'c:\oraload\'; GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS;

-- linux
conn sys@pdbdev as sysdba CREATE OR REPLACE DIRECTORY oraload AS '/stage'; GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS;

File To Create: test.txt Foote,Richard
Forbrich,Hans
Kyte, Tom
Morgan, Daniel
Nanda,Arup

Read Demo conn uwclass/uwclass@pdbdev CREATE TABLE test ( fld1 VARCHAR2(20), fld2 VARCHAR2(20)); CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS vSFile utl_file.file_type; vNewLine VARCHAR2(200); BEGIN vSFile := utl_file.fopen('ORALOAD', file_name,'r'); IF utl_file.is_open(vSFile) THEN LOOP BEGIN utl_file.get_line(vSFile, vNewLine); IF vNewLine IS NULL THEN EXIT; END IF; INSERT INTO test (fld1, fld2) VALUES (vNewLine, file_name); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; COMMIT; END IF; utl_file.fclose(vSFile); utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE); EXCEPTION WHEN utl_file.invalid_mode THEN RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter'); WHEN utl_file.invalid_path THEN RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location'); WHEN utl_file.invalid_filehandle THEN RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle'); WHEN utl_file.invalid_operation THEN RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation'); WHEN utl_file.read_error THEN RAISE_APPLICATION_ERROR (-20055, 'Read Error'); WHEN utl_file.internal_error THEN RAISE_APPLICATION_ERROR (-20057, 'Internal Error'); WHEN utl_file.charsetmismatch THEN RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent'); WHEN utl_file.file_open THEN RAISE_APPLICATION_ERROR (-20059, 'File Already Opened'); WHEN utl_file.invalid_maxlinesize THEN RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K'); WHEN utl_file.invalid_filename THEN RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name'); WHEN utl_file.access_denied THEN RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By'); WHEN utl_file.invalid_offset THEN RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0'); WHEN others THEN RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'); END read_demo; /

Read-Write Demo conn uwclass/uwclass@pdbdev CREATE OR REPLACE PROCEDURE rw_demo IS InFile utl_file.file_type; OutFile utl_file.file_type; vNewLine VARCHAR2(4000); i PLS_INTEGER; j PLS_INTEGER := 0; SeekFlag BOOLEAN := TRUE; BEGIN -- open a file to read InFile := utl_file.fopen('ORALOAD', 'in.txt','r'); -- open a file to write OutFile := utl_file.fopen('ORALOAD', 'out.txt', 'w'); -- if the file to read was successfully opened IF utl_file.is_open(InFile) THEN -- loop through each line in the file LOOP BEGIN utl_file.get_line(InFile, vNewLine); i := utl_file.fgetpos(InFile); dbms_output.put_line(TO_CHAR(i)); utl_file.put_line(OutFile, vNewLine, FALSE); utl_file.fflush(OutFile); IF SeekFlag = TRUE THEN utl_file.fseek(InFile, NULL, -30); SeekFlag := FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; COMMIT; END IF; utl_file.fclose(InFile); utl_file.fclose(OutFile); EXCEPTION WHEN others THEN RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'); END rw_demo; /

Write Demo

This demo writes out a Korn Shell script to run SQL*Loader conn uwclass/uwclass@pdbdev CREATE OR REPLACE PROCEDURE create_cmd_file AS CURSOR sll_cur IS SELECT loadname, loadfilename, loadfiledate FROM sqlldrlog WHERE run_status = 'B' ORDER BY sequenceno; sll_rec sll_cur%ROWTYPE; DirLoc VARCHAR2(30) := 'ORALOAD'; LFileName sqlldrlog.loadfilename%TYPE; LFileDate sqlldrlog.loadfiledate%TYPE; ctl_file VARCHAR2(500); dat_file VARCHAR2(500); log_file VARCHAR2(500); bad_file VARCHAR2(500); Emsg VARCHAR2(90) := 'Load CREATE_CMD_FILE Failed with ERROR '; vSubject := 'SQL Loader Failure Notification'; DayFile utl_file.file_type; LogFile utl_file.file_type; BEGIN DayFile := utl_file.fopen(DirLoc, 'execsqlldr.ksh','W'); LogFile := utl_file.fopen(DirLoc, 'log_list.dat','W'); OPEN sll_cur; LOOP FETCH sll_cur INTO sll_rec; EXIT WHEN sll_cur%NOTFOUND; ctl_file := '/data/cload/ctl/'|| LOWER(sll_rec.loadname) || '.ctl \'; dat_file := '/data/cload/data/' || sll_rec.loadfilename || ' \'; log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log \'; bad_file := '/data/cload/bad/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.bad'; utl_file.putf(dayfile, 'sqlldr userid=%s\ncontrol=%s\ndata=%s\nlog=%s\nbad=%s\n', '/ \', ctl_file, dat_file, log_file, bad_file); log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log'; utl_file.putf(logfile,'%s\n',log_file); END LOOP; utl_file.fclose(DayFile); utl_file.fclose(LogFile); EXCEPTION WHEN utl_file.invalid_mode THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20051, Invalid Option'; sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage); WHEN utl_file.invalid_path THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20052, Invalid Path'; sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage); WHEN utl_file.invalid_filehandle THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20053, Invalid Filehandle'; sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage); WHEN utl_file.invalid_operation THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20054, Invalid Operation'; sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage); WHEN utl_file.read_error THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20055, Read Error'; sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage); WHEN utl_file.write_error THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20056, Write Error'; sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage); WHEN utl_file.internal_error THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20057, Internal Error'; WHEN OTHERS THEN vErrMsg := SQLERRM; vMessage := Emsg || vErrMsg; sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage); END create_cmd_file; /

Extract BLOB Demo conn uwclass/uwclass@pdbdev CREATE OR REPLACE PROCEDURE blob2file( pdname VARCHAR2, psname VARCHAR2, pfname VARCHAR2) AUTHID DEFINER IS vblob BLOB; vstart NUMBER := 1; bytelen NUMBER := 32000; len NUMBER; my_vr RAW(32000); x NUMBER; l_output utl_file.file_type; BEGIN -- define output directory l_output := utl_file.fopen('ORALOAD', pfname, 'WB', 32760); -- get length of blob SELECT dbms_lob.getlength(iblob) INTO len FROM pdm WHERE dname = pdname AND sname = psname AND fname = pfname; -- save blob length x := len; -- select blob into variable SELECT iblob INTO vblob FROM pdm WHERE dname = pdname AND sname = psname AND fname = pfname; -- if small enough for a single write IF len < 32760 THEN utl_file.put_raw(l_output,vblob); utl_file.fflush(l_output); ELSE -- write in pieces vstart := 1; WHILE vstart < len LOOP dbms_lob.read(vblob,bytelen,vstart,my_vr); utl_file.put_raw(l_output,my_vr); utl_file.fflush(l_output); -- set the start position for the next cut vstart := vstart + bytelen; -- set the end position if less than 32000 bytes x := x - bytelen; IF x < 32000 THEN bytelen := x; END IF; END LOOP; END IF; utl_file.fclose(l_output); END blob2file; /