Something else you may or may not want to consider. You may want to have
both users and user-groups be principles. Something like the following:
Principle (ID, NAME, PERMS)
User (P_ID, PASSWORD, ... )
UserGroup (P_ID, ... )
PrincipleLink (PID, CID) -> Many to Many (parent, child)
Also consider a row in the Links table for each user refering to itself.
Then you can do stuff like the following pretty easily:
// search for a user permission (or the groups they belong to)
EXISTS(
SELECT null
FROM Principle P
JOIN PrincipleLink L ON P.ID = L.PID
WHERE
( P.PERMS & (1 + 2) = (1 + 2) )
AND L.CID = <userID> )
You can then simply have User and UserGroup store information that is
not directly related to permissions.
R.
-----Original Message-----
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 31, 2006 7:59 AM
To: Brent Baisley
Cc: Stephen Orr; mysql@lists.mysql.com
Subject: Re: Complex SQL for multiple joins
Also, depending on the number of permissions you are tracking, you could
use a single INT field and do bitwise ORing in your application to
determine permission checks...
Though I usually don't recommend denormalizing the schema, this is one
scenario that would potentially make life a bit easier. Instead of
having six tables, you would have 3:
Users
UserGroups
User2Group (stores many-to-many relationship)
Both the fact tables (Users and UserGroups) would have an INT UNSIGNED
field called, say, permission_flags which could contain up to 32 flag
values for various permissions.
This is a very compact and efficienct way of *storing* permissions.
Retrieving sets of users/groups based on a single flag would be easy,
though an index would not be used.
For instance, imagine you have set bit 1 to mean "has read access".
To find all users with read access, you would do:
SELECT * FROM Users WHERE permissions & 1;
Let's say you have another permission for write access at the second bit
and you want to see all users with both read and write permission, you'd
do:
SELECT * FROM Users WHERE permissions & (1 + 2) = (1 + 2);
the third bit would be 2^3 or 4, etc...:
SELECT * FROM Users WHERE permissions & (1 + 2 + 4) = (1 + 2 + 4);
Additionally, what is nice about this type of organization is that you
can store the user's permissions in session memory and reference the
permissions without having to go to the database by using the same
bitwise operations in your application code.
For instance, in PHP you would write something like:
<?php
define('CAN_READ', 1 << 0);
define('CAN_WRITE', 1 << 1);
define('CAN_DO_OTHER', 1 << 2);
$permissions = $my_session_vars['permissions']; $can_read = $permissions
& CAN_READ; $can_write = $permissions & CAN_WRITE;
...
?>
Some more notes:
* Use a 64-bit BIGINT datatype if you need 33-64 flags for permissions
* You can use a SET datatype as well in this manner
* Make sure you understand bitwise operations
Cheers,
Jay
On Thu, 2006-08-31 at 09:39 -0400, Brent Baisley wrote:
> You should be able to do it 1 query, I can think of two ways. I'm not
> sure which one will run faster. I use table aliasing to keep the lines
shorter.
> The first possibility would have user permissions in one column and
group permissions in another:
>
> SELECT users.userid,perm_u.permissions,perm_g.permissions
> FROM users
> LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid LEFT
> JOIN permissions AS perm_u ON u_p.permid=perm_u.permid LEFT JOIN
> users_usergroups AS u_ug ON users.userid=u_ug.userid LEFT JOIN
> usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid LEFT JOIN
> permissions AS perm_g ON ug_p.permid=perm_g.permid WHERE
> users.userid=#
>
> But if you want to just have one column of permissions, you'll need to
use UNIONs. I think this is more readable.
>
> SELECT users.userid AS userid,permissions AS permissions FROM users
> LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid LEFT
> JOIN permissions ON u_p.permid=permissions.permid WHERE
> users.userid=# UNION SELECT users.userid AS userid,permissions FROM
> users LEFT JOIN users_usergroups AS u_ug ON users.userid=u_ug.userid
> LEFT JOIN usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid
> LEFT JOIN permissions AS perm_g ON ug_p.permid=perm_g.permid WHERE
> users.userid=#
>
> Either of those should work. I don't know your table structure, so I
can't get too specific with it.
>
> ----- Original Message -----
> From: "Stephen Orr" <[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> Sent: Wednesday, August 30, 2006 7:27 PM
> Subject: Complex SQL for multiple joins
>
>
> > Hi,
> >
> > For my current website project I'm developing a fine-grained access
> > control system.
> > I have the schema mostly worked out, although it will be having
> > additional data added to parts of it later.
> >
> > For the record, I have 6 tables:
> >
> > users (contains all my individual users) usergroups (contains all
> > the groups users can belong to) permissions (contains all the things
> > that the system allows) users_usergroups (a join table that
> > identifies which users belong to which
> > groups)
> > users_permissions (another join table that identifies which users
> > have which
> > permissions)
> > usergroups_permissions (the final join table that identifies which
> > usergroups have which permissions)
> >
> > Each of the _permissions join tables has an additional type column
> > specifying whether the link allows or denies that particular action.
> >
> > What I've managed to get so far is queries returning which users
> > belong to which groups, which users have which permissions, and
> > which usergroups have which permissions. However, I need to go one
> > step further and retrieve the permissions belonging to the
> > usergroups which a specified user is a member of. Ideally I'd like
> > to retrieve the individual users permissions at the same time.
> >
> > So what I need is a query that returns permissions belonging to a
> > specific user, and permissions belonging to the usergroups that the
> > specified user is a member of. Is this something I can do without
too much hassle?
> >
> > Thanks in advance!
> >
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
