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]
