Subject Re: [firebird-support] Problems with granting privileges (bug?)
Author Helen Borrie
At 10:11 AM 13/01/2005 +0000, you wrote:

>We've got a customer's database (FB 1.0) where I can't GRANT
>table privileges to another user. Though I am the owner of that table
>(not SYSDBA).
>Shouldn't it be that the owner of a database object may GRANT to
>anyone he likes to grant to?

No, it's not a bug. Changing the owner of the database doesn't change the
owner of the objects in it.

Your problem is typically going to happen where the database started life
created and owned by the sysdba and, later, a backup was restored with a
different owner. Sysdba therefore still owns the objects.

>Over the last months/years we had several (well: a few) databases like
>that. But in those cases the following script helped.
>Unfortunately not in this case (error trying to GRANT to the object's
>owner, being connected as the object's owner).

Check the ownership by querying

select rdb$relation_name, rdb$owner_name from rdb$relations
[where rdb$relation_name = 'PERPLEXING']

If you want to, you can get past this problem by creating a script, to be
run by the SYSDBA, granting all privileges on all objects WITH GRANT OPTION
to the database owner.

Alternatively, write a stored procedure, to be run by sysdba one time only,
changing the rdb$owner_name of each user table (where rdb$relation_name not
starting with 'RDB$'). Remember to put the new name explicitly in upper
case. For heaven's sake, do this on a copy of the database and test

Don't overlook procedures - they also have owners.

Afterwards, you are probably still going to find some privileges that you
need to change. You can query rdb$user_privileges to find out what's needed.