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