Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

UTL_FILE package, how to change file ownership

SOLVED
Go to solution
Michele (Mike) Alberton
Regular Advisor

UTL_FILE package, how to change file ownership

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
9 REPLIES
Thomas Schler_1
Trusted Contributor

Re: UTL_FILE package, how to change file ownership

no answer because you do not assign points to all responses (only 74 of 100 responses got points)
no users -- no problems
Sanjay Kumar Suri
Honored Contributor
Solution

Re: UTL_FILE package, how to change file ownership

Check if this link helps:

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

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Jean-Luc Oudart
Honored Contributor

Re: UTL_FILE package, how to change file ownership

Mike,

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

Regards,
Jean-Luc
fiat lux
Michele (Mike) Alberton
Regular Advisor

Re: UTL_FILE package, how to change file ownership

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
Trusted Contributor

Re: UTL_FILE package, how to change file ownership

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.
"Only he who attempts the absurd is capable of achieving the impossible
Michele (Mike) Alberton
Regular Advisor

Re: UTL_FILE package, how to change file ownership

Interesting trick,


let me give a try.

Cheers,

Mike
Michele (Mike) Alberton
Regular Advisor

Re: UTL_FILE package, how to change file ownership

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
Esteemed Contributor

Re: UTL_FILE package, how to change file ownership

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
All different, all Unix

Re: UTL_FILE package, how to change file ownership

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