Re: utl_file and listener

From: Rhojel Echano <rhojel.echano_at_gmail.com>
Date: Sat, 4 Jul 2009 13:40:58 +0800
Message-ID: <16e45e3f0907032240s247d4224r29f71baa0cc0b42a_at_mail.gmail.com>

This sort of thing happened to me before....

Have you restarted the listener with a new oracle OS user terminal session *after* the privileges on the directory has been granted to oracle?

When you do a beq connection, the shadow process is spawned by sqlplus on the server, and would have the OS privileges as the account that you used to invoke sqlplus.
Via TNS however, we know that the shadow process is spawned by the listener so if this listener was started by the oracle OS account *prior* to granting privileges to oracle, then it inherits the privileges of the oracle account that didn't have the privilege to the dir that you've added in UTL_FILE.

Hope this helps.

On Wed, Jul 1, 2009 at 4:17 AM, Mathias Magnusson < mathias.magnusson_at_gmail.com> wrote:

> Can your run mount and paste the output here? suid may not be honored. I
> agree with Stephanie that this sounds as if we have a suid issue.
>
> Mathias
>
>
> On Tue, Jun 30, 2009 at 9:17 PM, Stephen Andert <andert_at_gmail.com> 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
>> > 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>
>>> >
>>> >
>>>
>>>
>>>
>>
>

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

Received on Sat Jul 04 2009 - 00:40:58 CDT