Thread: Grant select on view

Grant select on view

From
thiemo
Date:
Hi,


I try encapsulate my db in views such that users access it by the
views and only get to see the rows they are allowed to. However, I
created a view and put select select ricghts on it for the respective
users, but a select on it ends up with a permission denied. So, I
granted select rights on the base table, but to no avail. A select by
the users on the base table is no possible, but on the view it stays
denied.


What have I overlooked?


Thx


Thiemo

<fontfamily><param>Helvetica</param>

--

Thiemo Kellner

Tösstalstrasse 146

CH-8400 Winterthur

</fontfamily>Hi,

I try encapsulate my db in views such that users access it by the views
and only get to see the rows they are allowed to. However, I created a
view and put select select ricghts on it for the respective users, but a
select on it ends up with a permission denied. So, I granted select
rights on the base table, but to no avail. A select by the users on the
base table is no possible, but on the view it stays denied.

What have I overlooked?

Thx

Thiemo

--
Thiemo Kellner
Tösstalstrasse 146
CH-8400 Winterthur

Re: Grant select on view

From
Tom Lane
Date:
thiemo <thiemo@gmx.ch> writes:
> What have I overlooked?

A complete example; we can't possibly guess what you did wrong with
only such sketchy details.  It would also be useful to know which
version of PG you are using.

            regards, tom lane

Re: Grant select on view

From
thiemo
Date:
<excerpt>A complete example; we can't possibly guess what you did
wrong with

only such sketchy details.  It would also be useful to know which

version of PG you are using.

</excerpt>

Oh, sure. A self-compiled 7.2, without any source code modifications.
I first instal a sequence:

"CREATE SEQUENCE seq

  INCREMENT 1

  MINVALUE 1

  START 1

  CACHE 1"

then a table:

"CREATE TABLE base (

att1 type(x) default nextval(seq),

...

attn type(x)

)"

a view:

"CREATE VIEW vie AS

  SELECT att2, ..., attn_1

  FROM base

  WHERE attn = CURRENT_USER::varchar"

a user:

"CREATE USER usr PASSWORD 'somewhat'

  NOCREATEDB

  NOCREATEUSER"

and finally the grant:

"GRANT SELECT ON vie TO usr"


Hope this helps. Thx


Thiemo

<fontfamily><param>Helvetica</param>

--

Thiemo Kellner

Tösstalstrasse 146

CH-8400 Winterthur

</fontfamily>> A complete example; we can't possibly guess what you did wrong with
> only such sketchy details.  It would also be useful to know which
> version of PG you are using.

Oh, sure. A self-compiled 7.2, without any source code modifications. I
first instal a sequence:
"CREATE SEQUENCE seq
   INCREMENT 1
   MINVALUE 1
   START 1
   CACHE 1"
then a table:
"CREATE TABLE base (
att1 type(x) default nextval(seq),
...
attn type(x)
)"
a view:
"CREATE VIEW vie AS
   SELECT att2, ..., attn_1
   FROM base
   WHERE attn = CURRENT_USER::varchar"
a user:
"CREATE USER usr PASSWORD 'somewhat'
   NOCREATEDB
   NOCREATEUSER"
and finally the grant:
"GRANT SELECT ON vie TO usr"

Hope this helps. Thx

Thiemo

--
Thiemo Kellner
Tösstalstrasse 146
CH-8400 Winterthur

Re: Grant select on view

From
Tom Lane
Date:
thiemo <thiemo@gmx.ch> writes:
> Oh, sure. A self-compiled 7.2, without any source code modifications. I=20
> first instal a sequence:

You're still not being specific enough, because as closely as I can
reproduce your example, it works fine:

regression=# CREATE SEQUENCE seq;
CREATE
regression=# CREATE TABLE base (
regression(# att1 int default nextval('seq'),
regression(# att2 text,
regression(# attn varchar);
CREATE
regression=# CREATE VIEW vie AS select * from base
regression-# WHERE attn = CURRENT_USER::varchar;
CREATE
regression=# create user usr;
CREATE USER
regression=# GRANT SELECT ON vie TO usr;
GRANT
regression=# \c - usr
You are now connected as new user usr.
regression=> select * from base;
ERROR:  base: Permission denied.
regression=> select * from vie;
 att1 | att2 | attn
------+------+------
(0 rows)

regression=>

It's not apparent to me what you did differently from the above
to cause a permissions problem.

            regards, tom lane

Re: Grant select on view

From
thiemo
Date:
<excerpt>You're still not being specific enough, because as closely as
I can

reproduce your example, it works fine:

</excerpt>

Uhm, then I don't know what information could be missing. I set the
stuff up with dbvisualizer 2.1. I cannot say I really did it like I
stated, 'cause it was a rather chaotic try and error process up to the
point where I had the scripts how I wanted them. However, what I
mentioned was the process I think not necessairily in this order. I
proved the non workingness of this db with psql such that I boldly
rule out a flaw in dbvis.


Well, I shall drop the db and recreate it structuredly and post my
findings again.


Thx for your help anyway


Thiemo

<fontfamily><param>Helvetica</param>

--

Thiemo Kellner

Tösstalstrasse 146

CH-8400 Winterthur

</fontfamily>> You're still not being specific enough, because as closely as I can
> reproduce your example, it works fine:

Uhm, then I don't know what information could be missing. I set the
stuff up with dbvisualizer 2.1. I cannot say I really did it like I
stated, 'cause it was a rather chaotic try and error process up to the
point where I had the scripts how I wanted them. However, what I
mentioned was the process I think not necessairily in this order. I
proved the non workingness of this db with psql such that I boldly rule
out a flaw in dbvis.

Well, I shall drop the db and recreate it structuredly and post my
findings again.

Thx for your help anyway

Thiemo

--
Thiemo Kellner
Tösstalstrasse 146
CH-8400 Winterthur

Re: Grant select on view

From
thiemo
Date:
<excerpt>Well, I shall drop the db and recreate it structuredly and
post my findings again.

</excerpt>

So I have done and all works fine. However, I certainly shall be
curiuos how I could have lead my db to the effect I experienced.


Thx


Thiemo


<fontfamily><param>Helvetica</param>--

Thiemo Kellner

Tösstalstrasse 146

CH-8400 Winterthur

</fontfamily>> Well, I shall drop the db and recreate it structuredly and post my
> findings again.

So I have done and all works fine. However, I certainly shall be curiuos
how I could have lead my db to the effect I experienced.

Thx

Thiemo

--
Thiemo Kellner
Tösstalstrasse 146
CH-8400 Winterthur