Umask problem with Oracle processes on a trusted system

[Mark Sellan]

I have two servers which are both set up as trusted systems. There are a number of Oracle stored procedures that get run at a sqlplus prompt by the operations staff.

The procedures write their output in various places and in each case write 600 mode files owned by the HPUX Oracle user.

I entered a umask command in /etc/profile and in /home/oracle/.profile to set a 022 mask. It does not work for stored procedures only for users working within a shell.

If I touch a file logged in as Oracle I get the correct permissions (644). But if the stored procedure writes a file I get 600.

Is this because the stored procedure is not acutally logging in but merely using the Oracle account for writing the files? Is default system umask controlled by a kernel parameter?

How can I change this without converting back to a non-trusted system?

Thanks much,

-mark

[Duncan Edmonstone]

umasks are inherited from parent processes - although there's nothing to stop a process changing its umask once its started. Try setting the umask in the startup scripts for oracle and sqlnet. You'll have to re-start for these to take effect, and something in the oracle code may override your selection anyway...

HTH

Duncan

[A. Clay Stephenson · Accepted solution]

You need to be aware that there are two components to this problem. The umask and the creation_mode of the file. Even if umask were set to 000 (wide-open), if the creation mode is set to 600 (in the program that create the file), the file will be created with 600 permission. Umask cannot take away what was not there in the first place.

[A. Daniel King_1]

I've found that the way umask is called makes a difference. For instance:

/usr/bin/umask 077

Does absolutely nothing! However,

umask 077 does the trick.

I dunno. Perhaps this is my patch level, etc., but using the shell-internal umask (without the path) seems to be much better.

[Mark Sellan]

What controls the file creation mode? Is it the application (i.e. sqlplus)?

And where is the setting that makes a trusted system's umask different than a non-trusted system. It can't be /etc/profile since there was no entry in there until I added one.

Thanks again,

-mark

[WFFA MIS]

Mark
What was your final conclusion on this matter, I am having similar issues here on my servers. At my location, the "other" bit is getting dropped with the Oracle umask set at 002.
When I touch I get 664, when the database creates, I get 640?
any clues
Steven

[Mark Sellan]

My solution to this is so lame that I'm embarrassed to even admit to it....

Essentially it is not solved...although I think Clay provided the answer.

Unfortunately, the programmers here who work with the application in question cannot figure out how to change the creation mode when they write out the file.

So, my big embarrassing band-aid is I have scripts run via cron that correspond to the monthly run schedule of these jobs and set the files to the proper mode before the programmers then access/transfer the data to other systems.

Not great...but until I learn more about SQL and PL/SQL or I get a application programmer here *really* interested in the problem, it'll have to do.... :(

Maybe some DBA guru can help?