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...ontrol-in-sql/ http://www.xaprb.com/blog/2006/08/18...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