13

Is representing user permissions better in the user table or better in its own permissions table?

Permissions in User table
Putting permissions in the user table means making a column for each permission in the user table. An advantage is queries should run faster because no joins are necessary when relating users to user permissions. A disadvantage is that having many permissions columns clutters the user table.

Permissions in Permission table joined to User table with many-to-many relationship
Doing it this way cleanly separates out the permissions from the user table, but it requires a join across two tables to access user permissions. Database access might be slower, but database design seems cleaner.

Perhaps keeping permissions in a separate table is better when there are many permissions. What are other considerations in making this decision, and which design is better in various situations?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
steampowered
  • 11,809
  • 12
  • 78
  • 98

2 Answers2

29

The standard pattern for access control is called Role Based Security. As both the number of users and the number of different types of permissions you need grows, the management of your user-to-permissions links can become increasingly difficult.

For example, if you have five administrators and fifty users, how do you keep the permissions of each group in synch? When one of your users is promoted to an administrator, how many edits do you need to make? The answer is to create two intersections: users-to-roles and roles-to-permissions.

This solution is described (including entity relationship diagram) in my answer to this question.

enter image description here

Community
  • 1
  • 1
Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • 3
    This is a robust answer for a more complex application. As @Nurkeiwicz notes, a simple application may not even need a rolls table or permissions table. – steampowered Oct 29 '12 at 17:53
  • 1
    nice diagram, what tool did you use? – dangerousdave Dec 13 '13 at 10:38
  • 1
    @dangerousdave - Sorry for delayed response, I've been out of town. I use Visio with custom smart shapes that I built to use the James Martin ERD visual convention and a custom line pattern that gives it a hand-drawn look. – Joel Brown Dec 16 '13 at 17:16
  • 1
    I have always missed the Role Member table. This response just made my day because it solves so many problems that may arise later when the project gets bigger. – Cristian Aug 08 '15 at 16:38
  • @steampowered Can this approach use an incremental type of user role permissions (build on from the other). Consider these permissions: search, post, comment, manage users. I want to have: guest (search), user (search, post, comment), admin(search, post, comment, manage users). Can I say this: guest (search), user is guest + (post, comment), admin is user + (manage users)? Would this be a programming mess? – Cristian Aug 08 '15 at 16:43
  • @Cristian - You could do something like this without _too_ much mess by using **nested sets** or **adjacency list** for the roles. This way a role is defined by what is assigned to it plus what is assigned to any of its descendants. I would say the programming wouldn't be too awful, but the manual management of the data might start to get tough for the user, unless they are very, very good organizers. Programmers are used to thinking this way because of code reuse, but others may find the concept daunting. – Joel Brown Aug 08 '15 at 18:41
  • @JoelBrown Would you mind providing and example of nested sets or adjacency lists? I understand the idea but don't see how to put it into a DB (especially regarding the descendants). – Cristian Aug 09 '15 at 00:11
  • @Cristian That's too complex a thing for much of an answer in a comment, but here goes: Have a look at my answer to [this question](http://stackoverflow.com/questions/7420885/how-do-i-design-a-database-to-store-properties-selecting-attributes-by-synonyms/7423459#7423459) and imagine `ROLE` being a hierarchy. This would add an optional foreign key on `ROLE` to itself (parent role). Then instead of joining `ROLE_MEMBER` and `ROLE_RIGHT` by the FK to `ROLE` alone, join it by a list of FKs found using a correlated subselect on the visitation number range of the parent `ROLE` of interest. – Joel Brown Aug 09 '15 at 00:37
  • 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 07:13
  • Wouldn't it be nice to split permission in operation and object, like suggested in [this answer](http://stackoverflow.com/a/7615512/258127)? That way, you can just combine an action (like 'create' or 'delete') with an object (like 'user' or 'post'), which IMHO makes it easier to determine a permission programmatically *and* makes it more clear what the permissions (combinations) are for the (admin) user. – Marcel Korpel Jan 11 '16 at 19:12
  • @MarcelKorpel If you need so much granularity in your access control that you want to maintain separate CRUD permissions for each _object_ in your system, then normalizing out the operation from the object makes sense. For many business systems, this is just way too much detail. People are not necessarily going to have create, but not update, or delete, but not read. Similarly, in many systems having delete permissions on an invoice line item will not be distinct from having delete permissions on an invoice header. – Joel Brown Jan 12 '16 at 02:39
  • @JoelBrown How to add additional different attribute to each role? i.e. `Phone` on `User` table, `2nd_password` on `Admin` table – Yosua Lijanto Binar Jun 19 '17 at 08:34
  • @YosuaLijantoBinar You can add columns to any of the tables in this model to store attributes that are important to your system. You have to be careful to add these columns to the most appropriate table, however. Make sure that the new column is _single-valued_ and _fully functionally dependent_ on the primary key of the table to which you are adding the new column. If the column is _multi-valued_ then you almost certainly need a new (child) table to hold those values. – Joel Brown Jun 19 '17 at 11:53
  • @JoelBrown I created subclass from `User` table, like `Admin` and `Customer` tables, is it good idea? How about the data integrity and redundancy? – Yosua Lijanto Binar Jun 19 '17 at 12:59
  • @YosuaLijantoBinar - It's probably a great idea for your application (code), but probably not a good idea for your tables (storage). In role-based security whether a user is an admin or a customer (or both) has everything to do with what records are in the `ROLE_MEMBER` and `ROLE_RIGHT` tables rather than what version of the `USER` table you are in. – Joel Brown Jun 19 '17 at 16:16
  • @JoelBrown yea I am confused, because it's violating data integrity. In subclass table, the only purpose is to add extra attributes not the role checking, the role (or permission) checking is still using RBAC. But the drawback is when the ROLE_RIGHT is removed, I need to manually remove extra attribute row using the application code. What do you think? Must I create a trigger? – Yosua Lijanto Binar Jun 19 '17 at 19:05
  • @YosuaLijantoBinar I think you could try using triggers, although a lot of people dislike triggers because they spread application logic around and make code harder to maintain. Another approach would be to put the necessary application logic in a stored procedure and wrap the various table operations in a single transaction to preserve data integrity. This is one of those things for which there is no simple and clean answer, unfortunately. – Joel Brown Jun 19 '17 at 20:10
  • @JoelBrown yeah badly I need extra attributes. Or do you have another approach for this situation? – Yosua Lijanto Binar Jun 20 '17 at 04:50
  • @JoelBrown I have new thread, let's discuss there https://stackoverflow.com/questions/44644449/better-way-to-add-extra-attributes-in-rbac-role-based-access-control-tables – Yosua Lijanto Binar Jun 20 '17 at 05:30
4

Your first approach is feasible when the number of different roles/permissions is relatively small. For example if you only have two types of users: normal and admin, a separate table looks like an overkill. Single is_admin column is sufficient and simple.

However this approach does not scale once the number of roles exceeds a few. It has several drawbacks:

  • user table becomes very "wide" having a lot of empty columns (wasting space)

  • adding new role to the system requires altering user table. This is cumbersome and might be time-consuming for large user database

  • listing user roles requires enumerating over all columns, as opposed to simple database query.

Tomasz Nurkiewicz
  • 334,321
  • 69
  • 703
  • 674
  • Enumerating permissions columns to show all rolls and running ALTAR command on the db - two things I didn't think of. The separate table quickly becomes more desirable. – steampowered Oct 27 '12 at 18:46
  • Comma separated roles column in users table often works fine for small and even medium sized systems. – jjxtra Sep 21 '22 at 00:12