Spooled output from sqlplus "File permission Problem"

[Joaquin_13]

Hi guys,

I've been encountering this problem on one of our DB server. With regards to File permission, my problem is whenever i execute this specific shell script where it is composed of SQL scripting. The generated file by the program or the spooled file will definitely be under oracle as the owner of the file but the problem is i want the file permission of the spooled file will have RWXR-XR-X permission. Is there any parameter i should add on the DB/.profile/script. By the way i already explicitly declared on my .profile the umask as 022.

best regards,
Joaq's

[A. Clay Stephenson]

There are two components at play whenever a file is created. The mode and umask. For regular files under the shell (or sqlplus) the files will have a mode of 666 (rw-rw-rw); umask then "subtracts" from this mode to yield the actual mode of the created file. It's not really subtraction but you can think of the action of umask that way.
Umask can never put in what was not already there. To set the execution bit requires an explicit chmod after the file has been created.

If you were programming in C (or Perl), for example, then it is possible to set the executable bit when the file is created but not from the shell or sqlplus.

[Indira Aramandla]

Hi Joaqin,

The user file creation mode mask (umask) is a built-in shell command that may be used to set default values for the read/write/execution permissions on newly created files. It should be executed in either the ".cshrc" or ".profile" shell startup files.

It is given a three-digit octal value, which represents the binary inverse of the permissions which may be assigned to files. This three-digit numeric argument represents the access to be "inhibited" or "masked out" when a file is created.

By default, most UNIX versions specify an octal mode of 666 (any user can read or write the file) when they create new files. Likewise, new programs are created with a mode of 777 (any user can read, write, or execute the program). For regular files will have a mode of 666 (rw-rw-rw)

The most common umask values are 022, 027, and 077. A umask value of 022 lets the owner both read and write all newly created files, but everybody else can only read them:

0666 Default file creation mode
022 resultant mode
0644 resultant mode

A umask value of 077 lets only the files owner read all newly created files:

0666 Default file creation mode.
077 umask
0600 resultant mode

On many UNIX systems, the default umask is 022. This is inherited from the init process, as all processes are descendants of init.

Indira A

[Joaquin_13]

Thank you guys for the immediate & extensive response. I do already declared a umask value in my ".profile" but the output file seems doesn't inherit the given file permission mode. In addition the file that were dump after executing the script is dumped to the given "UTL DIR" within the Oracle RDBMS. The permission applied to the file is only RW------- where in only oracle user have the access. I need the permission to be set to RWXRW-RW- so that even the Application Developer have the access to the file once it was produce for development purpose & for security purpose I can't let them use the "ORACLE" user during their testing.

[Indira Aramandla]

Hi Joaq,

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 SQL+ session from a NT workstation, then the file inherits the permission of the TNS Listener. The TNS Listener permission is that permission as at the time when the listener was started by the oracle user.

If you have metalink account then refer to Note:197201.1 - How to Change Owner/Permissions for Files Created by UTL_FILE

Indira A

[Jean-Luc Oudart]

Joaquin

if you run the script from the same machine you would not need to use the listener process and therefore the oracle process will be owned by the user itself.
Hence ownership and permissions of the spool file.

Regards
Jean-Luc

[Patti Johnson · Accepted solution]

If you only want the permissions set for this file, then you could modify you script and have it change the permissions after the file is created.

You can do that from sqlplus, after spool off, with
sql> spool off
sql> !chmod 755 your_file_name

Patti

[Joaquin_13]

Hi,

Thank's guys for the effort in resolving my concern especially to Patti.. Your solution was the ideal work around on my problem..

thank's & best regards,
Joaquin

[Joaquin_13]

...