Re: UTL_FILE Permissions

Adley wrote:
> Peace all,
>
> I am working with an application which needs to execute UTL_FILE to
> read a certain file from a certain directory.
> Assume that the schema of this application is APP, the directory is /
> oradata/app/dir/files, and the DIRECTORY object is APP_DIR.
> Assume that the file to be read is called ‘testappfile’.
> The schema APP has already been granted READ and WRITE to the APP_DIR,
> and utl_file_dir in the init.ora is still ‘*’
> And schema APP has been granted the privilege to execute UTL_FILE
> package.
>
> Now, the file to be read is created by an SFTP user which is used by
> an external interfacing application to send files.
> Let’s say that this user is called ‘sftpuser’ whose OS group is sftp.
>
> Here are the test steps which I’ve done:
>
>
> Step 1
> The file testappfile has a 660 permission, owned by sftpuser:sftp
> The whole directory is accessible (rx permission) to the sftp group,
> and the last directory (files/) is writable to sftpuser.
> The /oradata directory is owned by oracle:dba, so I had to use setfacl
> (I’m on Solaris 10) to give permissions to sftp group and sftpuser.
> The next two directories, app and dir, are also owned by oracle:dba,
> so I had to use setfacl again.
>
> Just by using this configuration, when I execute the UTL_FILE to read
> testappfile, I failed.
> This is understandable because oracle user lacks the permission to
> read the actual file.
> So, I went to do some modifications.
>
>
> Step 2
> I used the usermod command so that oracle user has a secondary group
> of sftp (usermod –G sftp oracle).
> Testing manually from the OS level proved to me that oracle can
> actually read and write testappfile.
> However, when I tested from the SQLPLUS prompt using the APP schema,
> it still failed to read the file.
> Currently, my understanding is that at the OS level, the oracle user
> is used by UTL_FILE to read testappfile.
>
> What made me confused was that if I use SYS instead, it works.
> Out of curiosity, I did one more experiment.
>
>
> Step 3
> I changed testappfile’s group owner to be sftpuser:dba and still
> retains the 660 permission
> In this configuration, the APP schema could read the file.
>
>
> Hence, what I need to understand is what actually happened and went
> wrong?
> Does assigning a secondary group to oracle isn’t sufficient for this
> kind of task?
> Or is the APP schema lacks some privileges so that it failed to read
> the file as described in Step 2?
>
> Any help or insights on this matter is greatly appreciated.
>
> Thanks,
> =adley=

If you have a directory object pointing to the directory, and you have granted READ and/or WRITE on the directory object to your user the UTL_FILE_DIR entry should be dropped.

 From that point forward if you can not read the file then you either have the path wrong, the file name wrong, or the file was written with permissions or too a directory the user "oracle" can not access.

When you want help it is essential that you post full version number (all three decimal places) and the full and complete actual error message. None of which you did.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org