[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
>