Subject RE: [firebird-support] Unexpected privileges on a table via a view
Author Brian Dunstan
Post date 2017-11-12T19:16:15Z

Thanks Mark,

 

Are you saying that if SYSDBA creates a view on a table, then grants SELECT to the view to PUBLIC, then everyone has access to the view and the data in the table, without a further GRANT to allow the VIEW to SELECT from the table?

 

I guess I can understand that on the basis that the same OWNER has created the table and the view, it’s just not the way I thought it worked.

 

That would mean that if SYSDBA creates all the tables and views, then I don’t need to GRANT any privileges on tables for views, because SYSDBA owns all the objects. I’ll just need to GRANT privileges to users.

 

Our DB, which has been migrated many times from the 1990’s from Interbase through various versions of Firebird, is full of SELECT grants to views on tables. I’m now finding that some of our complex views are failing under Firebird 3.0 with permissions errors. Could unnecessary GRANTS actually be upsetting things?

 

Thanks for your response Mark.

 

Brian

 

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Sunday, 12 November 2017 11:09 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Unexpected privileges on a table via a view

 

 

On 12-11-2017 10:17, Brian Dunstan brian@... [firebird-support]
wrote:

>
>
> Hi,
>
> I’m running Firebird 3.0, dialect 3.
>
> As SYSDBA I’ve created a table and a view, thus:
>
> *create table tab_a (x integer, y integer, z integer);*
>
> **
>
> *create or alter  view test_view (x, y, z)*
>
> *as select a.x, a.y, a.z from tab_a a;*
>
> **
>
> *grant select on test_view to public;*
>
> Connect as an ordinary unprivileged user without a role:
>
> *Select * from tab_a*
>
> Fails as expected “no permission for SELECT access to TABLE TAB_A”
>
> But
>
> *Select * from test_view returns the data from TAB_A.*
>
> **
>
> No privileges have been granted on the table for the view and the user
> has no privileges on the table, so why can the view access the table?
>
> Previous experience (with Firebird 2.5 and dialect 1) has been that a
> view must be *granted* *select* privileges on a table before it is
> available, but Firebird 3.0 dialect 3 seems to be behaving differently.
> I’d really appreciate it if someone can point out what has changed or
> what I am misunderstanding.

It works exactly the same under Firebird 2.5. Maybe you are thinking of
a case where the view is created by a user without rights on tab_a?

Mark
--
Mark Rotteveel