No permission for EXECUTE ACCESS TO PROCEDURE in Firebird DB

Question

I followed the specs from the manual:

1. I created a role: CREATE ROLE reader;.
2. The user was created: CREATE USER voron PASSWORD 'somepassword'.
3. Then for to give permissions I did the following:

GRANT SELECT ON exportit TO PROCEDURE sp_get_itexport_tags;
GRANT EXECUTE ON PROCEDURE sp_get_itexport_tags TO ROLE reader;
GRANT reader TO USER voron;

I also tried 'granting' to PUBLIC user, but it gives me the following error: no permission to EXECUTE access to PROCEDURE sp_get_itexport_tags. Effective user is VORON. I gave those permissions as SYSDBA. Logged on using role reader and user's password. What's wrong?

Answer 1

I'm am unable to reproduce (using ISQL against Firebird 4.0.4):

create database 'e:\db\fb4\check_rights.fdb' user sysdba;
create role reader;
set term #;
create procedure test_1 as begin end#
set term ;#
grant execute on procedure test_1 to role reader;
grant reader to user voron;
commit;

And then without the role:

connect 'e:\db\fb4\check_rights.fdb' user voron;
execute procedure test_1;

This will (as expected) result in error:

Statement failed, SQLSTATE = 28000
no permission for EXECUTE access to PROCEDURE TEST_1
-Effective user is VORON

With role, the procedure executes just fine:

connect 'e:\db\fb4\check_rights.fdb' user voron role reader;
execute procedure test_1;

This reports no error (the procedure does nothing useful, so there is no output).

My guess is that you didn't specify the role, or you specified the role in double quotes, so it becomes case-sensitive (in that case, select CURRENT_ROLE from RDB$DATABASE reports NONE instead of READER). Alternatively, you didn't commit your grant statements, or, because you're missing the procedure name in the grant shown in the question, you received an error which you ignored, and there is no actual permission granted.

You can also check which privileges have been granted:

select RDB$USER, RDB$RELATION_NAME, RDB$PRIVILEGE 
from RDB$USER_PRIVILEGES 
where RDB$RELATION_NAME = 'TEST_1' and RDB$OBJECT_TYPE = 5

Replace TEST_1 with the actual procedure name. This should report privilege X (eXecute) granted to READER.

To check if the role has been granted, use

select RDB$USER, RDB$RELATION_NAME, RDB$PRIVILEGE 
from RDB$USER_PRIVILEGES 
where RDB$USER = 'VORON'

This should report "relation" READER and privilege M (Member).