This article is more about transferring active user accounts on a server to a workstation, however, the same principle applies to a backup of user accounts. Why do?
With the user accounts on our workstation we can change all of their real passwords to say, masterkey and working with them and testing real permission environments on tables, triggers, procedures and any other objects we have to GRANT. In addition, we can have the backup of the accounts, just in case of an accident and need to recover it again.
Another real scenario is when we are migrating from, for example, FirebirdSQL3 to FirebirdSQL4. Our following example will be based on this scenario.
BACKUP USER ACCOUNTS IN PRODUCTION
To run the backup of “security3.fdb” you need to be using the administrator account and open the terminal with it, then you can access the FirebirdSQL folder which is protected by administrative permissions and then run:
set path=%path%;"C:\Program Files\Firebird\Firebird_3_0" gbak -v -b -t -user sysdba "C:\Program Files\Firebird\Firebird_3_0\security3.fdb" c:\temp\security3.fbk
In the example above, we made the backup. If this is your goal, now just keep the file “c:\temp\security3.fbk” in a safe place. Note in the example above that we didn't use gbak's -password PRODUCT PASSWORD parameter, why not? It is not necessary because the access in this case is embedded.
RESTORING THE BACKUP OF USER ACCOUNTS
Either for any of the scenarios I mentioned at the beginning, to restore the database we will need the gbak of the desired version and obviously from the backup file:
set path=%path%;"C:\Program Files\Firebird\Firebird_3_0" cd "C:\Program Files\Firebird\Firebird_3_0" ren security3.fdb security3.fdb.ori gbak -v -r -user sysdba c:\temp\security3.fbk "C:\Program Files\Firebird\Firebird_3_0\security3.fdb"
Backup of user accounts has been restored!
IMPORTANT: If you are bringing production user accounts to the development environment you will need to change the SYSDBA password for security reasons, imagine the catastrophe that would be for you to enter the production base thinking you are entering the development base.
CHANGING SYSDBA PASSWORD
As of now, you have restored user accounts but want to change SYSDBA password, how to do? It's simple, first we'll need to connect to the security3.fdb base:
set path=%path%;"C:\Program Files\Firebird\Firebird_3_0" isql.exe -user SYSDBA "C:\Program Files\Firebird\Firebird_3_0\security3.fdb"
Then we will execute this sequence of commands changing not only the SYSDBA user's password, but if it is in a development environment, also yours and the users who want to simulate an action within the database:
alter user SYSDBA password 'masterkey'; -- altere outras senhas se preferir, por exemplo, equipe de desenvolvimento: -- afinal, não é bom que a equipe de desenvolvimento tenham as mesmas senhas -- em produção e também desenvolvimento: alter user MINHACONTA password 'masterkey'; alter user DEV_USER1 password 'masterkey'; alter user DEV_USER2 password 'masterkey'; commit; quit;
RESTORING ACCOUNT BACKUP IN FIREBIRD 4
The restore procedure is basically the same, what differs is the directory of FB4 which is different from FB3:
set path=%path%;"C:\Program Files\Firebird\Firebird_4_0" cd "C:\Program Files\Firebird\Firebird_4_0" ren security4.fdb security4.fdb.ori gbak -v -r -user sysdba c:\temp\security3.fbk "C:\Program Files\Firebird\Firebird_4_0\security4.fdb"
The above principle will work for any database upgrade.
GOOD PRACTICES WITH BACKUP/RESTORE
It is imperative that you NEVER mix development passwords with production passwords, whether in one bank or the other. When it is necessary to bring a base to the development environment, the first thing to do is change the passwords contained therein.
Don't use the -rep option to restore a backup over the existing database, be cautious, do as I showed above, rename the old file to .bak and proceed with your restore.
The server is not “Maria Joana's house”, inexperienced people should not have access to the administrator account or any production server, so the backup/restore procedure must be performed by someone experienced, perhaps pragmatic when the situation is to do things with caution and dexterity.