MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:June 9 2007 3:06pm
Subject:Re: Arbitrary Boolean Functions as Relational Database Structure?
View as plain text  
Hi David,

David T. Ashley wrote:
> Hi,
> 
> I'm implementing a software release database.  Users may aribtrarily be
> members of groups (a many-to-many mapping), and each software release may
> contain multiple files.
> 
> I'd like to allow users the maximum flexibility in deciding who may view
> what software releases.  The most obvious approach is to allow 
> specification
> in the form of "Release X may be viewed by Users in Group Y or Group Z", 
> per
> release.
> 
> In this case, the database design would be something like:
> 
> [Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files]
> 
> The many:many relationship between groups and software releases specifies a
> Boolean function, of the form "is in Group X or is in Group Y or ...".
> Since one knows the user who is logged in (for a web database), one can do
> an outer join and quickly find all the software releases that the user may
> view.  I believe one can do it in O(log N) time.
> 
> However, the Boolean function is of a fairly special form ("is in Group 
> X or
> is in Group Y ...").  This is the only form where it seems to translate to
> an SQL query naturally.
> 
> Here is my question:
> 
> Is there any interesting way to structure a database so that other forms of
> permissions can be specified and translate directly into SQL queries?
> 
> For example, what if, for a software release, one says, "to view this
> software release, a user must be in Group X or Group Y, but not in Group Z
> and not user Q"?  Is there a database structure and a corresponding 
> O(log N)
> query that will quickly find for a given user what software releases may be
> viewed?

I have developed such a system over the past 7 years or so.  It is quite complex 
to explain, but it's really simple when you get down to it.  I wrote a two-part 
series about it on my blog:

http://www.xaprb.com/blog/2006/08/16/how-to-build-role-based-access-control-in-sql/
http://www.xaprb.com/blog/2006/08/18/role-based-access-control-in-sql-part-2/

 From your description of the problem, I would say a subset of my solution could 
fit your needs exactly, and be about as simple and efficient as I believe is 
possible.

Cheers
Baron
Thread
Arbitrary Boolean Functions as Relational Database Structure?David T. Ashley9 Jun
  • Re: Arbitrary Boolean Functions as Relational Database Structure?Baron Schwartz9 Jun
  • Re: Arbitrary Boolean Functions as Relational Database Structure?Chris W10 Jun