[geeklog-devel] Bitwise SQL for ACL checks

[Michael Tutty]

That's still subject to the same bitwise comparisons.  (~1) + 2 + 4 = 6 for
owner, 6 for group, 6 for others.
     M.

On Tue, May 20, 2008 at 9:30 PM, Trinity <trinity93 at gmail.com> wrote:


> 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

> >

> _______________________________________________

> geeklog-devel mailing list

> geeklog-devel at lists.geeklog.net

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

>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://eight.pairlist.net/pipermail/geeklog-devel/attachments/20080520/27701946/attachment.html>