Re: row-level security model

On 2/4/04 4:50 am, "John DeSoi" <jd(at)icx(dot)net> wrote:

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

Hi John,

I don't know if this will fit your needs, but this is how I handled row
level security in an application I have. It uses arrays, so may be
PostgreSQL specific I think... But basically I have person and group tables

CREATE TABLE person (
person_id INT4 NOT NULL,
..
<other fields>
..
username TEXT NOT NULL,
lab_group_id INT4 NOT NULL,
groups_ids INT[] NULL
);

CREATE TABLE groups (
group_id INT4 NOT NULL,
name TEXT NOT NULL
);

Then each object has a base table:

CREATE TABLE experiment_base (
expt_id INT4 NOT NULL,
..
<other fields>
..
owner_id INT NOT NULL,
writer_id INT[] NOT NULL,
readers_id INT[] NOT NULL
);

I can then control who can update the row at the user level, and who can
read the row at the group level using a view like so:

CREATE OR REPLACE VIEW experiment
AS
SELECT <various fields>
FROM experiment_base a,
person b
WHERE
a.owner_id = b.person_id AND
(readers_id &&
(select groups_ids from person a where a.username = current_user)
OR
(select person_id from person a where a.username = current_user) = ANY
(writer_id)
OR
owner_id = (select person_id from person a where a.username =
current_user));

I then have a couple of functions to add or remove group_id's from the
readers_id array, and also to add or remove person_id's from the writer_id
array

I don't have large numbers of users or groups, so it performs ok... Not sure
how the array approach will scale with more though.

I don't think this is a classical approach.... But it seems to work for me.
But I would appreciate comments/criticisms from others?

Cheers

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.