Wrong owner when executing oracle pl/sql FOPEN?

Hello,

I have a user that is executing some pl/sql. He opoens a file with UTL_FILE.FOPEN and then writes some things and closes the file. BUT the file is owned by unix user "oracle" with a group of "dba" ??!! He is running as user "mwallace" (both in unix and oracle sqlplus) ? When he tries to edit the file, he gets permission denied... Please help!

btw...If the user spools output it will go to a file which he owns.

Thanks!
Keith
keithedwardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

syakobsonCommented:
UTL_FILE package ALWAYS creates a file owned by oracle rather than package caller. I'll explain why. PL/SQL (and UTL_FILE is PL/SQL package) is executed on the SERVER side rather than CLIENT side. Client can be on a different box, using different OS, etc... and in general does not need to exist as OS user on DB server side. You need to write an external stored procedure that would take two parameters: file and new owner and would execute corresponding chown (Unix change owner command).

Solomon Yakobson.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mshaikhCommented:
Go to $ORACLE_HOME/bin and do the following at the unix prompt:

chmod 751 sqlplus

Then try again.
0
mshaikhCommented:
You have to be logged in as oracle to do this.
0
syakobsonCommented:
Oops, I clicked Submit to early. I forgot to explain why spool works. SQL*Plus/Server Manager command spool is executed on the CLIENT side by CLIENT process. That is why spool file is owned by client OS username. If client box is different from DB server box, spool file and UTL_FILE created file produced by the same client would end up on different boxes even if both spool command and UTL_FILE.FOPEN would use same file specification.

Solomon Yakobson.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.