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 purposes 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.
Related Articles
- Table Refresh Using EXPDP-IMPDP Datapump Command
- Full Database Refresh Using EXPDP-IMPDP Datapump command
- How To Recover Table from Drop/Truncate/Delete done on Primary using Flashback on a Standby
- How to find Privileges on Directory in Oracle
- Schema Refresh Using EXPDP-IMPDP command
