Mittwoch, 16. März 2011

who owns that directory?

I'm talking here about the DIRECTORY object in Oracle, not the thing you might know from several OSes.

Most objects in a current database can be created for any user, but a DIRECTORY can't. Please check the CREATE DIRECTORY definition, there you can see Description of create_directory.gif follows
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:
select distinct owner from DBA_OBJECTS where object_type ='DIRECTORY';


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 DBA_DIRECTORIES. It's somewhat easier as DBA_OBJECTS and stil shows an owner(!) What a nice sense of humor.
FROM sys.user$ u,
sys.obj$ o,
sys.dir$ d
WHERE u.user# = o.owner#
AND o.obj# = d.obj#

For the following tests I created a DIRECTORY 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:

select owner# from sys.obj$ where obj#=42985;


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:

f utl_file.file_type;
f := utl_file.fopen('D1', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text');
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 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-29289

Ok. 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:
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!


This post was created out of some older documents and my holey brain. But the main theme should be clear.

2 spfiles - some confusion

At the moment I'm in a big migration project from 9i to 11gR2.
Most of the migrations are done like
  • create new DB

  • exp/imp

  • delete old DB

Even we have templates for all these steps, sometimes something goes wrong.

Today I hit such a something:
As we use Clusters for most of our DBs, cluster_database=TRUE should be set, otherwise it's generating problems. At the Creation of one DB, the 2nd instance did not come up. After some research I found out cluster_database was set to FALSE. No big deal, let's change it:
alter database set cluster_database=TRUE scope=spfile;
But after a srvctl stop instance -d DB_UNIQUE_NAME -i INSTANCE_NAME

srvctl start instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
a show parameter cluster_database still gave me FALSE. This confused me!
So I checked $ORACLE_HOME/dbs/initINSTANCE_NAME and there I found spfile=+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891 +ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153 there where 2 spfiles in the pfile-definition!
After a

create pfile='/tmp/pfile1' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891';
create pfile='/tmp/pfile2' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153';

and a diff /tmp/pfile1 /tmp/pfile2 I decided to use only the 1st spfile in the $ORACLE_HOME/dbs/initINSTANCE_NAME and apply all the differences manually.
Now both instances are up and fine - I just have to delete the correct spfile in ASM.