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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
