Connect to Firebird database as read-only

Question

I want to connect to a Firebird database read-only (because the location where the .fdb is read-only for my application.

I connect like this:

conn = fdb.connect(dsn="/path/to.fdb", user='****', password='****', charset='iso8859_1')

The error I am getting is:

Error while connecting to database:\n- SQLCODE: -551\n- no permission for read-write access to database /path/to.fdb', -551, 335544352

I am using FDB v2.0. I couldn't find anything in the online documentation about a read-only connection.

Answer 1

Unfortunately, what you want is not possible. Firebird doesn't have a concept of read-only connections. Every connection needs to be able to write information in the database for administration of transactions (this even applies for read-only transactions).

The only exception is for databases that have been marked as a read-only database using gfix. In that case, transactions piggyback on the last committed transaction id. However, marking a database read-only will make it read-only for all connections.

If your application needs read-only access, while other applications need write access to the same database, the only solution is to use Firebird server*, and use users (and roles) to assign sufficient rights to be able to read but not write to the database.

---

^{*: Firebird Embedded might be sufficient, but as the user then needs read/write access to the database, it would be simpler to circumvent this form of access control for malicious actors.}

Answer 2

Perhaps it's a good idea to create a user for the DB with a specific role, and use this role during further connections. Something like this:

$ isql firstdb.fdb -user SYSDBA -password masterkey
Database:  firstdb.fdb, User: SYSDBA
SQL> CREATE ROLE firstdbadmin;
SQL> GRANT SELECT, UPDATE, INSERT, DELETE ON sales_catalog
CON> TO ROLE firstdbadmin;
SQL> GRANT firstdbadmin TO TestAdmin;
SQL> quit;

Have you tried use 'role' argument, while creating connection? You may look here,here and here