This is an old blog post dated 2015-03-17 from my original Blogger account. System configurations and available software versions changed since then but I think it still works on modern systems, maybe a few modifications have to be made to suit your needs.
Assume you are a system administrator and you have forgotten your username or password so you have no longer access to your database. You don't have any other access to your database, you cannot reset the SYSDBA user's password because other people or applications are using it and you don't have access to them either but you really need access to certain records quickly. How annoying is that? Luckily, there is a solution for this problem but we also have to assume that we can shut down the database server for a short time.
- Operating system: Windows
- Database server: Firebird 2.5
- Database client: FlameRobin and gsec program
We are going to use another, temporary instance of the database server, where we have full access to everything, we shut down the original Firebird server for the time of process, start the temporary Firebird server and access to our databases using that server. We also take that we have to access those databases regularly before we can fix our original database server the proper way.
Let's call the database we are trying to access 'Test Database' and its file is located:
- Download and extract the ZIP edition of Firebird 2.5 database server;
- Shut down the original Firebird services;
- Run Windows console as system administrator, go to the extracted ZIP version of Firebird server, enter bin subfolder and start the server with the following command:
- Open FlameRobin client and create a new connection with the appropriate parameters specified in firebird.conf located in the main directory (if you haven't changed anything in that file then you can use localhost in the Hostname field and leave the Port number field empty):
- Now we need to create a new administrative user within the original Firebird database server. To do this we use the gsec program in the temporary Firebird server. Run the following command in the bin directory:
gsec -user sysdba -pass masterkey -database "localhost:<FIREBIRD_FOLDER>\security2.fdb" -add <NEW_USER> -pw <PASSWORD> -admin yes
where <FIREBIRD_FOLDER> is the absolute path of your original Firebird database server (C:\Program Files\Firebird\Firebird_2_5 in my case), <NEW_USER> is the username of your new administrative user (admin2 in my case) and <PASSWORD> is the password of this new user (simply admin2 in my case);
- Go back to FlameRobin client and register the database you are trying to access:
The Display name field can be anything you like (Test Database in my case), the Database path field is the absolute path of your database you are trying to access (C:\Program Files\Firebird\Firebird_2_5\databases\TESTDB.FDB in my case), the Authentification mode should be the 'Use saved user name and password' item, the User name is SYSDBA and the password is masterkey (both is the default in the temporary Firebird server), the Charset is the character set in your database, the Role should be admin. Save this information, then double-click on the new database item. Now the system should be able to connect the database.
- Use FlameRobin client to grant permissions to the new database user on the database elements you need access to. Don't forget to commit all transactions. Now the new user should have access to the desired database elements.
- Disconnect, close FlameRobin, stop the temporary Firebird server and restart the original database server. Now you should have access to your database using the new user name and password without killing the other connections.
This tutorial works only when you physically have the target databases on your computer and have all administrative rights on your operating system;
I don't really know if this is a security hole in the database server or is a normal backdoor to regain control on your system. Normally, you should not be able to have access to your databases without knowing the appropriate user name and password or replacing the security database with the default one. In this case, we used another instance of the database server after shutting down all original services. Other database servers (for example: PostgreSQL) may fail to access a foreign database without registering the tablespaces and databases on them properly on the administrative interface, however there are other (official) methods for accessing a database where you cannot sign in. Don't blame me if this tutorial will not work after a few months.