I'm getting error "no permission for read/select access to DATABASE" when I try to connect to a Firebird database with a user different than SYSDBA, lets say user SOM.
If I create a new database with SYSDBA on the same server, then grant access to the objects there to SOM, it is connecting without problem, but for this particular database - not. I tried to give all rights, including to the system objects to SOM (I'm using the great IBExpert where this is possible), no success.
c:\Program Files\Firebird\Firebird_2_5\bin>isql -user SOM -password secret
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect "c:\data\data.fdb";
Database: "c:\data\data.fdb", User: SOM
SQL> select * from exampletable ;
Statement failed, SQLSTATE = 28000
no permission for read/select access to DATABASE
Command error: select * from exampletable
I was unable to find way to grant anything to object DATABASE.
What could be the reason is that the problematic database was restored via gbak, version 2.5 from Firebird 3 database (downgrade on this way from Firebird 3 to 2.5).
A possible solution is to restore the database with user SOM, but I don't want that the user SOM is the owner of the database. If I do a backup and restore with SYSDBA, the same problem comes back again.
Solution provided in the firebird-support mailing list:
Attach as SYSDBA and run
UPDATE RDB$DATABASE SET RDB$SECURITY_CLASS = NULL