/*
Script: ChangeDatabaseOwner.sql
Schema: NONE
Module: N.A.
Author: Marco Bommeljé
Date:   2000-09-22

  Template script for changing ownership of a database from OLD_OWNER
  to NEW_OWNER.

DESCRIPTION
  This script updates Interbase system tables in order to hand over
  ownership of a database.

*/

CONNECT 'c:\projects\data\example.gdb'
  USER 'SYSDBA' PASSWORD 'masterkey';

/*  Copy the DBO's privileges on RDB$ROLES to NEW_OWNER */
INSERT INTO rdb$user_privileges
  (rdb$user, rdb$grantor, rdb$privilege, rdb$grant_option,
  rdb$relation_name, rdb$field_name, rdb$user_type, rdb$object_type)
SELECT 'NEW_OWNER', 'NEW_OWNER', rdb$privilege, rdb$grant_option,
  rdb$relation_name, rdb$field_name, rdb$user_type, rdb$object_type
  FROM rdb$user_privileges
  WHERE rdb$grantor = 'OLD_OWNER'
    AND rdb$user = 'OLD_OWNER'
    AND rdb$relation_name = 'RDB$ROLES' ;

/*  Copy other users' privileges on RDB$ROLES with NEW_OWNER as grantor */
INSERT INTO rdb$user_privileges
  (rdb$user, rdb$grantor, rdb$privilege, rdb$grant_option,
  rdb$relation_name, rdb$field_name, rdb$user_type, rdb$object_type)
SELECT rdb$user, 'NEW_OWNER', rdb$privilege, rdb$grant_option,
  rdb$relation_name, rdb$field_name, rdb$user_type, rdb$object_type
  FROM rdb$user_privileges
  WHERE rdb$grantor = 'OLD_OWNER'
  AND rdb$user <> 'OLD_OWNER'
  AND rdb$relation_name = 'RDB$ROLES';

/*  Revoke DBO's privileges from RDB$ROLES */
DELETE FROM rdb$user_privileges
  WHERE rdb$grantor = 'OLD_OWNER'
  AND rdb$relation_name = 'RDB$ROLES';

/*  Change ownership of catalog tables */
UPDATE rdb$relations
  SET rdb$owner_name = 'NEW_OWNER'
  WHERE rdb$relation_name LIKE 'RDB$%';

COMMIT;
/* end of script */