database - Many-to-Many Relationships across 4 Tables

Question

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.

标签： databasedatabase-designdatabase-schema

Answer 1

-- 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 ;

笔记：

All attributes (columns) NOT NULL

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