Oracle DIRECTORY access on OS layer

Oracle DIRECTORY objects are very nice to handle access for external tables or other procedures.
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.

who owns that directory?

I'm talking here about the DIRECTORY object in Oracle, not the thing you might know from several OSes.

Most objects in a current database can be created for any user, but a DIRECTORY can't. Please check the CREATE DIRECTORY definition, there you can see

No optional schema. anywhere, as you can see with tables, indices and similar objects.

But that's not the end of the story. There is a view which shows you all the objects in a database: DBA_OBJECTS. And there is a owner. allways!

So let's check all the owners of directory objects there:

select distinct owner from DBA_OBJECTS where object_type ='DIRECTORY';

OWNER
------------------------------
SYS

As there is a owner defined somewhere, we also can change it. It's only a question of effort.

Unfortunately there is no ALTER DIRECTORY between ALTER DIMENSION and ALTER DISKGROUP. But it is still defined somewhere, so it can be changed!

Now I'm leaving the well prepared area of Oracle Documentation, but that just increases the fun.

Let's start with the definition of DBA_DIRECTORIES. It's somewhat easier as DBA_OBJECTS and stil shows an owner(!) What a nice sense of humor.

CREATE OR replace FORCE VIEW "SYS"."DBA_DIRECTORIES"
("OWNER", "DIRECTORY_NAME", "DIRECTORY_PATH")
AS
SELECT u.NAME,
o.NAME,
d.os_path
FROM   sys.user$ u,
sys.obj$ o,
sys.dir$ d
WHERE  u.user# = o.owner#
AND o.obj# = d.obj#

For the following tests I created a DIRECTORY D1 with the os-path /tmp/d1.

Checking sys.dir$ I found obj# 42985 for this directory.

Just to make sure, jet's check the owner of that object. I don't trust oracle, there is too much hardcoded magic inside. But not at this point:

select owner# from sys.obj$ where obj#=42985;

OWNER#
----------
  0

Now I create another user and try to give him the ownership of this particular object - without any grants. The owner of an object should be able to access the object anyhow.

The user berx is equiped with CONNECT and CREATE PROCEDURE:

With user berx let's try to access that DIRECTORY:

declare
f utl_file.file_type;
begin
f := utl_file.fopen('D1', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.fclose(f);
end;
/

You will expect something like

ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 5

Now let's give user berx the ownership of that directory.

In my case user berx has user$ 41.

So I can (as sys)

update sys.obj$ set owner#=41 where  obj#=42985;

back as user berx - same testcase ....
-- still ORA-29289

Ok. Now we know Oracle does not check the owner of that particular object to check permissions. One of their shortcuts in the code.

So let's give poor user berx the GRANTs the ordinary way:

SQL> grant read, write on directory D1 to berx;

grant read, write on directory D1 to berx
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

Not the effect you expected? Well, maybe one of the side-effects of my unkind interaction with the data dictionary and Oracles shortcuts.

But as I know what I did before, I can revert all the things:

SQL> update sys.obj$ set owner#=0 where  obj#=42985;

1 row updated.

SQL> commit;

Commit complete.

SQL> grant read, write on directory D1 to berx;

Grant succeeded.

And with a last test as user berx:

PL/SQL procedure successfully completed.

Everything is fine again!

Comment:

This post was created out of some older documents and my holey brain. But the main theme should be clear.