<Herbert.Augustiny@...> wrote in message
news:OFA535F38F.A1BD0712-ONC1256B88.004ABE1D-C1256B88.004B3B46@.....
.

>
> I did change the user in rdb$relations and when I do a isql -x it shows

the

> correct user name, but when I try to drop this changed table it gives me
> this error:
>
> -ERASE RDB$RELATION_CONSTRAINTS failed
> -no permission for control access to TABLE JAVA_APPLICATION_RESOURCES
>
> Where else is the owner stored?

This is AS FLAWED, sorry, I mean AS DESIGNED with a bug in the middle.

When Borland did IB4, they did several mistakes typical of a team that takes
for the first time a product that was already working. One of those mistakes
was the weak interaction between the new security model
(rdb$user_privileges) and the old security model (rdb$security_classes).

They changed the old model in some places, you can see the code they
commented in the engine. The new model is layered on top of the old one.
This causes more than a couple of hiccups. A non-security related example is
the cumbersome procedure to change the NOT NULL constraint that Martijn
knows and the silly way check constraints are stored and managed.

By not tying DSQL to GDML security in all places, Borland not only made the
maintenance of the security model a pain for themselves, but they opened
several security holes and cans of worms, some of them were fixed in FB1;
others can't be fixed simply because we do not have more room in system
tables, unless we change the names of those permissions and the way they are
generated (another can of worms, I suppose).

GDML uses more rights that DSQL. Better said, the SQL standard talks about
some (apparently) implicit and additional rights that users can or cannot
have, but they don't seem to be controllable directly even if they can be
mapped to GDML security. One of those slippery rights is CONTROL. You have
GDML control right on a db if you created it. You have control right on an
object if you created it. When you relinquish control by changing the owner,
system triggers do not react and base-level rights aren't updated.
Surprisingly, the old user still can do almost everything with its table.
What you need is:

update rdb$relations set rdb$owner_name = <new_user>
where rdb$relation_name = 'JAVA_APP';
commit;
GRANT ALL ON JAVA_APP TO USER <new_user>;

Then connect with new_user and you can drop the table. Under the scenes, ALL
grants anything to <new_user> and also, system triggers and other places
react, giving CONTROL right to <new_user> so it can delete the table.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing