Export data from InterBase / Firebird database without credentials

firebirdinterbase

I have an InterBase / Firebird database file of an old project for which I lost the credentials. Some information about the database, dumped with the gstat command, is shown below [1]. I'm accessing this file as an embedded database, i.e., without a running instance of Firebird. I'm on MacOS and I've Firebird 2.5 installed, since On-Disk Structure (ODS) 11.2 corresponds to this version.

I'm able to export the database structure using the isql command, but I'm unable to access any data using this tool without a valid username and password, resulting in the following error message:

Error while preparing SQL statement:
- SQLCODE: -551
- no permission for read/select access to TABLE FILES', -551, 335544352

Providing the default SYSDBA/masterkey as the username/password results in the error message:

Error while connecting to database:
- SQLCODE: -902
- cannot attach to password database', -902, 335544653

Since encryption was introduced in the newer Firebird 3 and the data is stored unencrypted (I can view the data with a hex editor) I'd assume there is a way to access this data without credentials and make an export. How can I do so?

[1]: Output of the gstat command

Database "./DATABASE.GDB"
Database header page information:
    Flags           0
    Checksum        12345
    Generation      26450714
    Page size       4096
    ODS version     11.2
    Oldest transaction  25058616
    Oldest active       25058617
    Oldest snapshot     25058617
    Next transaction    25058619
    Bumped transaction  1
    Sequence number     0
    Next attachment ID  1401189
    Implementation ID   26
    Shadow count        0
    Page buffers        0
    Next header page    0
    Database dialect    1
    Creation date       Jul 21, 2014 13:42:21
    Attributes      force write

    Variable header data:
    Sweep interval:     20000
    *END*

Best Answer

The second error cannot attach to password database, that is given when the username and password are provided, is due to the fact that the security database couldn't be opened.

On macOS this file is located under:

/Library/Frameworks/Firebird.framework/Resources/English.lproj/var/security2.fdb

and is owned by the firebird user by default. This causes problems when the administrative tools (isql, gsec, gfix, etc.) are ran as a different user.

One way to solve this issue is to run those command as the firebird user. Another is to change the permissions of the security database:

chmod o+r /Library/Frameworks/Firebird.framework/Resources/English.lproj/var/security2.fdb

This solved the issue and data could be accessed using the default sysdba/masterkey credentials. These are not stored in the GDB file, but in the mentioned security2.fdb file, which explains why the default credentials can be used.

Related Question