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!