Hi Nando,

> TS> There is more involved as shown above.
>
> Can you (or anyone else) elaborate, or point to sources of
> information?

The following is an old how-to I've cooked up while
developing FBOM. Not sure if it is up to date. There
also might be an easier way.

1) Backup with <SRC_USER> and Restore with <DEST_USER>

2) Get different owner names for Relations (Tables/Views),
Stored Procedures and Roles

select rdb$owner_name from rdb$relations where rdb$system_flag = 0 or rdb$system_flag is null
union
select rdb$owner_name from rdb$procedures
union
select rdb$owner_name from rdb$roles

3) Loop over the <OWNER_LIST> and create them, if they
doesn't exist

4) Change the ownership of relations from members of
<OWNER_LIST> to <DEST_USER>

- Connect with each old relation owner and execute:

update rdb$relations set rdb$owner_name = '<DEST_USER>' where rdb$owner_name = '<OLD_USER>';

5) Change the ownership of stored procedures from
members of <OWNER_LIST> to <DEST_USER>

- Connect with each old stored procedure owner and execute:

update rdb$procedures set rdb$owner_name = '<DEST_USER>' where rdb$owner_name = '<OLD_USER>';


6) Change the ownership of roles from members of
<OWNER_LIST> to <DEST_USER>

- Connect with each old role owner and execute:

update rdb$roles set rdb$owner_name = '<DEST_USER>' where rdb$owner_name = '<OLD_USER>';


7) Delete all priviliges from members of <OWNER_LIST>

- Connect with each old owner and execute:

delete from rdb$user_privileges where rdb$grantor = '<OLD_USER>';


***CONNECT WITH <SRC_USER> AND PROCEED***

8) Loop through all user relations (exluding system tables)
and grant all rights with grant option for <DEST_USER>

grant all on <RELATION_NAME> TO <DEST_USER> WITH GRANT OPTION;

9) Loop through all stored procedures and grant execute
rights with grant option for <DEST_USER>

grant execute on <PROCEDURE_NAME> TO <DEST_USER> WITH GRANT OPTION;

***CONNECT WITH <DEST_USER> AND PROCEED***

10) Loop through all user relations (exluding system tables)
and grant all rights with grant option for <DEST_USER>

grant all on <RELATION_NAME> TO <DEST_USER> WITH GRANT OPTION;

11) Loop through all stored procedures and grant execute rights
with grant option for <DEST_USER>

grant execute on <RELATION_NAME> TO <DEST_USER> WITH GRANT OPTION;

***CONNECT WITH <SRC_USER> AND PROCEED***

12) Delete all priviliges granted by <SRC_USER>

delete from rdb$user_privileges where rdb$grantor = '<OLD_USER>';

13) Backup with <DEST_USER> and Restore with <DEST_USER>

14) Optional: Exclude given user names from the database
by creating an equal called role.

For all given user names execute:

insert into rdb$roles values ('<USER_NAME>', '<DEST_USER>');

> TS> You could give our FBOwnerMigrator utility a try. Let me
> TS> know if you are interested.
>
> Didn't know such a tool existed.

We don't have an official download, as it hacks the
system tables directly.

> Well, the procedure needs to be "embeddable" for me to be
> able to use it. Does this tool support automatic invocation or such?

FBOwnerMigrator is a stand-alone windows application.


Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com