UTL_FILE_DIR is the database initialization parameter the Oracle Database uses to determine what operating system directories and files PL/SQL packages, functions, and procedures may read from or write to when using the standard UTL_FILE database package. The directories specified in the UTL_FILE_DIR parameter may be accessed by any database user, which can be a security issue. In Oracle 9iR2, Oracle released new functionality called “Directories” that provides a more secure and robust capability to access operating system directories and files. The advantages of using Directories over UTL_FILE_DIR are –
- Read and/or Write access to a Directory can be granted to individual database accounts or roles
- A Directory can be added without having to bounce the database
- Directory name is used in the UTL_FILE.FOPEN statement rather than the directory path, which allows changes to the directory path without modification to the PL/SQL source code
Securing UTL_FILE
The UTL_FILE database package is used to read from and write to operating system directories and files. By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter.
Oracle usually assumes that PUBLIC has execute permission on UTL_FILE, therefore, many Oracle product installations do not specifically grant execute permission on UTL_FILE to Oracle installed database accounts. Consequently, revoking execute permission on UTL_FILE from PUBLIC will result in errors in a number of standard Oracle database utilities and The Oracle E-Business Suite. Also, some Oracle products and third party products will grant execute on UTL_FILE to PUBLIC during the installation of the product.
We do not recommend revoking execute permission on UTL_FILE from PUBLIC in database instances running the Oracle E-Business Suite and other complex applications (i.e., SAP, Peoplesoft, Oracle Clinical, etc.) due to the possibility of encountering errors in the application and third party products. Only revoke execute permission from PUBLIC in database instances where the application, third party products, and all database management tools can be thoroughly tested. All Oracle delivered products must be tested since Oracle often assumes UTL_FILE is granted to PUBLIC and does not provide the necessary grants when any products are installed – this includes products like Enterprise Manager Grid Control and Apex.
Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.
Oracle E-Business Suite and UTL_FILE_DIR
The combination of UTL_FILE being granted to PUBLIC and UTL_FILE_DIR being publicly accessible creates a significant security issue for the Oracle E-Business Suite. The Oracle E-Business Suite uses UTL_FILE_DIR to read and write concurrent manager request temporary files. Also, UTL_FILE_DIR is extensively used by most organizations to access interface and conversion data files from PL/SQL interface programs.
In the Oracle E-Business Suite, UTL_FILE_DIR is usually set to include at least the directories specified in $APPLPTMP and $APPLTMP – in the default installation this will include at least “/usr/tmp”. Frequently, additional custom directories will be included for custom interfaces and other custom programs.
By accessing the APPLSYSPUB database account, an attacker can easy read and write interface data files. Depending on the exact configuration, implemented modules, and custom interfaces, this could allow access to sensitive information including social security numbers and credit card numbers.
Migrating From UTL_FILE_DIR
For Oracle E-Business Suite customers, migrating from UTL_FILE_DIR to Directories requires only minimal changes and may require no source code changes depending on the design of the interfaces and other custom programs. The steps are as follows -
- Identify where UTIL_FILE is used
- Create Directories
- Change FOPEN calls to use Directories
- Edit UTL_FILE_DIR to remove physical Directories
Step One – Identify Where UTIL_FILE Is Used
The most difficult issue is identifying the packages, functions, and procedures using the physical directories in UTL_FILE_DIR. The UTL_FILE_DIR physical directories are only directly referenced by the UTL_FILE.FOPEN function. The FOPEN specifies the operating system directory and file name to open. All subsequent read, write, and close function calls use the file handle returned by FOPEN.
The following SQL may assist in identifying uses of UTL_FILE_DIR in FOPEN statements –
SELECT * FROM dba_source
WHERE upper(text) like '%FOPEN%'
AND name like '%<custom prefix>%'
AND owner = 'APPS' If the calls to FOPEN are not in a common function and are not accessed through some other indirection, it should be fairly straightforward to find and change the necessary FOPEN references in any custom PL/SQL packages, functions, and procedures. If the physical directory paths are stored in a table or in a concurrent program definition, then no changes to the source code are required.
At this time, converting the standard the Oracle E-Business Suite directories ($APPLPTMP and $APPLTMP) is not recommended as this is not supported by Oracle. Theoretically it should work without any issues, however, the Oracle E-Business Suite references the directories in multiple places including the $APPLPTMP and $APPLTMP environmental variables, system profile options (e.g., “ECX: XSLT File Path”), and potentially in some configuration files.
Step Two – Create Directories
The following general steps are required to change the references from UTL_FILE_DIR to Directories. Please note that the directory name MUST always be in uppercase in all UTL_FILE.FOPEN statements, otherwise errors may be encountered
For each custom directory in UTL_FILE_DIR, execute the following SQL statements in each development, test, and production database instance –
CREATE OR REPLACE DIRECTORY <name> AS '<physical directory path>'; GRANT READ, WRITE ON DIRECTORY <name> TO APPS;
as an example –
CREATE OR REPLACE DIRECTORY TMP_DIR AS '/usr/tmp'; GRANT READ, WRITE ON DIRECTORY TMP_DIR TO APPS;
The directories “/usr/tmp” and “../comn/temp” and any other directories specified in $APPLPTMP and $APPLTMP should remain in UTL_FILE_DIR, since these directories are required by Oracle.
Step Three – Change FOPEN Calls to Use Directories
Once directories have been created the next step is to edit your code to use them. The process is straightforward. If a physical directory is specified in the UTL_FILE.FOPEN statement, change the hard-coded path to the Directory name.
As an example –
FILE_ID := UTL_FILE.FOPEN('/usr/tmp', 'dummy.txt', 'W'); change to –
FILE_ID := UTL_FILE.FOPEN('TMP_DIR', 'dummy.txt', 'W'); Two pointers to keep in mind:
- Always be sure to use the directory name in uppercase and it must be enclosed in single quotes
- If the physical directory is specified in a table or as a parameter in a Concurrent Program definition, then just specify the Directory name rather than a physical path – /usr/tmp becomes TMP_DIR
Step Four – Edit UTL_FILE_DIR to Remove Physical Directories
Remove all the custom physical directories from UTL_FILE_DIR. The standard Oracle directories of ‘/usr/tmp’, ‘../comn/temp’, etc. should not be removed. The database must be bounced for the change to take effect.
If you have questions, please contact us at info@integrigy.com
References
- Using CREATE DIRECTORY Instead of UTL_FILE_DIR init.ora Parameter”, Oracle Metalink, Metalink Note ID 196939.1 https://support.oracle.com/rs?type=doc&id=196939.1
