It just can be challenging to enable proper access to those files for other users than the database user (I call it oracle here).
If there are any concerns to allow any access to the databases host (except for DBAs and OS admins) it get's tricky fast.
One possibility is to use a NFS mount and make the directory available on the DB host as well as on another, where "the others" can access the files. A possible scenario is shown on the right. An export is mounted on the DB server and the other server as well. As the DB needs to write on this directory, user and group are oracle:dba. Unfortunately, on the other server the mount has an ownership 1001:1002 now, as there was no user oracle or group dba.
In an environment whith NFS mounts I highly recommend to keep the uid<->loginname, gid<->groupname mapping the same on all hosts (where the user/group exists) to avoid mostly unwanted side effects.
But with this setting otheruser (a given user on other server) can only read files in /oradir (given the default umask of 664), but not create files (in case oracle should read them) or delete files (when they are not used anymore).
To give otheruser more control about his files in this directory, we can create a group othergroup and assign oradir to it.
When setting oracle as member of this group as well, both users can manipulate files as long as they belong to othergroup. Unfortunately there is still a limitation: both users can deal with the files, IF they belong to othergroup. If otheruser has othergroup as primary group, that's easy from this side: Every file written by otheruser can be manipulated by oracle. The other way is more complex: a file written by oracle (e.g. a logfile of an external table) still has oracle:dba as user:group. Still otheruser can read it, but not delete it, e.g. after fixing issues, or just checking everything was right.
To enable both (otheruser and database) to read AND write any file in this directory, permissions must be adapted properly.
With setting chmod g+ws all members (not only primary) of othergroup can write into this directory AND if the process is member of othergroup, the group-id of the file is set to othergroup (not to the primary group of the process). With this setting, regardless who writes the file, the group is set so both can manipulate the file.
Now the setting is complete, and in a simple environment, at least after a reboot, everything will work smooth. But there are still some pitfalls:
- as long as the database is not restarted, all existing processes (and those spawned by existing processes like jobs/scheduler processes) will not have the new groups
- in clusterware environemnts, databases are started by oraagent. To restart it, I use (the undocumented)
crsctl stop resource ora.crsd -init
crsctl start resource ora.crsd -init - processes started by listener will as well suffer this limitation unless listener is restarted
- in case of different users for clusterware and rdbms, the clusterware user must be considered as well
- other problems might occur, e.g. with scripts in those directories.
Still this should show a introduction to a sane usage of DIRECTORY to exchange files with other os users.
