Re: utl_file and listener -- RESOLVED

From: Stephen Andert <andert_at_gmail.com>
Date: Tue, 30 Jun 2009 14:21:39 -0700
Message-ID: <6d45e210906301421s5000fe04h577a92225a4ff563_at_mail.gmail.com>

Thanks to all for your help. Seems the problem was with permissions and the fact that the listener-spawned connections had different rights than the bequeath connections.

Solution was to chmod the directories to 2777 (anything less did not work). Not a great solution security-wise, but these directories are isolated from other stuff, so the risk is not huge (I think). Had to jump through some hoops as it would not let us assign those privs directly.

*Stephen Andert*
 Blog <http://cactustri.blogspot.com/> | Flowing Desert Photography<http://flowingdesert.com/>
Twitter <http://www.twitter.com/CactusTri>

On Tue, Jun 30, 2009 at 12:27 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

>
> Well, the only difference I am aware of in the scenario you describe is
> the parent process of the shadow process - the user's sqlplus process in
> one case, and tnslsnr in the other case. Everything looks right indeed.
> No trace file with some OS error number that could give a clue?
>
>
> Stephen Andert wrote:
> > Stephane,
> >
> > I think my sticky bit is set right and the oracle user is in the group
> > that has permissions to read and write to the directory.
> >
> > $ ls -l oracle tnslsnr
> > -rwsr-s--x 1 oracle dba 118977048 Sep 19 2006 oracle
> > -rwxr-x--x 1 oracle dba 570944 Sep 19 2006 tnslsnr
> >
> > So if I understood you correctly, that should not be a problem, right?
> >
> >
> >
> > *Stephen Andert*
> > Blog <http://cactustri.blogspot.com/> | Flowing Desert Photography
> > <http://flowingdesert.com/>
> > Twitter <http://www.twitter.com/CactusTri>
> >
> >
> >
> > On Tue, Jun 30, 2009 at 11:48 AM, Stephane Faroult
> > <sfaroult_at_roughsea.com <mailto:sfaroult_at_roughsea.com>> wrote:
> >
> > Stephen,
> >
> > It's obviously a problem linked to rights on /dir_a. Just a
> stupid
> > thought, when you connect through SQL*Net, and if you aren't using
> > shared servers, the shadow process is forked by tnslsnr that then
> > excutes oracle (the program). Could you check in $ORACLE_HOME/bin
> > if you
> > really see something like this:
> > $ ls -l oracle tnslsnr
> > -rwsr-s--x 1 oracle dba 152692986 2009-01-18 18:44 oracle
> > -rwxr-x--x 1 oracle dba 833609 2009-01-18 18:45 tnslsnr
> >
> > If you don't see all the 's' for oracle, and if for an unknown reason
> > tnslsnr belongs to the wrong user/group, I could make sense of
> > what you
> > describe.
> >
> > HTH
> >
> > Stéphane Faroult
> >
> >
> > Stephen Andert wrote:
> > > Sort of strange scenario.
> > >
> > > Oracle 10.2.0.2 64 bit
> > > SunOS 5.10
> > >
> > > Added a new directory to utl_file. Let's call it /dir_a
> > >
> > > When a user (user1) connects locally (beq, not SQL*Net) to the
> > db and
> > > writes a file to /dir_a it works fine.
> > >
> > > When the same user (user1) connects to the db using listener
> > > (_at_dbtest), they are not able to write to /dir_a, UNLESS the file is
> > > already there. The error is:
> > >
> > > ORA-29283: invalid file operation
> > > ORA-06512: at "SYS.UTL_FILE", line 475
> > > ORA-29283: invalid file operation
> > > ORA-06512: at line 12
> > >
> > > Same user can create a file in the directory outside the db as
> > can the
> > > oracle user.
> > >
> > > Any ideas?
> > >
> > > Thanks In Advance
> > >
> > >
> > >
> > > *Stephen Andert*
> > > Blog <http://cactustri.blogspot.com/> | Flowing Desert
> > Photography
> > > <http://flowingdesert.com/>
> > > Twitter <http://www.twitter.com/CactusTri>
> > >
> > >
> >
> >
> >
>
>
> --
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 30 2009 - 16:21:39 CDT