Search code examples
delphifirebirdfiredac

error: no permission for select access to view

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.

Solution

  • 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.