Thread: 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
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? > > 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 >
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
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
> 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
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 > 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 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.