[geeklog-devel] Bitwise SQL for ACL checks

[Tony Bibbs]

K, done some more homework and I guess I've answered my own questions.  Right now MySQL, SQL Server and Postgres all support & and | for bitwise math.  I'll use that for now in our named queries.

Moving forward our named query implementation will need to include a dbms="mysql" attribute in the XML and then you can tweak individual named queries manually.  I can do this later...besides we have zero support for Oracle, DB2, etc now anyway so adding later shouldn't be a problem with this approach.

Still chime in, though, if you have a question or suggestion.

--Tony

----- Original Message ----
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Sent: Tuesday, May 20, 2008 4:36:08 PM
Subject: [geeklog-devel] Bitwise SQL for ACL checks

Apparently Bitwise math is not part of the SQL92 standard.  Given we are using bitwise operations for ACL's checks in GL2 this is a problem because we store the ACL values in a table called gl2_item_acl.  Even worse, we not only store the ACL but we also allow for inverse ACL's which essentially implements both a blacklist and whitelist for access to an item.

My brain isn't equipped at the moment to deal with all the possible solutions so I'm asking for help as this is one of the last things I need to knock out for the first GL2 alpha.

We have a few constants for ACL check so to illustrate:

ACL_READ = 1
ACL_LIST = 2

Thus a sample *mysql* query for matching the above ACL's would be:

SELECT gl2_item.*
FROM gl2_item, gl2_item_acl
WHERE  gl2_item_acl.item_id = gl2_item.item_id 
AND (rights & 1) 
OR (rights & 2) 

Given the &,|, etc aren't SQL92 standard is there another way to construct the SQL in a DBMS friendly manner?  For reference you may want to read this first:

http://wiki.geeklog.net/wiki/index.php/Using_ACLsG2

--Tony


_______________________________________________
geeklog-devel mailing list
geeklog-devel at lists.geeklog.net
http://eight.pairlist.net/mailman/listinfo/geeklog-devel