[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: <https://pairlist8.pair.net/pipermail/geeklog-devel/attachments/20080520/27701946/attachment.html>