CODEWITHSUNDEEP
sqlsql-serverpermissions
Stefan Steiger
Stefan Steiger

Reputation: 82276

How to implement nested groups in Db?

Question: In our database, we have a table Users, a table Groups, and a mapping table from users to groups. Then we have permissions, which are on Groups.

T_User
USR_UID
USR_Name
USR_PasswordHash


T_Group
GRP_UID
GRP_Name


T_MAP_User_Group
MAP_USRGRP_UID
MAP_USRGRP_USR_UID
MAP_USRGRP_GRP_UID


T_MAP_SitePermission
MAP_STEGRP_UID
MAP_STEGRP_STE_UID
MAP_STEGRP_GRP_UID
MAP_STEGRP_ReadPermission
MAP_STEGRP_WritePermission
MAP_STEGRP_DeletePermission


T_Sites
STE_UID
STE_Name
STE_Location

Now if I wanted to add a Group to a Group (nested groups, as in ActiveDirectory), how would I go about implementing this, especially resolving users to groups and rights/permisssions in a select query ?

Upvotes: 3

Views: 864

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

You can add a mapping table for groups... following your convention, it would look something like the following:

T_MAP_Group_Group
MAP_GRPGRP_UID
MAP_GRPGRP_PARENT_GRP_UID
MAP_GRPGRP_CHILD_GRP_UID

This allows for a group to be nested within multiple parent groups... and at the same time allows for a group to be the parent of multiple children groups.

Upvotes: 1

Steve Fibich
Steve Fibich

Reputation: 322

There are a number of different ways to handle this problem, I believe the best way to handle it would be to create a Parent Group to Group relationship table. As to how would right a query to determine the permissions I don't think there is enough information to determine that yet. How would conflicting permission be resolved, would the parent group override the child or the other way around?

Upvotes: 0

Related Questions