Re: row-level security model

Marc,

On Apr 1, 2004, at 4:21 PM, Marc Durham wrote:

> 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.
>

Thanks very much -- this helped get me started.

I think I finally came up with something that lets me have modularized
access functions where I can combine access but still maintain a single
view. Here is a rough example.

Let's say I have two access functions contact_students and
staff_teaches_students. If the current user is in the contact group it
returns the primary keys (integer dbid in my example) of the related
students. Similarly, if the current user is on the teaching staff, it
returns the keys for all students in his/her classes. So I create a
function to combine all of my access functions with union:

create or replace function student_access ()
returns setof integer as '
select * from contact_students()
union select * from staff_teaches_students();
' language sql;

Then my view is

create view student_v as select student.* from student,
student_access() as id
where student.dbid = id;

Comments/criticisms about design or performance issues?

Is there a way to provide column security without creating different
views for every possible scenario?

Best,

John DeSoi, Ph.D.