UTL_FILE package, how to change file ownership

[Michele (Mike) Alberton]

Hi !

I'm using UTL_FILE within PL/SQL scripts but the files are not accessed as the standard Unix user I'm using to run the script, but an oracle administrative user instead (oraadm).

Is there any way to force the current Unix user to be the one handling the files generated or eread by UTL_FILE package ?

Thanks !

Mike

[Thomas Schler_1]

no answer because you do not assign points to all responses (only 74 of 100 responses got points)

[Sanjay Kumar Suri · Accepted solution]

Check if this link helps:

http://www.csee.umbc.edu/help/oracle8/server.815/a68001/utl_file.htm

sks

[Jean-Luc Oudart]

Mike,

as far as I know the file creation inherits from the tns process.

Regards,
Jean-Luc

[Michele (Mike) Alberton]

Thanks Sanjav,

it definitely helps !
It looks the Unix user running the shadow instance will be owning the files generated by the UTL_FILE.

Cheers,

Mike

[R. Allan Hicks]

Saw the earlier post. It makes sense that the owner is the TNS user.

I'm curious....

What happens if you escape out of sqlplus and do a chown.

<>>
!chown newuser:newgroup myfile
<>

I'd try it, but I'm not set up for UTL_FILE and I don't want to take my production server down to try it.

[Michele (Mike) Alberton]

Interesting trick,


let me give a try.

Cheers,

Mike

[Michele (Mike) Alberton]

Hi !

Nice try, but it looks invoking scripts trogh sqlplus ../.. @script.sql does not allow/work with escaping commands through "!".

Thanks anyway, I'm just running an easy SQL to retrieve the info I'm lloking for before pyassing the variable to PL/SQL.

Thanks !

Mike

[Nicolas Dumeige]

As for the host command execution with SQL*Plus, it works for us :

cat > test.sql
spool /tmp/one.txt
select sysdate from dual;
! chmod 777 /tmp/one_test.txt
spool off
exit success
sqlplus / @test.sql
ll /tmp/one_test.txt
-rwxrwxrwx 1 rstexplo rst_app 627 Mar 29 14:41 /tmp/one_test.txt


We use it to insert some info on the footer of spooled files.

Cheers

Nicolas

[Stephen Wales_1]

Note 74268.1 at Metalink explains how the permissions get set based upon whether you're connecting via the listener or a direct connect from the shell.

We had a similar problem with a user not being able to read the output from files dropped out into the utl_file destination

Cut and paste of an explanation I emailed to someone else some time ago below:
==========================
OK, long winded explanation follows.

We had some files being written out of Oracle to a UTL_FILE destination that we needed a Unix group (rtptest) to be able to access. First thing we did was to add the user oracle to the rtptest group.

From /etc/group:

rtptest::215:rtpupdt,oracle,rteldev


Second, we changed the default umask for the oracle user to be 022 - this has introduced other issues, in that we need to make sure that any scripts we have with the sys or system passwords in them have to be manually chmod'ed to make sure that the permissions are changed away from global read. However, since we usually store those in a mounted directory where the base directory permissions are 700, it's not too bad.

From Oracle's .profile:

set umask 022

Next, we had to do some fiddling with directory permissions.

The base directory was /var/opt/mincom/ellipse/rteldev/work - all of the utl_file directories were under this. The next one down in the tree was bw_export. It was created and defined as follows:

root[2215] rtsisd1 /var/opt/mincom/ellipse/rteldev/work # ll -d bw_export
drwxrwsr-x 5 oracle rtptest 3072 Sep 26 10:03 bw_export
root[2216] /var/opt/mincom/ellipse/rteldev/work #

Notice that there is setgid bit set on the directory. It's owned by Oracle, group rtptest and setgid makes the files written put utl_file based procedures create them with a group of rtptest. Underneath that are the final layer of directories:

root[2223] /var/opt/mincom/ellipse/rteldev/work/bw_export # ls -al
total 210
drwxr-s--- 2 oracle rtptest 3072 Sep 26 10:03 adhoc
drwxr-s--- 2 oracle rtptest 96 Sep 24 16:43 full
drwxr-s--- 2 oracle rtptest 6144 Sep 28 16:56 incremental
drwxrwsr-x 5 oracle rtptest 3072 Sep 26 10:03 .
drwxrwsr-x 18 rteldev mincomrt 86016 Nov 18 11:06 ..

Note again how the directories are owned by oracle:rtptest with setgid bit set.

Here's what I did to further test this, as per Note 74268.1 at Metalink:

Created a procedure called test1, defined as follows:

CREATE OR REPLACE PROCEDURE test1 IS

file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
col1 NUMBER; -- C1 retrieved from testtab table
retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file
BEGIN
-- Open file to write into and get it's file_handle
file_handle := UTL_FILE.FOPEN('/var/opt/mincom/ellipse/rteldev/work/bw_export','myfile.txt','W');

-- Write a line of text out to the file.
UTL_FILE.PUT_LINE(file_handle, 'this is line 1 as a test');

-- Select the c1 from the testtab table where empno = 7900
SELECT c1 INTO col1 FROM testtab
WHERE c2 = 25;

-- Using PUTF write text with the col1 argument out to the file.
UTL_FILE.PUTF (file_handle,
'This is the c1 %s when the c2 is %s.\n',
col1,'25');

-- Close the file.
UTL_FILE.FCLOSE(file_handle);

-- Open the same file to read from
file_handle := UTL_FILE.FOPEN('/var/opt/mincom/ellipse/rteldev/work/bw_export','myfile.txt','R');

-- Read a line from the file.
UTL_FILE.GET_LINE (file_handle, retrieved_buffer);

-- Print fetched line out to the SQL*PLUS prompt.
DBMS_OUTPUT.PUT_LINE(retrieved_buffer);

-- CLose the file.
UTL_FILE.FCLOSE(file_handle);
EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(file_handle);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other stuff');
UTL_FILE.FCLOSE(file_handle);
END;

SQL> set serveroutput on
SQL> exec test1
this is line 1 as a test

PL/SQL procedure successfully completed.

Then I did:

SQL> !
$ cd /var/opt/mincom/ellipse/rteldev/work/bw_export
$ ll
total 14
drwxrwsr-x 2 oracle rtptest 96 Jul 1 10:48 adhoc
drwxrwsr-x 2 oracle rtptest 96 Jul 1 10:48 full
drwxrwsr-x 2 oracle rtptest 6144 Sep 15 16:33 incremental
-rw-r----- 1 oracle rtptest 62 Sep 24 11:53 myfile.txt
$

Note that myfile.txt is readable by group rtptest.


Finally, after making all of these changes, you will need to bounce the database instance and restart the listener. One mistake we initially made was that we just bounced the database. When I was logging in from the Unix shell, it was working just fine, but when my user was connecting through SQL*Net via the listener it wasn't working.

Further scanning of the note at Metalink indicated that we should drop the listener in order for it to pick up the new default umask for Oracle.

Once we bounced the listener, all was fine.
====================

Hope this helps.

Steve