I'm talking here about the DIRECTORY object in Oracle, not the thing you might know from several OSes.
No optional
schema. anywhere, as you can see with tables, indices and similar objects. But that's not the end of the story. There is a view which shows you all the objects in a database:
DBA_OBJECTS. And there is a owner. allways! So let's check all the owners of directory objects there:
Now let's give user berx the ownership of that directory.
back as user berx - same testcase ....
-- still
select distinct owner from DBA_OBJECTS where object_type ='DIRECTORY';
OWNER
------------------------------
SYS
As there is a owner defined somewhere, we also can change it. It's only a question of effort.
Unfortunately there is no
ALTER DIRECTORY between ALTER DIMENSION and ALTER DISKGROUP. But it is still defined somewhere, so it can be changed! Now I'm leaving the well prepared area of Oracle Documentation, but that just increases the fun.
Let's start with the definition of
For the following tests I created a DIRECTORY
DBA_DIRECTORIES. It's somewhat easier as DBA_OBJECTS and stil shows an owner(!) What a nice sense of humor.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#
D1 with the os-path /tmp/d1.Checking sys.dir$ I found obj# 42985 for this directory.
Just to make sure, jet's check the owner of that object. I don't trust oracle, there is too much hardcoded magic inside. But not at this point:
Now I create another user and try to give him the ownership of this particular object - without any grants. The owner of an object should be able to access the object anyhow.
select owner# from sys.obj$ where obj#=42985;
OWNER#
----------
0
Now I create another user and try to give him the ownership of this particular object - without any grants. The owner of an object should be able to access the object anyhow.
The user berx is equiped with
CONNECT and CREATE PROCEDURE:With user berx let's try to access that DIRECTORY:
declareYou will expect something like
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;
/
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 berx the ownership of that directory.
In my case user berx has
user$ 41.So I can (as sys)
update sys.obj$ set owner#=41 where obj#=42985;
back as user berx - same testcase ....
-- still
ORA-29289Ok. Now we know Oracle does not check the owner of that particular object to check permissions. One of their shortcuts in the code.
So let's give poor user berx the GRANTs the ordinary way:
SQL> grant read, write on directory D1 to berx;
grant read, write on directory D1 to berx
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
Not the effect you expected? Well, maybe one of the side-effects of my unkind interaction with the data dictionary and Oracles shortcuts.
But as I know what I did before, I can revert all the things:
And with a last test as user berx:
Everything is fine again!
SQL> update sys.obj$ set owner#=0 where obj#=42985;
1 row updated.
SQL> commit;
Commit complete.
SQL> grant read, write on directory D1 to berx;
Grant succeeded.
And with a last test as user berx:
PL/SQL procedure successfully completed.Everything is fine again!
