• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Oracle DIRECTORY access issue

Hello Experts,

I have user where I have the created a directory. Where the user is having  having 'create any directory ' grant .

But when I want to read any file from that directory that I get "29283. 00000 " error.

When I do the same operation from my local system I am able to read the file.

Error report:
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.

Open in new window

0
Swadhin Ray
Asked:
Swadhin Ray
  • 14
  • 9
  • 6
2 Solutions
 
sdstuberCommented:
does oracle itself have access to the os file?



your database users might be able to read/write the directory object, but if the os user (oracle) that is running the instance can't access the file, you'll get ora-29283 errors
0
 
slightwv (䄆 Netminder) Commented:
Does Oracle have read/write privs on that directory?

Is this on Windows and a network folder?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
>> does oracle itself have access to the os file?
Yes

>>Does Oracle have read/write privs on that directory?
Yes it has

>>Is this on Windows and a network folder?
No it is on Linux server where my oracle is been installed
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Swadhin RaySenior Technical Engineer Author Commented:
When I checked :

SQL> select * from USER_TAB_PRIVS where grantee='MYUSER'
and table_name ='MY_DIR';

Open in new window


Then I get the below result :


GRANTEE	OWNER	TABLE_NAME	GRANTOR	PRIVILEGE	GRANTABLE	HIERARCHY
MYUSER	SYS	MY_DIR	SYS	EXECUTE	YES	NO
MYUSER	SYS	MY_DIR	SYS	READ	YES	NO
MYUSER	SYS	MY_DIR	SYS	WRITE	YES	NO

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
And the directory which was created has also been granted with chmod g+rwx <<MY_DIR>> from sudo access .
0
 
sdstuberCommented:
what about the files in the directory?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
its a text file which I want to load it into my tables..

>>your database users might be able to read/write the directory object, but if the os user (oracle) that is running the instance can't access the file, you'll get ora-29283 errors

I think I have given read , write and execute from this directory by chmod
0
 
sdstuberCommented:
did you chmod the files in the directory?

or only the directory itself?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
only the directory .

chmod g+rwx MYDIR
0
 
sdstuberCommented:
chmod the files then see if you can read them
0
 
Swadhin RaySenior Technical Engineer Author Commented:
If I give :
chmod g+rw MYFILE 

Open in new window


Then I get the below error:

"Operation not permitted"

Open in new window

0
 
sdstuberCommented:
you need to be root, or the owner of the file in order to use chmod on it
0
 
Swadhin RaySenior Technical Engineer Author Commented:
But I used the sudo user where I have given the access for chmod
0
 
sdstuberCommented:
what do these return?  - change "some_file_you_want_to_read" to one of your real files

ls -alF  some_file_you_want_read
whoami

and, what was the exact command you used to try to change the permissions?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
The exact command that I used is :

chmod g+rwx MY_DIR 

Open in new window

0
 
sdstuberCommented:
what about the other output?  where is the sudo?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
First logged on with my user into linux .
then I used sudo user to login .


whoami shows my user account details.
0
 
sdstuberCommented:
please post the exact output

copy paste the all of the text from your screen (don't take a screen image, just copy the text)

if you want to hide the file name, create a new dummy file "dummy.txt"  and then use it in the ls above.  I'm not asking you to show me all of your files.

but I want to see exactly what you're doing when it fails.
the only think you need to mask is where you type in a password,  replace that with xxxxxx otherwise please post exactly what you typed and all of the output

don't describe the output to me, show me what it actually is
0
 
slightwv (䄆 Netminder) Commented:
Can you also post the results from:
select * from dba_directories where directory_name='MY_DIR';
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Hello experts,

Here is what I have done till now but still facing the same issue:


login as: myuserid
   _________________________________________________________________

    This system is restricted to XXXX authorized users for business
    purposes.  Unauthorized access is a violation of the law. This
    service may be monitored for administrative and security reasons.
    By proceeding, you consent to this monitoring.
   _________________________________________________________________

   [ THIS SYSTEM IS CURRENTLY BEING ADMINISTERED BY LINUX SUPPORT ]

FOR LOGIN ACCESS, GO HERE: http://XXXXXXXX.com

FOR SA REQUESTS/WORK ORDERS, GO HERE: http://XXXXXX.com/requests
AND OPEN AN AOTS TICKET USING "LINUX SYS ADMIN" WORK QUEUE

TO REPORT A SEV 1 OUTAGE CALL THE ITO SERVICE DESK at ***-***-****

myuserid@myhost.xxxx.com's password:
Last login: Tue Aug 28 10:01:00 2012 from vpn-***.***.****.vpn.XXXX.com
[myuserid@myhost ~]$ sudo su - xxx***
[sudo] password for myuserid:
[xxx***@myhost ~]$ cd /opt/app/abc/xxxx/
[xxx***@myhost xxxx]$ pwd
/opt/app/xxxx/xxxx
[xxx***@myhost xxxx]$ chmod a+rwx data_load
You have new mail in /var/spool/mail/xxx***
[xxx***@myhost xxxx]$ ls -lrt
total 64056
drwx------  2 root     root        16384 Sep 16  2010 lost+found
drwxrwxrwx  4 xxx***  xxx***     4096 Apr  7  2011 usr
drwxrwxrwx  2 xxx***  xxx***     4096 Aug 28 08:33 data_load
[xxx***@myhost xxxx]$ cd data_load
[xxx***@myhost data_load]$ ls -lrt
total 794692
-rw-r----- 1 xuser xxx***  18246753 Aug 27 17:23 file1.txt
-rw-r----- 1 xuser xxx***       133 Aug 27 17:23 file2.txt
-rw-r----- 1 xuser xxx***   8208949 Aug 27 17:23 fil3.txt

Open in new window

Here is the output for the query asked for :


SQL> select * from dba_directories where directory_name='MY_DIR';

Open in new window

OUTPUT:
OWNER     DIRECTORY_NAME         DIRECTORY_PATH
SYS	       MY_DIR	        /opt/app/abc/xxxx/data_load

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>drwxrwxrwx  2 xxx***  xxx***     4096 Aug 28 08:33 data_load

oracle should own the folder the database needs to write to.

You should also NEVER set permissions to 777.  This is a large security hole.


What is the filename you are trying to read?  If it is myfile.txt, then please post the results of:

ls -al /opt/app/abc/xxxx/data_load/myfile.txt

I want to see the file permissions on that file.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Here is the OUTPUT:

-rw-r----- 1 xuser xxx*** 4229697 Aug 27 17:23 file1.txt
0
 
sdstuberCommented:
is Oracle a member of xxx*** group?  

if not, Oracle can't read the file
0
 
slightwv (䄆 Netminder) Commented:
>>-rw-r----- 1 xuser xxx*** 4229697 Aug 27 17:23 file1.txt

There is your problem.  oracle is the user that is running the database.  The oracle unix user does not have access to that file.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
thanks for the inputs .. I will check the same with my network admin and then I will update ..

Till that time let me have the question open.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
What are the steps need to be done for getting the access ....?  And is that we need root user to give the grants
0
 
slightwv (䄆 Netminder) Commented:
The oracle database runs as the oracle unix user.  It is actually this user that needs access to the file.

Either add the oracle user to the unix group that can access the file or grant permissions on the file and folder for the oracle unix user to be able to read the file.
0
 
slightwv (䄆 Netminder) Commented:
To follow up:

We cannot say which you need to do.  We do not know your system so anything we say might not be OK in your specs.

It would be very easy to say just do: chmod -R 777 /opt/app/abc/xxxx/data_load

But that would open up security issues and might not actually solve the problem since we do not know how the file to be loaded gets in the data_load folder.  That process might reset the permissions.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks a lot experts.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now