Re: UTL_FILE Permissions

Adley <adley194_at_gmail.com> 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