This seems like a simple query to me. Correct me if I am wrong but as
I understand it you want to get a list of SwReleases that a user has
access to. Would something like this not work
SELECT s.ID, s.Name
FROM SwRelease as s
WHERE s.ID IN (
SELECT SwID
FROM GroupSwRel -- this table is the group to SwRelease relation ship
WHERE GroupID IN (
SELECT GroupID
FROM UserGroupRel -- this table is the user to group relationship.
WHERE UserID = 'someuserid'))
I've never done a query with a sub query in a sub query but it seems
like it should work to me. In fact, if I'm not mistaken you may be able
to rewrite this just using joins.
Chris W
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?
>
> Thanks.
>
--
Chris W
KE5GIX
"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm"
Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
