9

I'm trying to find the best way for a basic security system for a website. I know I want users and groups.

I thought I'd have:

user_table
user_id
user_name
...

group_type
group_id
group_name
parent_id
...

group_table
id
user_id
group_id

First one is the user, second one is the group, and the third one is the intermediary table that connects the two. One user has many groups.

Does this sound ok?

|improve this question
  • Other than the names. Your GROUP_TYPE table should be named GROUP or something similar, and your GROUP_TABLE should reference both Users and Groups, as that's what it's linking. – Adam Musch Feb 1 '12 at 14:49
  • @adam doesn't group_table do that with user_id and group_id? – johnny Feb 1 '12 at 14:55
  • 4
    @AdamMusch It should not be named GROUP, as that is a reserved word. Tables and columns should never be named after reserved words – Philᵀᴹ Feb 1 '12 at 15:02
  • 1
    @Phil agreed. You may as well name a table SELECT and a field FROM so you can have a query like SELECT [FROM] FROM [SELECT] – JNK Feb 1 '12 at 15:04
  • 2
    Try calling the basic entities something like 'app_user' and 'app_role' – ConcernedOfTunbridgeWells Feb 1 '12 at 15:06
17

The traditional way to model this is using a pattern called Role-Based Security.

The idea is not just to have groups of users, but also groups of permissions. Here is how the pattern looks:

Role Based Security ERD

Note that you want to avoid reserved words for table names, so don't name your tables exactly as shown in the diagram.

The way it works is that your groups or Roles have not only a list of users assigned to them but also a list of permissions assigned to them. This allows you to table-drive both who can do what, but also what it is they can do, if you follow me.

|improve this answer
  • But what if my user has some role like manager or whatever and for some reason the same user needs one particular permission as well (which is not in that role, nor is in any other role alone)? – levi Dec 24 '15 at 7:14
  • @levi - If that happens then one way to handle it is to make a special role like "Bob's Special Permissions", which is a bit of a kludge, but it solves the problem as long as you don't have too many exceptions like this. It is possible that what you think is an individual exception may actually be a subtle new role that you haven't considered. What happens when Bob quits? Does Bob's replacement also need that exceptional permission? If so then you actually have a role with one member, not an exception. – Joel Brown Dec 24 '15 at 12:58
  • @levi - Another possibility is that you really need to have a mixture of individual and role rights. In that case, your ROLE table can be subtyped into Individual and Group types, where the group type has zero to many members and the individual type has exactly one member. How you enforce these cardinality rules is up to you. It could be done declaratively in your database schema, in which case you need to change the pictured schema slightly. Or you might use application logic, in which case your schema still looks like what I've pictured above. – Joel Brown Dec 24 '15 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.