Re: row-level security model

From: "John DeSoi" <jd@icx.net>

>
> On Apr 1, 2004, at 10:26 AM, Bruno Wolff III wrote:
>
> > You should be able to do this with a view. current_user will give you
> > the user. You probably want to join this with your own group table
> > and with the table of interest. If each row belongs to only one group
> > this is easy.
>
>
> I'm still not clear on how this would work. Let me provide a more
> concrete example and perhaps you can give me a little more help on how
> the view model would work.
>
> Suppose I have a school database. Teachers have a many-to-many
> relationship with their sections (classes). Students also have a
> many-to-many relationship with the section table. So the teacher group
> should be able to view the student records for any student in any of
> their classes. My second group is parents. The parent and student
> tables also have a many-to-many relationship. So when a parent logs in
> they should be able to view any student for which they are a primary
> contact. Now what if Sam is both parent and teacher groups. He should
> be able to view the students in his class as well as any students for
> which he is the guardian.
>
> Best,
>
> John DeSoi, Ph.D.
>

Do you think this would work?
There are a lot of joins. And I assumed it would need to look up the
parent's and teacher's  usernames, and that your many-to-many relationships
were in tables like students_parent_link.

CREATE VIEW your_students AS
SELECT s.*
 FROM student AS s
  INNER JOIN class_student_link AS cs ON s.student_id = cs.student_id
  INNER JOIN class AS c ON cs.class_id = c.class_id
  INNER JOIN students_parent_link AS sp ON s.student_id = sp.student_id
  INNER JOIN parent AS p ON sp.parent_id = p.parent_id
  INNER JOIN teacher AS t ON c.teacher_id = t.teacher_id
WHERE t.username = CURRENT_USER()
 OR p.username = CURRENT_USER()

-Marc Durham-