Knowledge Base : Change Directory Owner

59 Juni 16, 2022

Can I change the owner of an Oracle database directory?

I can’t? So, why there is a column „OWNER“ in DBA_DIRECTORIES?

According to Oracle documentation, it’s not possible to create a directory in a user schema:

But you can tweak the whole thing under the hood:

select distinct owner from DBA_OBJECTS where object_type ='DIRECTORY';
 OWNER
 ------------------------------
 SYS
CREATE OR replace FORCE VIEW "SYS"."DBA_DIRECTORIES" ("OWNER", "DIRECTORY_NAME", "DIRECTORY_PATH")
AS
 SELECT u.NAME,
 o.NAME,
 d.os_path
 FROM sys.user$ u,
 sys.obj$ o,
 sys.dir$ d
 WHERE u.user# = o.owner#
 AND o.obj# = d.obj#
select owner# from sys.obj$ where obj#=XXX;
OWNER#
 ----------
 0
declare
f utl_file.file_type;
 begin
 f := utl_file.fopen('D1', 'something.txt', 'w');
 utl_file.put_line(f, 'line one: some text');
 utl_file.fclose(f);
 end;
 /

You will expect something like

ERROR at line 1:
 ORA-29289: directory access denied
 ORA-06512: at "SYS.UTL_FILE", line 41
 ORA-06512: at "SYS.UTL_FILE", line 478
 ORA-06512: at line 5

Now let’s give user XX the ownership of that directory.

update sys.obj$ set owner#=XX where obj#=XXX;
SQL> grant read, write on directory D1 to XX;
*
 ERROR at line 1:
 ORA-00603: ORACLE server session terminated by fatal error
SQL> update sys.obj$ set owner#=0 where obj#=XXX;
1 row updated.
 SQL> commit;
 Commit complete.

SQL> grant read, write on directory D1 to XX;

Grant succeeded.

PL/SQL procedure successfully completed.

See also: Berx Blog