[geeklog-devel] Bitwise SQL for ACL checks

[Trinity]

why not use unix style 3 digit number
chmod 666 etc etc

On Tue, May 20, 2008 at 5:46 PM, Tony Bibbs <tony at tonybibbs.com> wrote:

> 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

>

>

>

> _______________________________________________

> geeklog-devel mailing list

> geeklog-devel at lists.geeklog.net

> http://eight.pairlist.net/mailman/listinfo/geeklog-devel

>