CODEWITHSUNDEEP
sqlfirebirdsql-viewsql-grant
dsungaro
dsungaro

Reputation: 158

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

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?

Upvotes: 1

Views: 1548

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109265

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.

Upvotes: 2

Arioch &#39;The
Arioch &#39;The

Reputation: 16125

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

Upvotes: 3

Related Questions