On Aug 29, 2008, at 5:33 PM, Michael B Allen wrote:
Hello,
I've been tinkering with SQL level access control for web
applications. Currently I have a UNIX style uid / gid for each record
and do a WHERE e.uid IN (10,20,30) where 10, 20 and 30 would be the
IDs
of groups the user is in.
However, I'm not satisfied with this model as it has all of the
problems
UNIX style permissions have. In particular you can only have one
group on
each record which ultimately leads you into a few quantized levels of
privilege.
It would be much better if there were a way to implement ACLs.
Meaning - given a user with the following groups (again using IDs
instead of names):
user_groups:
10
20
30
and an ACL with groups:
acl_groups:
18
19
20
21
an access control check is performed with the following pseudocode:
foreach (acl_groups as ag) {
foreach (user_groups as ug) {
if (ug == ag) {
return true
}
}
}
return false;
Meaning, groups 18, 19, 20 and 21 are allowed to access the resource
protected by the ACL (the database record). When group 20 is reached
by
the outer loop and the inner loop finds 20 in the list of groups the
user is in, the above example would return true to indicate that the
particular user should be granted access to the record (i.e. the WHERE
clause would match).
There is one way to do this. Each record has an ACL field with a
string
like '+18+19+20+21+':
UPDATE e SET acl_groups = '+18+19+20+21+' WHERE eid = 1001
Then to perform the access check and retrieve the record you do:
SELECT * FROM e WHERE
(e.acl_groups LIKE '%+10+%'
OR e.acl_groups LIKE '%+20+%'
OR e.acl_groups LIKE '%+30+%')
INSERT INTO e (name, color) VALUES ('Alice', 'blue') WHERE eid = 1001
AND (e.acl_groups LIKE '%+10+%'
OR e.acl_groups LIKE '%+20+%'
OR e.acl_groups LIKE '%+30+%')
... etc
Using LIKE is a little inefficient but I assume it would be more
efficient
than retrieving all of the records and performing the access check in
loop within the application.
Can anyone suggest a superior method?
Or any other ideas regarding implementing ACLs in SQL would be greatly
appreciated.
Mike
Hey Mike,
currently I am underway implementing this with pure SQL, but I don't
have groups
like unix groups.
Each group has a specific meaning. For example a group marketing,
or a group marketing admin. They are just names and it's up to the
application to implement
what action to take.
In my case I am disabling and enabling specific objects within a adobe
flex interface
and subgroups are handles with trees.
Then in that case a user can be member of specific groups (or sub
groups) in and
my case it would show what application you can access, or what objects
are allowed for this user.
Current I have a user table, a group table and an applciation table
and a object table
Then two MM tables between user and application/object and group and
application/object.
Then in plpgsql I resolve the correct ACL for a user.
Ries
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
