Tuesday, October 02, 2007

ORA-29289: directory access denied

You attempted to perform an operation like that contained in the following script:
declare
file_handle utl_file.file_type;
begin
file_handle := utl_file.FOpen('TEST_DIR', 'test.txt', 'w');
utl_file.put_line(file_handle, 'test');
utl_file.FFlush(file_handle);
utl_file.FClose(file_handle);
end;
/

ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 4
Note that we opened the file for writing, but the same applies when the file is opened for reading.

The error message simply means that you are not authorized to write (or read) in the target directory that is you lack the WRITE privilege (or the READ privilege) on the directory object being used.
The good news is that directory TEST_DIR is actually existing, otherwise you'd get ORA-29280.

You can fix the problem by asking the DBA to issue:
GRANT READ ON DIRECTORY TEST_DIR TO username
-- or
GRANT WRITE ON DIRECTORY TEST_DIR TO username
This error message means also that you do not hold the CREATE ANY DIRECTORY privilege, because if you held it, you could read and/or write to any directories that you created or even to those created by other users.

Without CREATE ANY DIRECTORY privilege, another user must create the directories for you (typically the DBA) and explicitly grant read and/or write privileges before you can successfully open a file located there.

You can easily determine if you are allowed to create directory objects yourself:

select privilege from user_sys_privs
/

PRIVILEGE
---------
CREATE PROCEDURE
CREATE JOB
CREATE TYPE
CREATE SYNONYM
CREATE CLUSTER
CREATE TABLE
CREATE ANY DIRECTORY
CREATE VIEW
CREATE SESSION
CREATE TRIGGER
CREATE SEQUENCE
CREATE MATERIALIZED VIEW

So, with this system privilege, it's possible to execute the following script without the need of asking the DBA to grant the write permission on TEST_DIR:
create directory test_dir as 'C:\Temp'
/* replace C:\Temp with any suitable path for your platform or environment */
/
declare
file_handle utl_file.file_type;
begin
file_handle := utl_file.FOpen('TEST_DIR', 'test.txt', 'w');
utl_file.put_line(file_handle, 'test');
utl_file.FFlush(file_handle);
utl_file.FClose(file_handle);
end;
/
Without the CREATE ANY DIRECTORY privilege, the first statement would fail with ORA-01031: insufficient privileges.

Note also that directory names specified in UTL_FILE procedures are case sensitive.
See the posting on ORA-29280 for further examples on this subject.

Last but not least, once you pick the directory object method for specifying file system paths for read/write operations, you no longer need to worry about the setting of parameter UTL_FILE_DIR.

See message translations for ORA-29289 and search additional resources.



ORA-29289: accesso alla directory negato
ORA-29289: acceso al directorio denegado
ORA-29289: s'ha denegat l'accés al directori
ORA-29289: accès refusé au répertoire
ORA-29289: Verzeichniszugriff abgelehnt
ORA-29289: δεν επιτρέπεται η πρόσβαση στον κατάλογο
ORA-29289: katalogadgang nægtet
ORA-29289: åtkomst till katalogen nekad
ORA-29289: ingen tilgang til katalog
ORA-29289: ei hakemiston käyttöoikeutta
ORA-29289: a könyvtárhoz való hozzáférés megtagadva
ORA-29289: accesarea directorului a fost refuzată
ORA-29289: Toegang tot directory is geweigerd.
ORA-29289: acesso ao diretório negado
ORA-29289: acesso ao directório recusado
ORA-29289: отказ в доступе к каталогу
ORA-29289: přístup k adresáři byl zamítnut
ORA-29289: prístup do adresára odmietnutý
ORA-29289: odmowa dostępu do katalogu
ORA-29289: dizine erişim reddedildi
ORA-29289: directory access denied

4 comments:

Anonymous said...

this is the worst background/font color combination I've ever seen... but the information is good

Byte64 said...

I took your complaint very seriously and i decided to change the template once for good. It was an outstanding issue in my to-do list, however it's not going to be completed any time soon.

Anonymous said...

Thank you! This helped resolve an issue I was experiencing.

Wikus Coetser said...

Thanks - it helped :)

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles