1

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?

dsungaro
  • 148
  • 1
  • 11
  • can you grant those table reading rights to the VIEW itself, not to the user ? – Arioch 'The Nov 02 '20 at 10:05
  • I was using `GRANT SELECT ON myview TO username;`on the view. Do you mean something like `GRANT SELECT ON mytable TO myview;` ? – dsungaro Nov 02 '20 at 10:20
  • Could it be that it's a problem that the table is owned by user A and view is owned by user B? So user C if getting granted SELECT on the view cannot access the table data? If yes: how do I change the owner of the table? – dsungaro Nov 02 '20 at 11:33
  • I do not think Firebird has an expllicit concept of "ownership" of tables/views/procedures, exposed to SQL commands level, Also, giving a user read grants on the source tables, as you noted before, would defeat the very idea of restricted rights. You have to grant table read rights to the view, not to the users. Read chapter `11.2.2. Statements for Granting Privileges` in documentation: https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-security-auth-manage-users – Arioch 'The Nov 02 '20 at 11:45
  • `GRANT SELECT ON mytable TO VIEW myview;` is the solution. Thanks a lot! Could you please post this as answer so I can vote it up and mark it as answer? – dsungaro Nov 02 '20 at 11:52

2 Answers2

3

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

Arioch 'The
  • 15,799
  • 35
  • 62
2

To be able to select from a view, you need to grant the SELECT privilege on the view to the user, however, to be able to select from the view, either the user or the view must have the SELECT privilege on the table(s) used in that view.

In this case, you don't want the user to have access to the underlying table, so you need to grant the necessary privileges to the view:

GRANT SELECT ON TABLE <sourcetable> TO VIEW <yourview>

See also Statements for Granting Privileges in the Firebird 2.5 Language Reference.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197