Thread: Implementing ACLs in Pure SQL?
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_groupsLIKE '%+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/
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
On Fri, Aug 29, 2008 at 6:57 PM, ries van Twisk <pg@rvt.dds.nl> wrote: > > Then in plpgsql I resolve the correct ACL for a user. I didn't think procedures would help me much in this case but I would be interested in hearing how they would. Another way to do it would be to have a table for storing ACL entries and reference those entries with an ACL ID in the records being protected. For example: DROP DATABASE acl; CREATE DATABASE acl; USE acl; CREATE TABLE ace ( acl int(5) unsigned, eid int(5) unsigned, UNIQUE (acl,eid) ); CREATE TABLE entry ( eid int(5) unsigned NOT NULL AUTO_INCREMENT, acl int(5) unsigned, stuff tinytext, PRIMARY KEY (eid) ); -- acl 100 has groups 18, 19, 20, 21 INSERT INTO ace (acl,eid) VALUES (100,18); INSERT INTO ace (acl,eid) VALUES (100,19); INSERT INTO ace (acl,eid) VALUES (100,20); INSERT INTO ace (acl,eid) VALUES (100,21); -- acl 101 has groups 20, 21, 22 INSERT INTO ace (acl,eid) VALUES (101,20); INSERT INTO ace (acl,eid) VALUES (101,21); INSERT INTO ace (acl,eid) VALUES (101,22); -- entry with no acl INSERT INTO entry (acl,stuff) VALUES (0,'red'); -- entry with acl 100 INSERT INTO entry (acl,stuff) VALUES (100,'blue'); -- several entries with acl 101 INSERT INTO entry (acl,stuff) VALUES (101,'green'); INSERT INTO entry (acl,stuff) VALUES (101,'yellow'); INSERT INTO entry (acl,stuff) VALUES (101,'purple'); The above inserts two ACLs with IDs 100 and 101 with ACEs 18, 19, 20, 21 and 20, 21, 22 respectively. Then we create three entries - one with no ACL reference, one with ACL 100 protecting 'blue' and one with ACL 101 protecting entries for 'green', 'yellow' and 'purple'. Now an access check is: sql> SELECT DISTINCT e.eid, e.acl, e.stuff FROM entry e, ace a WHERE e.acl = a.acl AND a.eid IN (10, 20, 30); which should return entries for everything but 'red' because group 20 is found in both ACL 100 and 101. Whereas the following should return only 'blue' because groups 18 and 19 are only found in ACL 100. sql> SELECT DISTINCT e.eid, e.acl, e.stuff FROM entry e, ace a WHERE e.acl = a.acl AND a.eid IN (18, 19); The nice thing about this is that ACLs tend to be inherited so we have an opportunity to normalize ACLs a bit. Although it would be very nice if I could avoid the DISTINCT so that the access check is isolated to the WHERE clause. Is there an expression that means "if x matches any one of the following values"? Mike -- Michael B Allen PHP Active Directory SPNEGO SSO http://www.ioplex.com/