grant the right to select only certain rows?

You could create a VIEW and in that view show only grade level 1; and give
her access just to that view.

-----Original Message-----
From: Fran Fabrizio [mailto:ffabrizio@mmrd.com]
Sent: Friday, January 25, 2002 12:04 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] grant the right to select only certain rows?

If I have a table students:

name grade_level
Joe 1
Kim 1
Lisa 2
Mike 2

And I have two database users, mary_smith and tom_white. If Mary Smith is
the 1st grade teacher, is there any way to grant her access to only select
rows where grade_level=1? I think GRANT only works as a table-wide
permission, but a co-worker thinks he has seen similar behavior in Oracle,
like "GRANT SELECT AS SELECT * FROM STUDENTS WHERE grade_level = 1
ON students FOR USER mary_smith" (Rough approximation of the type of
query I am looking for).

Thanks,
Fran

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Why not create a view and grant her access to the view?

Greg

----- Original Message -----
From: "Fran Fabrizio" <ffabrizio@mmrd.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, January 25, 2002 12:04 PM
Subject: [GENERAL] grant the right to select only certain rows?

Fran Fabrizio <ffabrizio@mmrd.com> writes:

And I have two database users, mary_smith and tom_white. If Mary Smith
is the 1st grade teacher, is there any way to grant her access to only
select rows where grade_level=1?

Make a VIEW that shows only those rows, and grant access to the view.
See the archives for past discussions.

regards, tom lane

Fran Fabrizio wrote:

If I have a table students:

name grade_level
Joe 1
Kim 1
Lisa 2
Mike 2

And I have two database users, mary_smith and tom_white. If Mary Smith
is the 1st grade teacher, is there any way to grant her access to only
select rows where grade_level=1? I think GRANT only works as a
table-wide permission, but a co-worker thinks he has seen similar
behavior in Oracle, like
"GRANT SELECT AS SELECT * FROM STUDENTS WHERE grade_level = 1
ON students FOR USER mary_smith" (Rough approximation of the type of
query I am looking for).

Such granularity doesn't exists in PostgreSQL.

But you could setup a second table teachers:

name grade
-------------------
mary_smith 1
tom_white 2

and then use a view

CREATE VIEW my_students AS SELECT S.name, S.grade_level
FROM students S, teachers T
WHERE T.name = CURRENT_USER AND T.grade = S.grade_level;

Now the teachers don't need SELECT permissions on students,
but only on my_students. Mary can only see Joe and Kim, and
Tom can only see Lisa and Mike.

And you can have multiple rows for one and the same teacher.
So if you add

name grade
-------------------
john_kimble 1
john_kimble 2

he can see all four students.

The advantage is that you don't deal with permissions, but
with data. That's alot easier to keep track and you gain
portability too.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

I was wondering if there were other options, since the reality is that we
have so many grade_levels. It would have been cool if it could be done via
grant or something similar, because (and I'd be thrilled if someone told me
I was wrong on this next point) doing it via views means that every time you
reload the db schema, you have to recreate the views (since the views point
to an OID for the parent table that no longer exists). Our schema will be
under heavy development at least for a couple more months, so this could
become tedious.

Thanks,
Fran

But you could setup a second table teachers:

name grade
-------------------
mary_smith 1
tom_white 2

and then use a view

CREATE VIEW my_students AS SELECT S.name, S.grade_level
FROM students S, teachers T
WHERE T.name = CURRENT_USER AND T.grade = S.grade_level;

Now the teachers don't need SELECT permissions on students,
but only on my_students. Mary can only see Joe and Kim, and
Tom can only see Lisa and Mike.

Ahh...this was the missing link in my head. A way to make one view
customize itself to serve all users.

Thank you!

-Fran

Fran Fabrizio writes:

If I have a table students:

name grade_level
Joe 1
Kim 1
Lisa 2
Mike 2

And I have two database users, mary_smith and tom_white. If Mary Smith
is the 1st grade teacher, is there any way to grant her access to only
select rows where grade_level=1? I think GRANT only works as a
table-wide permission, but a co-worker thinks he has seen similar
behavior in Oracle, like
"GRANT SELECT AS SELECT * FROM STUDENTS WHERE grade_level = 1
ON students FOR USER mary_smith" (Rough approximation of the type of
query I am looking for).

Not directly, Pg's ACL (Access Control Lists) only apply to full relations.
What you can do though is create a view and only grant her permission on
that view.

CREATE VIEW first_grade AS SELECT * FROM students WHERE grade_level = 1;
GRANT SELECT ON first_grade TO mary_smith;

So Mary doesn't have permissions to the "student" table, but she does have
permission to view the "first_grade" view. Not perfect by any stretch of the
imagination, but workable in many situations.