Re: grant the right to select only certain rows?

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(at)Yahoo(dot)com #

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