How to view the contents of a Firebird 2.5 database

I would like to view the contents of a .fdb file and hopefully transfer them to an Excel format, this file is from another computer. I have installed Firebird 2.5 and I have installed FlameRobin

And inside FlameRobin I did Database > Register Existing Database, with username = SYSDBA and password = masterkey

But I get an error:

"Your login SYSDBA is same as one of the SQL role name. Ask your database Administrator to set up a valid Firebird login"

I have done enough reading to know that this is supposedly a trick as Firebird 2.5 don't have user. According to this old post. I supposedly have to use IBexpert or IBsurgeon FirstAid, but it is not clear on how to use them. I have downloaded and installed them. I'm supposed to find a table named RDB$ROLES, but I could not find them.

Anyone can help me with a better method?

This database was "protected" from being accessed by the SYSDBA user by creating a role with the same name.

Be aware that the following steps do come under the assumption that nothing was done to prevent a normal user from selecting from the RDB$ROLES table. Otherwise, you will need to use other means to find the right user (e.g. from the application code, tracing the connection), or need a database repair tool to remove/overwrite the role, or inspect the file itself (which does require a lot of knowledge about the internals).

To be able to access it, you will need to create a new user.

Creating a new user

In Firebird 2.5, there are basically two ways to create new users:

1. Connect to another database with SYSDBA (or another user with the RDB$ADMIN role). For example, using ISQL, connect to the employee database which is included in standard Firebird installations(*):

   Start ISQL (substitute masterkey with the actual password):

   isql -user sysdba -password masterkey localhost:employee
   

   Inside ISQL (substitute thenewuser and thepassword with desired values):

   create user thenewuser password 'thepassword';
   commit;
   exit;
   

2. Use GSEC to create a user (note: GSEC has been deprecated in Firebird 3.0):

   Start GSEC (substitute masterkey with the actual password):

   gsec -user sysdba -password masterkey
   

   Inside GSEC (substitute thenewuser and thepassword with desired values):

   add thenewuser -pw thepassword
   

This user will not be able to do much, but it should be able to inspect the metadata tables of the database.

Find the user needed to drop the role

You should be able to login into to the database (substitute thedatabase with the actual path or alias of the database) with the user created previously:

isql -user thenewuser -password thepassword localhost:thedatabase

Then query who "owns" the role SYSDBA:

select RDB$OWNER_NAME from RDB$ROLES where RDB$ROLE_NAME = 'SYSDBA';

NOTE: If the RDB$OWNER_NAME is not a valid regular identifier (starts with A-Z, rest is A-Z,0-9 or _ or $), you will need to identify users with the RDB$ADMIN privilege:

select RDB$USER from RDB$USER_PRIVILEGES where RDB$RELATION_NAME = 'RDB$ADMIN' and RDB$OBJECT_TYPE = 13;

As a last resort, try to identify the database owner:

select distinct RDB$OWNER_NAME as DATABASE_OWNER
from RDB$RELATIONS
where RDB$SYSTEM_FLAG = 1;

Now, create a new user with the owner name of the role or one of the users with the RDB$ADMIN privilege using the steps described in "Creating a new user" (in the following steps, I use the username BROKEN_LOCK).

If the user already exists, change their password with:

alter user BROKEN_LOCK set password 'thepassword';

Drop the SYSDBA role

Access the database with ISQL and the user that owns the role (or is an admin) as found in the previous step:

isql -user broken_lock -password thepassword -role RDB$ADMIN localhost:thedatabase

The option -role RDB$ADMIN is only needed for the users with the RDB$ADMIN privilege, but is silently ignored for users that don't have that role.

Inside ISQL, drop the role:

drop role SYSDBA;
commit;
exit;

You should now be able access the database with SYSDBA.

(*): If you don't have any other databases on your system, you will need to create one first (or use the GSEC route).

Start ISQL:

isql

And in ISQL (substitute C:\Databases\mydatabase.fdb with a valid path, and masterkey with the actual password):

create database 'C:\Databases\mydatabase.fdb' user 'sysdba' password 'masterkey';

Then use the database path instead of employee to create the users.