UTL_FILE Permissions

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= Received on Tue Oct 14 2008 - 23:42:15 CDT