Many-to-Many Relationships across 4 Tables

user_12905596 0
Viewed 138

I'm implementing a role base access control system, for which have the following database tables.

groups
---------
id (PK)
name
level

resources
---------
id (PK)
name

roles
---------
id (PK)
name

permissions
-----------
id (PK)
name
description


users
-----------
id (PK)
name
group_id(FK - references id on groups)
role_id(FK - references id on roles)

Groups has a many-to-many relationship with Resources and Roles. So I have the following junction tables.

group_resource
---------------
group_id(FK - references id on groups)
resource_id(FK - references id on resources)

group_role
---------------
group_id(FK - references id on groups)
role_id(FK - references id on roles)

Here is the issue:

Any given role within a group should have permissions for resources assigned to that group only.

I'm not entirely sure what would be the best way to model the relationship between roles, permissions, and resources in the context of group_resource and group_role relationships .

Any suggestions will be highly appreciated.

Thanks.

database-schema database-design database
2 Answers

Here is a possible solution, with a certain degree of redundancy.

groups (id (PK), name, level)
roles (group_id (FK for groups) ,num_role, name) with PK (group_id, num_role)
users (id (PK), name, group_id, num_role) with (group_id, num_role) FK for roles
resource_types (id (PK), name)
group_resources (resource_type_id (FK for resource_types), group_id (FK for groups) with PK both the attributes
permissions (resource_type_id (FK for resource_types), group_id, num_role, description)  with (group_id, num_role) FK for roles

With this solution, the application must check during the insertion of a permission, that the resource appear in the group_id specified in the permission, typically with a trigger.

A way of eliminating at all this redundancy (but it seems to me a less satisfying design), is to eliminate the relation group_resources, since all the information can be found through permissions.

user_23827340 
-- Group GRP exists.
--
group {GRP}
   PK {GRP}

-- Role ROL exists.
--
role {ROL}
  PK {ROL}

-- Resource RES exists.
--
resource {RES}
      PK {RES}

-- Role ROL exists within group GRP.
--
group_role {GRP, ROL}
        PK {GRP, ROL}

FK1 {ROL} REFERENCES role  {ROL}
FK2 {GRP} REFERENCES group {GRP}

-- Group GRP is assigned resource RES.
--
group_resource {GRP, RES}
            PK {GRP, RES}

FK1 {GRP} REFERENCES group    {GRP}
FK2 {RES} REFERENCES resource {RES}

-- Permission PER exists.
--
permission {PER}
        PK {PER}

-- Permission PER is granted to role ROL
-- in group GRP for resource RES.
--
group_resource_permission {GRP, RES, ROL, PER}
                       PK {GRP, RES, ROL}

FK1 {GRP, RES} REFERENCES group_resource {GRP, RES}
FK2 {GRP, ROL} REFERENCES group_role     {GRP, ROL}
FK3 {PER}      REFERENCES permission     {PER}

-- User USR is assigned role ROL in group GRP.
--
user {USR, GRP, ROL}
  PK {USR}

FK1 {ROL} REFERENCES role  {ROL}
FK2 {GRP} REFERENCES group {GRP}

-- User USR in role ROL of group GRP,
-- has permission PER to resource RES.
--
CREATE VIEW user_resource_permission
AS
SELECT  u.USR
      , x.RES
      , x.PER
      , u.GRP
      , u.ROL
FROM user as u
JOIN group_resource_permission as x ON x.GRP = u.GRP
                                   AND x.ROL = u.ROL ;

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
user_1967130
Related