Using OS credentials with DIRECTORY objects

Someone needed access to DIRECTORY objects located behind UNC path. There are actually two cases, first where machines are located in the same domain or domains having some sort of trust relationship and second, where machines are in non-trusting domains. Obviously the first case is a bit easier and presented here first. I will publish the second case later, after having time to finish it off.

Kudos to E. Soini and P. Kaila for initial researching and testing out the “same domain” case.

 

Machines in the same domain

 

Let’s assume a couple of things. We already have a directory on some remote server and it is shared. It could be referenced with an UNC path  \\remoteserver\remoteshare. Our server is named myserver. There is a file called readthis.txt on the remote server.

First we need to grant some rights via NTFS ACL’s to remote directory, for example, read and write permissions. The permissions should be granted to MYSERVER Computer object via OS GUI or command line tools and I’m not going to go through it here.

After that we need to grant permissions to a share to the same computer object, read and write permissions again. Again with OS GUI or command line tools.

After this we create the directory objects in Oracle as a user having CREATE ANY DIRECTORY privileges and grant some permissions on it, if we are not running as a user having full privileges to all objects, which should be the case.

CREATE OR REPLACE DIRECTORY remotedir AS '\\remoteserver\remoteshare';
CREATE OR REPLACE DIRECTORY localdir AS 'C:\temp';
GRANT READ ON DIRECTORY remotedir TO user;
GRANT WRITE ON DIRECTORY remotedir TO user;
GRANT READ ON DIRECTORY localdir TO user;
GRANT WRITE ON DIRECTORY localdir TO user;

After this we can copy an example file with UTL_FILE package, as follows. One might try to use DBMS_FILE_TRANSFER which is apparently only suitable for files being multiples of 512 bytes.

BEGIN
UTL_FILE.FCOPY('REMOTEDIR','test1.txt', 'LOCALDIR', 'test2.txt');
END;

What this means is that if you want to use remote UNC paths with, for example, datapump jobs, you can still schedule the task under LocalSystem (Older win: SYSTEM) account, since the authentication is based on machine account. Somewhat easier than changing Oracle to run under some domain user.