Privileged firebird user cannot access tables

Question

I created a Firebird user (PIPPO) via jaybird, after gsec "display":

  GSEC> di
      user name uid gid admin full name
 -------------------------------------------------- ----------------------------------------------
 SYSDBA 0 0 Sql Server Administrator
 PIPPO 0 0 GesAll 1.0 User
 GSEC>

I create a role (GESALLDB_USER) in the Firebird database and grant some privileges:

  SQL> show grant;

 / * Grant permissions for this database * /
 GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON ANELLI TO ROLE GESALLDB_USER

 GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON COPPIE TO ROLE GESALLDB_USER

 GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON COVE TO ROLE GESALLDB_USER
 GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON DATI_CONFIGURAZIONE TO ROLE GESALLDB_USER
 GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON DATI_COVE TO ROLE GESALLDB_USER
 GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON DATI_SOGGETTI TO ROLE GESALLDB_USER
 GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON DEPOSIZIONI TO ROLE GESALLDB_USER
 GRANT GESALLDB_USER TO PIPPO
 SQL>

provided this role to the new user via jaybird (last line before):

The problem is that whenever I try to run a request, I get a message:

  SQL> select * from anelli;
 Statement failed, SQLSTATE = 28000
 no permission for read / select access to TABLE ANELLI
 SQL>

If I provide TABLE directly to a newly created user, everything works.

SQL> grant all on anelli to pippo; SQL> show grant; /* Grant permissions for this database */ GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON ANELLI TO ROLE GESALLDB_USER GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON ANELLI TO USER PIPPO SQL> connect "C:\Users\teiluke\Documents\Ondulati\DB\prova\gesalldb.fdb" user "p ippo" password "topolino"; Commit current transaction (y/n)?y Committing. Server version: WI-V2.5.2.26540 Firebird 2.5 WI-V2.5.2.26540 Firebird 2.5/XNet (E7441EA1CA2CF4)/P12 WI-V2.5.2.26540 Firebird 2.5/XNet (E7441EA1CA2CF4)/P12 Database: "C:\Users\teiluke\Documents\Ondulati\DB\prova\gesalldb.fdb", User: pi ppo SQL> select * from anelli; 

PROGRESSIVO FEDERAZIONE RNA TIPO ANNO INIZIO FINE ATTIVA LAST_USED

=============================== ====== ====== ========== === =================== =

  1 FOI 89LR E 2012 1 100 N 0 2 FOI 89LR E 2013 1 100 S 41 

Any help on this?

Thanks to Gianluca.

Answer 1

In Firebird, the rights assigned to roles apply only when this role is specified when connecting to the database. In other words, if a user has a role, that user does not automatically receive rights to this role. The user must explicitly specify the role to be used, otherwise only the rights assigned by PUBLIC and the user will be applied.

For ISQL CONNECT specification :

 CONNECT database name [user username] [password password] [role role_name]; 

So, for your specific example, use:

 SQL> connect "C:\Users\teiluke\Documents\Ondulati\DB\prova\gesalldb.fdb" user "p ippo" password "topolino" role GESALLDB_USER; 

Role names surrounded by (single or double) quotation marks are case sensitive. Therefore, the use of role 'gesalldb_user' will not fit the role GESALLDB_USER , whereas the role gesalldb_user will. This is similar to the rules for other double-quoted names (such as tables and columns) in Firebird.

This also applies when using the driver or access component, but the exact name of the configuration and property may differ (for example, for Jaybird this is the roleName or sqlRole ).