Solution for Error ORA-29289 Directory Access Denied in Oracle

Error ORA-29289 Directory Access Denied in Oracle


Sometimes application users have
DIRECTORY details like name of DIRECTORY of a database but not having proper privileges (READ, WRITE) on DIRECTORY and received error “ORA-29289″ directory access denied”. Thanks to one of my application users who did the same mistake yesterday and encourage to me elaborate here to help our DBA community.


Application user tried to use
DIRECTORY name for  UTL_FILE packages inside PL/SQL block and received below errors due to insufficient privileges on DIRECTORY.  

ERROR at line 1:
ORA-29289: directory access denied

ORA-06512: at “SYS.UTL_FILE”, line 23

ORA-06512: at “SYS.UTL_FILE”, line 105

ORA-06512: at “TESTUSER1.MYTEST_PL”, line 4  

ORA-06512: at line 1


Below is the step by step demonstration for the solution
ORA-29289″ directory access denied.


STEP1:
We need to find which users have access to the required DIRECTORY.
Click here to get step by step to validate access on DIRECTORY in Oracle.


STEP2:
As we found user has no access to DIRECTORY in STEP1 so we have to grant access to the required user on DIRECTORY.

SQL> GRANT READ, WRITE ON DIRECTORY MOMO_DIR_LAB TO TESTUSER1;

Grant succeeded.


STEP3: This is the final step where validate again user’s privilege on DIRECTORY as we did in STEP1 and now it’s time to execute the procedure to validate successful execution.

SQL> conn testuser1/XXXXXXXXX

Connected.

SQL> exec MYTEST_PL;

PL/SQL procedure successfully completed.


NOTE: 1
. This is just a demo so I connected to my user to validate, you may ask your application user to validate after a successful grant to the user from your end (DBA).


2.
DIRECTORY contains one physical directory on the database server so that physical directory (file) should have properly granted to READ WRITE to that location to other than group owner of a file on OS level.


We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.


This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

 

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

 

 

 662 total views,  2 views today

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *

1 × 2 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru