Postgres Pro
Downloads
Home   >   mailing lists

Thread: How restrict select on a view ?

How restrict select on a view ?

From
Andreas
Date:
Hi,

I'd like to have a view only to be used by certain users.
The tables are public.

Can this only be done by restricting access to the tables?

Re: How restrict select on a view ?

From
Raymond O'Donnell
Date:
On 15/12/2008 17:55, Andreas wrote:

> I'd like to have a view only to be used by certain users.
> The tables are public.

It doesn't seem to make a lot of sense to have public access to the
tables but restrict access to the views.

The usual pattern is the other way around - restrict access to the
tables, and use views to give public (or at least less restricted) to
those subsets of the data you choose.

If you have views with restricted access but leave the tables public,
the users with access to the views can bypass them and go straight to
the tables instead, circumventing your attempts at security.

Then again, maybe I've misunderstood what you're trying to achieve...
can you give more detail?

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: How restrict select on a view ?

From
Klint Gore
Date:
Andreas wrote:
> I'd like to have a view only to be used by certain users.
> The tables are public.
>
> Can this only be done by restricting access to the tables?
>

GRANT/REVOKE works on views
revoke all on aview from public;
grant select on aview to user1;

As Raymond pointed out, if user2 knows what the definition of aview is,
they can just run it against the raw tables.
e.g.
create view aview as select * from pg_proc;
revoke all on aview from public;
grant select on aview to user1;
set session authorization user2;
select * from aview;  -- fails
select * from pg_proc;  -- works and gives the same result

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Re: How restrict select on a view ?

From
"Merlin Moncure"
Date:
On Mon, Dec 15, 2008 at 9:38 PM, Klint Gore <kgore4@une.edu.au> wrote:
> Andreas wrote:
>>
>> I'd like to have a view only to be used by certain users.
>> The tables are public.
>>
>> Can this only be done by restricting access to the tables?
>>
>
> GRANT/REVOKE works on views
> revoke all on aview from public;
> grant select on aview to user1;
>
> As Raymond pointed out, if user2 knows what the definition of aview is, they
> can just run it against the raw tables.
> e.g.
> create view aview as select * from pg_proc;
> revoke all on aview from public;
> grant select on aview to user1;
> set session authorization user2;
> select * from aview;  -- fails
> select * from pg_proc;  -- works and gives the same result

Yes, but:

* you can still \d the view (or \d equivalent in sql) which shows it's
definition
* if you can \d view, you can 'create temporary view' with the same
definition on public tables

what does this get you?

merlin

Re: How restrict select on a view ?

From
Andreas
Date:
Merlin Moncure schrieb:
> what does this get you?
>
>
Not as much as I first hoped. In hindsight it was a silly question.
The point was that the view gets linked into an Access-Client.
There are some Users who shouldn't bother to figure over the results as
it does some performance statistics.

The users arent able to install other clients like pgAdmin to enter the
DB w/o Access. Most of them are likely not interested enough to figure
out how one would create a query anyway.
A query-object just waiting to get clicked in an idle moment is another
thing though.

Thanks a lot anyway as I learned 1-2 thingies :)