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>

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

The problem is that whenever I try to run a query, I get 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?

Solution

In Firebird 3.0 and earlier 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).

In Firebird 4.0 and later, you can grant a role as a "default role". Privileges of default roles are automatically applied even when the role is not explicitly specified on connect.

Comments

[Gianluca Pasqualato]

Done as suggested same result: 'SQL> connect "c:\users\teiluke\documents\ondulati\DB\prova\gesalldb.fdb" user "pippo" password "topolino" role "gesalldb_user"; Server version: WI-V2.5.2.26540 Firebird 2.5 Database: "c:\users\teiluke\documents\ondulati\db\prova\gesalldb.fdb", User: pippo, Role: gesalldb_user SQL> select * from anelli; Statement failed, SQLSTATE = 28000 no permission for read/select access to TABLE ANELLI SQL> show grant; /* Grant permissions for this database */ . . . GRANT GESALLDB_USER TO PIPPO'

[Mark Rotteveel]

@GianlucaPasqualato Weird, I will try to reproduce this and see if I missed anything.

[Mark Rotteveel]

@GianlucaPasqualato I just tested it, and the role is case sensitive when surrounded by (single or double) quotes, so using role 'gesalldb_user' doesn't work, but using role 'GESALLDB_USER' or role gesalldb_user will work.

[Gianluca Pasqualato]

thanks again, it works if I run it directly from isql. If I do it with jaybird: Properties props = new Properties(); props.setProperty("user", utenteDB); props.setProperty("password", passDB); props.setProperty("role", roleDB); I printed out roleDB and its valuse is GESALLDB_USER Connection conn = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050‌​:" + DB_path,props); I got: Errore: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544352. no permission for read/select access to TABLE <table>

[Mark Rotteveel]

For Jaybird the property is roleName (and sqlRole as alias), see the list of connection properties