When the file is created using the utl_file plsql package, how to set the desired file permission

When the file is created using the utl_file plsql package, how to set the desired file permission ?

Solution
In the Client/Server architecture , if you connect to Oracle database from any client workstation, through SQLNET, a session is created on behalf of your user process. It is the responsibility of the listener process to connect user process with available shared server / dispatcher process. These are the background process serving on behalf of user process. Normally oracle is the owner of these background process.

The file permissions is not a function of the UTL_FILE package at all.The concept is if you are establishing a local session on the server,then the shadow process you started inherits the permissions as you logged in as..
However if you login from a remote client say a SQLplus session from a NT workstation, then the file inherits the permission of the TNS Listener of the server. The TNS Listener permission is that permission as at the time when the listener was started by the oracle user. By stopping the listener and logging back in and restarting listener after setting the new umask value, the <oracle> user basically inherits the new umask value which in turn passes it to the new listener.Hence whenever you change the umask value , you should always restart the listener so that it gets the umask value of the user who has started the listener.

1. Login to server as oracle
2. stop the listener
3. set the desired umask value
4. disconnect remote session (session from where you are initiating procedure)
5. log back into the server as oracle to inherit new umask value
6. As a check - type 'umask' at the prompt to check the value
7. start the listener
8. from remote client start a new sqlplus session to test procedure.

You must get the right permissions this time.

You must be connected to database using listener or bequeath connection. So in both the cases the UMASK should be specified properly.

If the database is started using user oracle (UMASK=022) and listener started using user test1(UMASK=002). Always the new files will be created with the ownership of oracle. Hence oracle should have proper permissions to access the folder/directory where file is created.

If you are connected to database using bequeath connection (sqlplus scott/tiger), then file permissions will be inherited from user oracle. Hence the file permissions would be (rw-r-r). If you have connected through listener than file permissions will be inherited from user test1 because the listener was started by test1. Hence the file permissions would be (rw-rw-r).

If the file is already existing and you are trying to change the permission using utl_file package by setting new umask value, the changes will not be affected. You will have to manually change the permission using 'chmod' command.

Example :

Server :

=========

1. Login to server as oracle
login as: oracle
oracle@10.177.59.119's password:
Last login: Thu Oct 18 07:50:27 2007 from 10.177.59.103

2. stop the listener
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-OCT-2007 07:53:47

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

3. set the desired umask value
[oracle@localhost ~]$ umask
0022
[oracle@localhost ~]$ umask 002

4. disconnect remote session (session from where you are initiating procedure)
5. log back into the server as oracle to inherit new umask value
6. As a check - type 'umask' at the prompt to check the value
7. start the listener
[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-OCT-2007 07:54:12

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /home/databases/ORA10203/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /home/databases/ORA10203/network/admin/listener.ora
Log messages written to /home/databases/ORA10203/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 18-OCT-2007 07:54:12
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/databases/ORA10203/network/admin/listener.ora
Listener Log File /home/databases/ORA10203/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "ORA10203" has 1 instance(s).
Instance "ORA10203", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$

Client :
=========

8. from remote client start a new sqlplus session to test procedure.

C:\Documents and Settings\aldsouza>sqlplus scott/tiger@ORA10201_LINUX

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 18 07:47:20 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> declare
2
3 f1 utl_file.file_type;
4
5 begin
6
7 f1 := utl_file.fopen('TEST_DIR','test.txt','w');
8 utl_file.fclose(f1);
9
10 end;
11 /

PL/SQL procedure successfully completed.

SQL>

The file will be created in the TEST_DIR with the following permissions

-rw-rw-r-- 1 oracle dba 0 Oct 18 08:00 test.txt