Library

How to migrate (copy) users from security2.fdb (Firebird 2.5) to security3.fdb (Firebird 3.0)

In order to copy users accounts (names, passwords, etc) from Firebird 2.5 security2.fdb to security3.fdb in Firebird, copy all records from RDB$USERS (security2.fdb) to PLG$USERS (security3.fdb).

The easiest way to do it is to restore security2,fdb under Firebird 3, and then connect to security3.fdb through embedded connection and run the SQL script below (modify the path to the restored security2.fdb from localhost:D:\fb\data\security2.fdb to the actual path).

This script will connect to the restored database from 2.5 and copy all users data to the security3.fdb and, if Legacy Authorization is turned on in Firebird 3 configuration), you will be able to connect to the database with the same credentials as in 2.5.

connect security.db user sysdba;

set term ^;

EXECUTE BLOCK
AS
    DECLARE VARIABLE FRST VARCHAR(32);
    DECLARE VARIABLE MDDL VARCHAR(32);
    DECLARE VARIABLE LST  VARCHAR(32);
    DECLARE VARIABLE USR VARCHAR(128);
    DECLARE VARIABLE PASSWD VARCHAR(64);
    DECLARE VARIABLE ATTR VARCHAR(4096);
    DECLARE VARIABLE UID  INT;
    DECLARE VARIABLE GID  INT;
    DECLARE VARIABLE SQL  VARCHAR(4096);

BEGIN
  FOR EXECUTE STATEMENT '
      SELECT
          RDB$USER_NAME,
          RDB$PASSWD,
          RDB$FIRST_NAME,
          RDB$MIDDLE_NAME,
          RDB$LAST_NAME,
          RDB$UID,
          RDB$GID
      FROM
          RDB$USERS
      WHERE RDB$USER_NAME IS NOT NULL
        AND UPPER(RDB$USER_NAME) != ''SYSDBA''
      '
      ON EXTERNAL DATA SOURCE 'localhost:D:\fb\data\security2.fdb'
      AS USER 'SYSDBA' password 'masterkey' 
      INTO :USR,
           :PASSWD,
           :FRST,
           :MDDL,
           :LST,
           :UID,
           :GID

  DO
  BEGIN
    SQL = '
    INSERT INTO PLG$USERS(
       PLG$USER_NAME,
       PLG$PASSWD,
       PLG$FIRST_NAME,
       PLG$MIDDLE_NAME,
       PLG$LAST_NAME,
       PLG$UID,
       PLG$GID)
    VALUES(';
    SQL = SQL || '''' || USR || '''';
    SQL = SQL || ',''' || PASSWD || '''';
    SQL = SQL || ',' || COALESCE('''' || FRST || '''', 'NULL');
    SQL = SQL || ',' || COALESCE('''' || MDDL || '''', 'NULL');
    SQL = SQL || ',' || COALESCE('''' || LST || '''', 'NULL');
    SQL = SQL || ',' || COALESCE(UID, 'NULL');
    SQL = SQL || ',' || COALESCE(GID, 'NULL');
    SQL = SQL || ')';
    EXECUTE STATEMENT SQL;
  END
END^

set term ;^

commit;

exit;