ContactSitemapProduktenInfoWie zijn wijWelkomStartDownload


Interbase Script - Change Relation Owner

Marco Bommeljé 22 aug 2000
[download] [email]

Once I had changed the database ownership, doing the same for the ownership of tables, views and procedures seems easy. This script shows the steps involved. It works, but it is better to think about security issues at an early stage of development and avoid having to do these awkward things.
 

/*
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 */


(C) - Last updated: