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
--
Michael B Allen
PHP Active Directory SPNEGO SSO
http://www.ioplex.com/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
