Re: Implementing ACLs in Pure SQL?

On Fri, Aug 29, 2008 at 6:57 PM, ries van Twisk <pg(at)rvt(dot)dds(dot)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/