[SQL] 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_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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Implementing ACLs in Pure SQL?
On Fri, Aug 29, 2008 at 6:57 PM, ries van Twisk <[EMAIL PROTECTED]> 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/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Must I use DISTINCT?
Please consider the following SQL SELECT e.eid, e.name FROM entry e, access a WHERE e.eid = 120 AND (e.ownid = 66 OR e.aid = a.aid) The intent is to match one entry with the eid of 120. However I would like to impose an additional constraint that either e.ownid must be 66 or e.aid must match the aid of an entry in the access table (there's actually a lot more to the query but I think this should be sufficient to illustrate my problem). The problem is that the e.ownid is 66 and therefore the same entry is returned for each access entry. Of course I can simply SELECT DISTINCT but that seems like an improper usage of DISTINCT here. Is there an alternative way to write this query? I only want to select from the access table for the purpose of constraining by aid. Mike -- Michael B Allen Java Active Directory Integration http://www.ioplex.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Must I use DISTINCT?
On Thu, Feb 5, 2009 at 10:59 PM, Rajesh Kumar Mallah wrote: > have you tried Join using , eg > SELECT e.eid, e.name > FROM entry e join access a ON( e.eid = 120 > AND (e.ownid = 66 OR e.aid = a.aid) ) ; > > some sample data might also help in understanding the prob > more clrearly. Hi Rajesh, Unfortunately using JOIN does not seem to change the result. Here is some real data: > select eid, name, ownid, aclid from foo_entry; +-+---+---+---+ | eid | name | ownid | aclid | +-+---+---+---+ | 64 | system|64 | 0 | | 66 | abaker|66 | 0 | | 67 | bcarter |67 | 0 | | 68 | cdavis|68 | 0 | | 69 | [email protected]|66 | 114 | | 70 | [email protected] |67 | 120 | | 71 | [email protected]|68 | 0 | | 72 | (201) 555-1234|66 | 0 | | 73 | (201) 555-4321|67 | 0 | | 74 | (908) 555-2341|68 | 0 | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |66 | 0 | | 92 | IOPLEX Software |64 | 0 | | 114 | Small Cap Consultants |66 | 0 | | 120 | Equity Report Readers |66 | 0 | | 111 | proton.foo.net|64 | 0 | +-+---+---+---+ 15 rows in set (0.01 sec) > select a, b from foo_link; +-+-+ | a | b | +-+-+ | 64 | 111 | | 64 | 113 | | 66 | 69 | | 66 | 72 | | 66 | 113 | | 66 | 114 | | 67 | 70 | | 67 | 89 | | 67 | 113 | | 68 | 71 | | 68 | 113 | | 69 | 72 | | 70 | 73 | | 71 | 74 | | 71 | 92 | | 114 | 120 | +-+-+ 16 rows in set (0.00 sec) So there are two tables: foo_entry AS e and foo_link AS a1. I want to select the the single row from foo_entry with e.eid = 113 but only if the e.ownid = 66 OR if e.aclid is indirectly linked with 66 via the foo_link table. SELECT e.eid, e.name FROM foo_entry e JOIN foo_link a1 ON (e.eid = 113 AND (e.ownid = 66 OR (e.aclid = a1.a AND a1.b = 66))) This yields: +-+---+ | eid | name | +-+---+ | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d | +-+---+ 16 rows in set (0.00 sec) So it matched eid 113 because e.ownid is 66 but it also matched duplicates for each entry in the foo_link table because the is nothing to constrain it with foo_link. Ultimately what I'm trying to do is implement simple access lists in SQL. The foo_link table represents links between account and access list entries. So to select an entry, you either need to own it (ownid = 66) or your account id is linked with it through an access list entry (e.aclid = a1.a AND a1.b = 66). If I use DISTINCT it works fine but it just does not feel right. I'm worried that as the number of links increases (there could be thousands) performance be negatively affected. Mike > On Fri, Feb 6, 2009 at 3:27 AM, Michael B Allen wrote: >> Please consider the following SQL >> >> SELECT e.eid, e.name >> FROM entry e, access a >> WHERE e.eid = 120 >> AND (e.ownid = 66 OR e.aid = a.aid) >> >> The intent is to match one entry with the eid of 120. However I would >> like to impose an additional constraint that either e.ownid must be 66 >> or e.aid must match the aid of an entry in the access table (there's >> actually a lot more to the query but I think this should be sufficient >> to illustrate my problem). >> >> The problem is that the e.ownid is 66 and therefore the same entry is >> returned for each access entry. >> >> Of course I can simply SELECT DISTINCT but that seems like an improper >> usage of DISTINCT here. >> >> Is there an alternative way to wr
Re: [SQL] Implementing "access lists" (was: Must I use DISTINCT?)
On Fri, Feb 6, 2009 at 1:11 AM, Stephan Szabo wrote: > > On Thu, 5 Feb 2009, Michael B Allen wrote: > >> Please consider the following SQL >> >> SELECT e.eid, e.name >> FROM entry e, access a >> WHERE e.eid = 120 >> AND (e.ownid = 66 OR e.aid = a.aid) >> >> The intent is to match one entry with the eid of 120. However I would >> like to impose an additional constraint that either e.ownid must be 66 >> or e.aid must match the aid of an entry in the access table (there's >> actually a lot more to the query but I think this should be sufficient >> to illustrate my problem). >> >> The problem is that the e.ownid is 66 and therefore the same entry is >> returned for each access entry. >> >> Of course I can simply SELECT DISTINCT but that seems like an improper >> usage of DISTINCT here. >> >> Is there an alternative way to write this query? I only want to select >> from the access table for the purpose of constraining by aid. > > Would something like: > SELECT e.eid, e.name FROM entry e WHERE e.eid = 120 AND > (e.ownid = 66 OR e.aid in (select a.aid from access a)) > do what you wnat? Indeed it does. Using sub-selects eliminates the duplicate entries. Thanks Stephan. However, now I have a deeper SQL challenge. As I stated before, I'm ultimately trying to implement "access lists" where I simply join on (or subselect across) a table of links that link account and access list entries. If I only use one level of indirection it's easy. However, I'd really like to see if I can efficiently implement *two* levels of indirection. Meaning instead of having an access list linked with account entries, I want to have an access list linked with access lists that are linked with account entries. This is much more useful because you can grant permissions to lists of accounts. Otherwise, you have to constantly add and remove individual accounts from access lists which would be very tedious. This is the full "access list" query with two levels of indirection (that uses subselects instead of joins): SELECT e.eid, e.type, e.name, e.ownid, e.aclid FROM foo_entry e WHERE e.eid = 70 AND (e.ownid = 66 OR e.aclid = 66 OR e.aclid IN (SELECT a FROM foo_link WHERE data >= 10 AND (b = 66 OR b IN (SELECT a FROM foo_link WHERE b = 66) OR b IN (SELECT b FROM foo_link WHERE a = 66) )) OR e.aclid IN (SELECT b FROM foo_link WHERE data >= 10 AND (a = 66 OR a IN (SELECT a FROM foo_link WHERE b = 66) OR a IN (SELECT b FROM foo_link WHERE a = 66) )) ) So the above should return the foo_entry record with eid 70 but only if the user's own account (the entry with an eid of 66) matches either the ownid OR aclid OR the eid of an entry linked with an entry with an eid of 66 OR linked with an entry that is linked with an entry with an eid of 66. The 'data' field is the access level - the higher the level, the more permission you have. So my question is simply - can this query be reduced or optimized futher? Another thing I'm wondering is if this query can scale. At some point I'll just load a lot more records and find out. But I suspect there are people that would already know if this is a hopeless exercise? Here's the sample data again. There are only two tables: > select eid, type, name, ownid, aclid from foo_entry; +-+--+---+---+---+ | eid | type | name | ownid | aclid | +-+--+---+---+---+ | 64 |5 | system|64 | 0 | | 66 |5 | abaker|66 | 0 | | 67 |5 | bcarter |67 | 0 | | 68 |5 | cdavis|68 | 0 | | 69 |7 | [email protected]|66 | 114 | | 70 |7 | [email protected] |67 | 120 | | 71 |7 | [email protected]|68 | 0 | | 113 |6 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |66 | 0 | | 92 | 10 | IOPLEX Software |64 | 0 | | 114 |8 | Small Cap Consultants |66 | 0 | | 120 |8 | Equity Report Readers |66 | 0 | > select * from foo_link; +-+-+--+--+--+ | a | b | weight_a | weight_b | data | +-+-+--+--+--+ | 114 | 120 |1.000 |1.000 | 10 | | 66 | 69 |1.000 |1.000 | NULL | | 67 | 70 |1.000 |1.000 | NULL | | 68 | 71 |1.000 |1.000 | NULL | | 66 | 72 |1.000 |1.000 | NULL | | 69 | 72 |1.000 |1.000 | NULL | | 70
Re: [SQL] Grass Root Protectionism
On Sun, Feb 8, 2009 at 1:50 AM, Scott Marlowe wrote: > On Sat, Feb 7, 2009 at 11:40 PM, Boycott Tech Forums > wrote: >> I am a Sr. Software Engineer in USA who (like many others) have been >> unfairly treated with offshore software engineers who have the audacity to >> take our jobs, yet ask (mostly) American engineers to help them with their >> technical challenges via Technical Forums (like this one). >> >> One solution is a bit of Grass Root Protectionism by boycotting technical >> forums. Perhaps if it takes an offshore engineer 2 hours to solve a problem, >> then the employer would see the real cost implication. >> >> I encourage American engineers who spend hundreds of thousands of dollars to >> develop their skills not to give it away so freely. > > Without foreign engineers working on pgsql it wouldn't be nearly as > far along as it is today. > > I live and work in America, but I am not about to sign on to this shit. I don't think anyone should take this seriously. If your job is doing something that someone else can do almost as well, you're going to find yourself out of work once in a while. Hoarding trivial information is not going to help you with that - quite the opposite I think. Someone should point out to this "Senior Engineer" that America was built by foreigner labor. Heterogeneity is our greatest asset. But it's not going to be me because I think the whole thing is just a troll or an email harvester. I'm not sure which. Mike -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
