Alessandro
Alessandro

Reputation: 3760

Resource based authorization database schema

I have a fairly common database schema for authentication/authorization:

User

 - Id
 - Username
 - Password

Role

 - Id
 - Name

RolePermission

 - RoleId
 - PermissionType

UserRole

 - UserId
 - RoleId

Up to there, no problems. Now I need to associate UserRoles to resources in a fairly complex way.

E.g. imagine a Department resource. A user could be a Teacher for one department and an Auditor for other departments. The same user could also be a Supervisor for the entire Institute resource.

Now I need to express that requirement in my database structure and I came up with that idea:

User

Id   Username
1    user1

Role

Id   Name
1    Teacher
2    Auditor
3    Supervisor

Department

Id   Name
1    Dep1
2    Dep2
3    Dep3

Institute

Id   Name
1    Inst1

UserRole

UserId   RoleId   EntityName   EntityId
1        1        Department   1
1        2        Department   2
1        2        Department   3
1        3        Institute    1

This could work but I'm worried by the lack of referential integrity enforcement. Are there better solutions to model that requirements?

Upvotes: 1

Views: 157

Answers (0)

Related Questions