Re: UTL_FILE Permissions
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Wed, 15 Oct 2008 22:00:05 -0700
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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- References:
- UTL_FILE Permissions
- From: Adley
- UTL_FILE Permissions
- Prev by Date: Re: Simple Security Questions
- Next by Date: Re: Oracle Certification - Suggestion
- Previous by thread: Re: UTL_FILE Permissions
- Next by thread: Oracle Heterogeneous Services
- Index(es):
Relevant Pages
|
