6 Replies Latest reply: Dec 10, 2012 5:20 AM by BluShadow RSS

    Using wildcards / specifying multiple directories with UTL_FILE_DIR

    3004
      Hi,

      I would like to allow Oracle to write files to any subdirectory of a specified directory (say '/home/rolf'). Since the subdirectories may change often, I would like to specify this once in init.ora and not have to change it when I add a new directory. I was hoping something like:
      UTL_FILE_DIR = /home/rolf/*
      would do the trick, but alas, it doesn't seem to. Does anyone know a way to make a set of subdirectories writeable without having to list every single directory?

      Thanks

      --Rolf                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        • 1. Using wildcards / specifying multiple directories with UTL_FILE_DIR
          3004
          Let me give it a shot. I remember setting this parameter as UTL_FILE_DIR=* which actually specifies any directory.
          • 2. Using wildcards / specifying multiple directories with UTL_FILE_DIR
            3004
            <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>Originally posted by Madhavi Acharya(madhaviacharya@netscape.net):
            Let me give it a shot. I remember setting this parameter as UTL_FILE_DIR=* which actually specifies any directory.<HR></BLOCKQUOTE>

            Hmmm, I read about this, but the Oracle8 PL/SQL Programming book recommended against this as it allows Oracle to write anywhere and can 'circumvent operating system permissions'. That sounds a little dangerous so I would like limit Oracle to a specified directory tree. Any ideas?

            --Rolf
            null
            • 3. Re: Using wildcards / specifying multiple directories with UTL_FILE_DIR
              968331
              Hi,

              Just want to know.Does this will work or not?

              UTL_FILE_DIR = /home/rolf/*

              I mean . Does Oracle user able to read/write to any directory under the given patch " /home/rolf/*"?

              Regards,
              -Amit
              • 4. Re: Using wildcards / specifying multiple directories with UTL_FILE_DIR
                BluShadow
                This is a very old thread. If you have a question you should really start your own thread for it.

                In answer to your question...

                UTL_FILE_DIR parameter has been deprecated since 10g.
                You should no longer use the UTL_FILE_DIR parameter as it can leave security holes on your server, meaning people can access files and directories they shouldn't.

                Oracle has replaced the functionality with Oracle Directories.

                http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm#SQLRF01207

                Example usage:

                As SYS user:
                CREATE OR REPLACE DIRECTORY mydir AS 'c:\myfiles';
                Note: This does not create the directory on the file system. You have to do that yourself and ensure that oracle has permission to read/write to that file system directory.

                Then, grant permission to the users who require access e.g....
                GRANT READ,WRITE ON DIRECTORY mydir TO myuser;
                Then your user (myuser in this example) will use that directory object inside your FOPEN statement e.g.
                fh := UTL_FILE.FOPEN('MYDIR', 'myfile.txt', 'r');
                Note: You MUST specify the directory object name in quotes and in UPPER case for this to work as it is a string that is referring to a database object name which will have been stored in uppercase by default.
                • 5. Re: Using wildcards / specifying multiple directories with UTL_FILE_DIR
                  968331
                  Thank you for the reply.I know it's an old thread. I should have open a new one :)

                  I know it's deprecated with Oracle 10g versions.
                  But sometimes developers does not want to go with the new changes.

                  Can you please still answer my original question reg the UTL_FILE_DIR?
                  • 6. Re: Using wildcards / specifying multiple directories with UTL_FILE_DIR
                    BluShadow
                    965328 wrote:
                    Thank you for the reply.I know it's an old thread. I should have open a new one :)

                    I know it's deprecated with Oracle 10g versions.
                    But sometimes developers does not want to go with the new changes.
                    So you're happy for your systems to have security holes in them.... very odd. Oracle does not recommend using deprecated functionality, so the developers should be using the correct tool for the job, and that IS directory objects.
                    Can you please still answer my original question reg the UTL_FILE_DIR?
                    In the hours that have passed since you posted the question, you could have tested it. I can't recall if * was a special case in it's own right or whether it could be part of a directory path.... and I was wrong, it's not since 10g, it's actually since 9i R1...

                    http://docs.oracle.com/cd/B19306_01/em.102/b16231/db.htm#sthref1074

                    So, using it is using technology that is well over a decade old. While your at it, why not go back to using Windows 98. ;)