Re: UTL_FILE Permissions
- From: Tim X <timx@xxxxxxxxxxxxxxx>
- Date: Wed, 15 Oct 2008 17:17:45 +1100
Adley <adley194@xxxxxxxxx> writes:
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.
Its a long time since I worked on Solaris, but under many *nix
platforms, adding a user to a new group doesn't not take affect until
the user logs in again. If you added a new group to the oracle user, it
probably won't take affect unless the process that is trying to access
the file is the child of a login that occured *after* the new group was
added.
there is an alternative approach that may work even better. Essentially,
model things on how FTP handles (or at least use to - my sys admin days
are back in the eraly 90s) uploaded files. In this situation, many sites
didn't want the uploaded files to be vailable until they had vetted
them. However, they wanted the location to be available via annonymous
FTP.
The general idea is to create a directory (e.g. incomming) and use the
setuid bit on the directory owner and only make the directory readable
by the directory owner. The setuid bit would force any files written
into the directory to be created as being owned by the owner of the
directory. the convenient thing about this is that the client who is
uploading the file, doesn't need to be the same as the directory owner
and doesn't need any special groups. Note that different nixes act a bit
differently with respect to some of the directory/file bits, so you may
need to experiment. However, it should be possible for your upload
directory to be owned by oracle and readable/writable by oracle. This
eliminates your issue. Furthermore, it means that while clients can
upload files, they cannot see what other files may have been uploaded
and they cannot retrieve uploaded data. If, for example, your client
password or ssh key gets compromised, at least they cannot access the
data which has already been uplaoded.
I use a slightly different model in our application. We create data
files using utl_file and want other processes to be able to use the
files and then remove them. We created an application group and used the
setuid bit on the group for the data directory. The files created are
owned by Oracle, but the client scripts that are not part of oracle and
move/rename/delete the files and don't need to be in any of the normal
Oracle groups. Note also that you will probably need to experiment with
umask to ensure things work as you expect and have the right access
controls.
Tim
--
tcross (at) rapttech dot com dot au
.
- References:
- UTL_FILE Permissions
- From: Adley
- UTL_FILE Permissions
- Prev by Date: UTL_FILE Permissions
- Next by Date: Re: good/bad statistics
- Previous by thread: UTL_FILE Permissions
- Next by thread: Re: UTL_FILE Permissions
- Index(es):
Relevant Pages
|
