Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE_DIR (Oracle 10gR2/ Solaris 10)
UTL_FILE_DIR [message #661726] Wed, 29 March 2017 20:43 Go to next message
reym21
Messages: 201
Registered: February 2010
Location: Philippines
Senior Member

Gurus,

Good day.

As per Oracle documentation, UTL_FILE_DIR has no default value.
I just want to validate where would be user files, logs, etc. be saved
after granting his/her permission when I didn't create a specific directory
for UTL_FILE_DIR? See commands below:

SQL> grant <permissions> on SYS.UTIL_FILE_DIR to <user>;

Grant succeeded.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
utl_file_dir string

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------ ------------------------------ -----------------------------------
SYS WORK_DIR /ade/aime_stath10/oracle/work
SYS ORACLE_OCM_CONFIG_DIR /appl1/home/oracle/10.2/ccr/state
SYS DATAPUMP_DIR /VOL03/oradata/DATAPUMP_DIR/

As per my assumption, all said files were saved in his/her default OS
working home directory (e.g. /home/export/<username>). Am I correct?

Thanks in advance.
Re: UTL_FILE_DIR [message #661728 is a reply to message #661726] Wed, 29 March 2017 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25854
Registered: January 2009
Location: SoCal
Senior Member
You should NOT be using UTL_FILE_DIR now (V10 or higher) or at any time in the future.

Use 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

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
Re: UTL_FILE_DIR [message #661729 is a reply to message #661728] Wed, 29 March 2017 22:07 Go to previous messageGo to next message
reym21
Messages: 201
Registered: February 2010
Location: Philippines
Senior Member

Sir,

Thanks for enlighten me on this and for the reference.
But this have been implemented already by my colleagues in production environment.

Back to my question, on what particular directory said user is working with?

Thanks.
Re: UTL_FILE_DIR [message #661730 is a reply to message #661729] Wed, 29 March 2017 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 25854
Registered: January 2009
Location: SoCal
Senior Member
>Back to my question, on what particular directory said user is working with?
Realize that Oracle is OS agnostic.
Oracle know who USER (schemaname) is, but any number of OS user could login as same schema. How to decide which is correct OS home directory?

Unwilling or incapable to simply TEST & see the actual results directly & for yourself?

>But this have been implemented already by my colleagues in production environment.
Perhaps you & they need to stop doing things which have been deprecated & improved.
Re: UTL_FILE_DIR [message #661731 is a reply to message #661726] Thu, 30 March 2017 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

And feedback in your topics starting with your previous ones.

Re: UTL_FILE_DIR [message #661733 is a reply to message #661726] Thu, 30 March 2017 01:29 Go to previous messageGo to next message
John Watson
Messages: 7261
Registered: January 2010
Location: Global Village
Senior Member
You did this,
Quote:
SQL> grant <permissions> on SYS.UTIL_FILE_DIR to <user>;

Grant succeeded.
What object type is UTIL_FILE_DIR? Note that he name is not the same UTL_FILE_DIR.

It isn't a directory, because you can't grant privileges on a directory like that.
Re: UTL_FILE_DIR [message #661737 is a reply to message #661733] Thu, 30 March 2017 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lets clarify a few points:
1) the utl_file_dir parameter and oracle directory objects are only used by the utl_file package, nothing else.
2) The parameter and the directory objects are different ways of doing the same thing - controlling which o/s directories users can access.
3) Even if you have a directory object called utl_file_dir, it still has nothing to do with the parr ameter and you should probably rename it to avoid confusion.
4) There is no way, what so ever, to set either of those up to allow users to write to their own directories by default.
5) The DB itself is running on the server as the user that installed it (normally oracle) - so it has the directory privs of that user, not the user who's currently connected via sqlplus (or whatever else).
Re: UTL_FILE_DIR [message #661739 is a reply to message #661737] Thu, 30 March 2017 03:23 Go to previous messageGo to next message
reym21
Messages: 201
Registered: February 2010
Location: Philippines
Senior Member

Gurus (BlackSwan, Sir John Watson, cookiemonster & Michel),

I'm very sorry that it was a mixed up infos.
What my colleague had executed was "grant <permissions> to SYS.UTIL_FILE to <user>;" (not 'SYS.UTIL_FILE_DIR').
UTIL_FILE is a table owned by SYS.

This clears up things.

I appreciate very much your time and help!

Kudos to all and best regards,


Re: UTL_FILE_DIR [message #661740 is a reply to message #661739] Thu, 30 March 2017 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
utl_file would be a package not a table
Re: UTL_FILE_DIR [message #661741 is a reply to message #661737] Thu, 30 March 2017 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Thu, 30 March 2017 10:12
Lets clarify a few points:
1) the utl_file_dir parameter and oracle directory objects are only used by the utl_file package, nothing else.
...

They (Oracle directories) are also used by Data Pump, Data Mining, DBMS_REPORT package, BFILENAME function and a couple of other components.

Re: UTL_FILE_DIR [message #661743 is a reply to message #661739] Thu, 30 March 2017 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What my colleague had executed was "grant <permissions> to SYS.UTIL_FILE to <user>;" (not 'SYS.UTIL_FILE_DIR').
UTIL_FILE is a table owned by SYS.

Aaargh! Never put your objects in SYS schema.
Read SYS is special.

[Updated on: Thu, 30 March 2017 03:34]

Report message to a moderator

Re: UTL_FILE_DIR [message #661744 is a reply to message #661743] Thu, 30 March 2017 04:10 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel - utl_file is supposed to be in sys.
Re: UTL_FILE_DIR [message #661745 is a reply to message #661744] Thu, 30 March 2017 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes but he said UTIL_FILE and this is a table.

Re: UTL_FILE_DIR [message #661746 is a reply to message #661745] Thu, 30 March 2017 07:22 Go to previous message
Bill B
Messages: 1717
Registered: December 2004
Senior Member
Michel Cadot wrote on Thu, 30 March 2017 05:13

Yes but he said UTIL_FILE and this is a table.

nice catch Michel. I missed that also
Previous Topic: Avoid UNION ALL
Next Topic: Re: ToDate function (split from hijacked thread http://www.orafaq.com/forum/t/56308/ by bb)
Goto Forum:
  


Current Time: Tue Feb 20 11:40:02 CST 2018

Total time taken to generate the page: 0.10198 seconds