error: no permission for select access to view

Question

I have a Firebird 3 database. I created a new user, NATO. Then I created a new role, Cash. On database tables this role has permissions. Query, that contains these tables, works fine, but when I create view (v_goods) from these tables, it has no permissions and I get error:

no permission for select access to view v_goods.

CREATE USER NATO;
CREATE ROLE CASH;
GRANT CASH TO NATO;
GRANT SELECT ON TABLE V_GOODS TO CASH; 

 Connection1.DriverName :='FB';
 Connection1.Params.Add('port=3050');
 Connection1.Params.Add('protocol=TCPIP');
 Connection1.Params.Add('CharacterSet=UTF8');
 Connection1.Params.Add('Server= SERVER-EDEL');
 Connection1.Params.Add('Database=D:\DirectDll\Base_TMG.fdb');
 Connection1.Params.Add('User_name=NATO');
 Connection1.Params.Add('Password=***');
 Connection1.Params.Add('sql_role_name=Cash'); //+USER_Role);
 Connection1.Connected := True;

 v_Goods.Connection:=Connection1;
 v_Goods.Open;

If I grant permissions on view to special user PUBLIC

GRANT SELECT ON TABLE V_GOODS TO CASH

then view works fine but in this case every authenticated user (with this role or not) may open view. If I revoke permissions from user PUBLIC then I get this error.

Are you sure `sql_role_name` is the right property? For example, the FireDAC documentation specifies `RoleName`. What is shown when you execute `select CURRENT_ROLE from RDB$DATABASE` from your Delphi application? Also which database component/library are you using? — Mark Rotteveel, Aug 26 '21 at 06:53
@MarkRotteveel you are right. After changing "sql_role_name" with "RoleName" everything works properly. Thank you very much!!! P.S. I'm using FireDac component FDConnection. — basti, Aug 26 '21 at 09:35
@MarkRotteveel Could you post this as answer so I can vote it up?! — basti, Aug 26 '21 at 09:42

score 2 · Accepted Answer · answered Aug 26 '21 at 11:15

According to the FireDAC documentation, the appropriate property for the role is RoleName, not sql_role_name. So, likely you currently do not have a role specified.

You can verify this by executing select CURRENT_ROLE from RDB$DATABASE from within your Delphi application.

error: no permission for select access to view

1 Answers1