Re: Arbitrary Boolean Functions as Relational Database Structure?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Arbitrary Boolean Functions as Relational Database Structure?
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]
Arbitrary Boolean Functions as Relational Database Structure?
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.