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