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

  Template script for changing ownership of all non-system tables,
  views and procedures owned by OLD_OWNER to NEW_OWNER.

DESCRIPTION
  This script updates Interbase system tables in order to hand over
  ownership of tables, views and procedures. Note that you might want
  to add an update statement to change ownership of OLD_OWNER's ROLES.

  Note that you can do most of this script using a series of GRANT and 
  REVOKE statements. The update statements at the end could also be 
  achieved by normal DML and DDL operations, but this is much simpler.
*/

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

/*  Copy OLD_OWNER's privileges 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 IN
      (SELECT rdb$relation_name FROM rdb$relations
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$relation_name NOT LIKE 'RDB%')
    OR  rdb$relation_name IN
      (SELECT rdb$procedure_name FROM rdb$procedures
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$procedure_name NOT LIKE 'RDB%') );

/* Copy granted privileges as privileges 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 IN
      (SELECT rdb$relation_name FROM rdb$relations
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$relation_name NOT LIKE 'RDB%')
    OR  rdb$relation_name IN
      (SELECT rdb$procedure_name FROM rdb$procedures
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$procedure_name NOT LIKE 'RDB%') );

/* Delete OLD_OWNER's granted privileges */
DELETE FROM rdb$user_privileges
  WHERE rdb$grantor = 'OLD_OWNER'
  AND ( rdb$relation_name IN
      (SELECT rdb$relation_name FROM rdb$relations
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$relation_name NOT LIKE 'RDB%')
    OR  rdb$relation_name IN
      (SELECT rdb$procedure_name FROM rdb$procedures
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$procedure_name NOT LIKE 'RDB%') );

/* Change owner of selected tables and views */
UPDATE rdb$relations
  SET rdb$owner_name = 'NEW_OWNER'
  WHERE (rdb$relation_name IN
      (SELECT rdb$relation_name FROM rdb$relations
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$relation_name NOT LIKE 'RDB%')
    OR  rdb$relation_name IN
      (SELECT rdb$procedure_name FROM rdb$procedures
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$procedure_name NOT LIKE 'RDB%') );

/* Change owner of selected procedures */
UPDATE rdb$procedures
  SET rdb$owner_name = 'NEW_OWNER'
  WHERE (rdb$procedure_name IN
      (SELECT rdb$procedure_name FROM rdb$procedures
        WHERE rdb$owner_name = 'OLD_OWNER'
        AND rdb$procedure_name NOT LIKE 'RDB%') );

COMMIT;
/* end of script */