How to grant a user SELECT on a restricting view in Firebird

Question

I need to restrict SELECT access to a table in Firebird 3.0, so that not all columns can be read by the user.

This FAQ entry suggests to create a view with the restricted columns and then grant SELECT only on this view. However if I do that and connect with the restricted user with FlameRobin and try to select from the view, the error says I don't have SELECT rights to select from the underlying table. But If I grant access to that table, all columns can be read...

How can I grant SELECT access only to the restricting view and not to the whole underlying table?

Answer 1

Since you do not want your user to have grants on the table, then it is the VIEW which should have, (or a selectable Stored Procedure, when user permissions are regulated with SPs). The user "invokes" the VIEW (or an SP) and then the VIEW invokes the table.

GRANT SELECT ON mytable TO VIEW myview

You have to grant table read rights to the view, not to the users. Read chapter 11.2.2. Statements for Granting Privileges in Firebird documentation at https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-security-auth-manage-users