Re: row-level security model
От | Marc Durham |
---|---|
Тема | Re: row-level security model |
Дата | |
Msg-id | 005001c4182f$479d09a0$310a0a0a@mara обсуждение исходный текст |
Ответ на | row-level security model (John DeSoi <jd@icx.net>) |
Ответы |
Re: row-level security model
Re: row-level security model |
Список | pgsql-general |
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-
В списке pgsql-general по дате отправления: