[geeklog-devel] Bitwise SQL for ACL checks

[Tony Bibbs]

DB2 and Oracle only support it with UDF's.  e.g. in ORACLE  you'd use the BITAND function.  

Hadn't thought about converting it to a string.  NonthelessI think we're OK.  Apparently I just need to post it so-as to make sure I'm not doing anything *really* stupid.

--Tony

----- Original Message ----
From: Joe Mucchiello <joe at ThrowingDice.com>
To: Geeklog Development <geeklog-devel at lists.geeklog.net>
Sent: Tuesday, May 20, 2008 5:22:22 PM
Subject: Re: [geeklog-devel] Bitwise SQL for ACL checks

I do believe Oracle and DB2 support bitwise ops. What databases does 
that leave?

Worst case, you could convert ACLs to binary strings and then use 
substr to find the bits. So an ACL of 35 (32 | 2 | 1) would end up in 
the database as 00010011 (or 0000000000010011). The "rights & 1" 
where clause becomes "substring(rights, 7, 1) = 1"

Or you can use letters:

$db_rights = 'ABCDEFGHIJ';  <-- index this array using "log(2) 
targetright" So ACL 16 becomes 'D'
So you create function that turn "10011" into EBA and store the 
string EBA in the rights column.
Checking for access becomes "rights like '%A%' ".

There's always another way to store the data. But since GL2 uses 
named queries, under the hood it can be as convoluted as necessary.

   Joe


At 05:51 PM 5/20/2008, Tony Bibbs wrote:
>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
>
>
>
>_______________________________________________
>geeklog-devel mailing list
>geeklog-devel at lists.geeklog.net
>http://eight.pairlist.net/mailman/listinfo/geeklog-devel

----
Joe Mucchiello
Throwing Dice Games
http://www.throwingdice.com 

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