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.
1,501 Total Views, 4 Views Today
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.
Thanks for the visits!
Share Learn Grow!