0

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
nmm
  • 108
  • 7
  • It is just a theory, but your downgrade may have contained a new Firebird 3 permission that doesn't exist in Firebird 2.5. I'm not sure if that could cause a problem like this, but Firebird 3 introduced CREATE DATABASE, ALTER DATABASE and DROP DATABASE permissions. It might be best to recreate the database under Firebird 2.5 and pump the data over. – Mark Rotteveel Nov 19 '19 at 08:54
  • I would recommend asking your question on the firebird-support mailing list. – Mark Rotteveel Nov 19 '19 at 08:57
  • Thank you for advising, i did it https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/134783 – nmm Nov 19 '19 at 12:41
  • @nnm hope it was the only artifact and nothing else will pop up ever later... Since Vlad is not interested in SO, put this action as the answer for the question, there would hardly be better one – Arioch 'The Nov 21 '19 at 07:43

2 Answers2

1

Solution provided in the firebird-support mailing list: 

Attach as SYSDBA and run

UPDATE RDB$DATABASE SET RDB$SECURITY_CLASS = NULL
nmm
  • 108
  • 7
0

have you tried gbak from 2.5 to BACKUP FB 3 database?

  1. FB 3 running
  2. place gbak from 2.5 to fb 3 folder, as gbak25. Or, do as you wish, but gbak from 2.5 need to use fbclient.dll from 3.0
  3. gbak25 -b -g -v db.fdb db.fbk -user ... -pass ...

then restore this backup on 2.5.

Dmitry Kuzmenko
  • 36
  • 1
  • yes, I did on this way, with the exception that I did not put gbak from 2.5 in 3.0 folder, but i ran it from 2.5 folder ( download a zip of firebird 2.5) – nmm Nov 19 '19 at 12:08
  • also without -g switch – nmm Nov 19 '19 at 12:31
  • checked, the fbclient.dll from 2.5 was in this folder, so will try to do it with fbclient from 3.0, but it will take some days most likely. – nmm Nov 19 '19 at 12:47
  • then I think it will not work, I mean, you will get same error. In this case the only way to downgrade is to recreate database from script and pump data back. There are lot of specific stuff in 3.0 that not existed in 2.5. BLR, ACL, and other objects, that stay in the database during backup-restore. If you upgraded from 2.5 to 3.0, then for the test period you must not change anything in the 3.0 database, to be able to return back. While you started to run DDL on that database, db changed, and these changes prevent it to return to 2.5. – Dmitry Kuzmenko Nov 19 '19 at 21:35
  • yes, tested with gbak 2.5 and fbclient 3.0 result is "unable to restore database invalid request BLR at offset 2004. BLR syntax error: expected value at offset 2005, encountered 202. Error while parsing procedure pProfit's BLR. unknown ISC error 336330835." – nmm Nov 20 '19 at 00:21