firebird user with granted privileges cannot access tables

Question

I created a Firebird user (PIPPO) via jaybird, follows 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 Firebird DB and granted 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>

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

the problem is that whenever I try to run a query I got the message:

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

If I grant directly the TABLE to the 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 Gianluca.

Answer 1

In Firebird the rights assigned to a role are only applied when that role is specified when connecting to the database. In other words if a user has a role, that user does not automatically get the rights of that role. The user needs to explicitly indicate the role to use, otherwise only the rights assigned to PUBLIC and the user itself apply.

For ISQL the CONNECT specification is:

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) quotes are treated case sensitive. So using role 'gesalldb_user' will not match a role GESALLDB_USER, while role gesalldb_user will. This is like the rules for other double quoted objectnames (like table and columnnames) in Firebird.

This also applies when using a driver or access component, but the exact configuration and property name might vary (eg for Jaybird the property is roleName or sqlRole).