[geeklog-devel] Bitwise SQL for ACL checks

[Tony Bibbs]

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