I'm currently in the process of setting up roles where there are regular users, admins, and super admins, each of which have special permissions. I deferred to this post on how to do so.

It basically comes down to either setting up a few tables in addition to the User table: Roles (second table), UserRoles (join table) or creating boolean attributes for the User model: admin and super_admin. While I'm capable of doing both what should I keep in mind in keeping my app secure? Is one better than the other? Explain.

I ask this because as the the second answer of the SO post states:

It really depends on what you wish to do with your admin role. The first option, I would say is a bit secure as the admin role is a unique model in itself.

The second option is straightforward and would help you get going with the least effort. However, if your users figure out the boolean variable and a way to set it, any user can become an admin and access areas you don't want them to.

Before coming to this conclusion I wanted to get a second opinion on the matter.

  • 5
    I'm not sure why the structure of the data would have any effect on security. It's not like we're talking about different encryption schemes, or the rules governing who has access to what data. Is it possible you've left out some details we need to fully understand the problem? – Ixrec Dec 19 '15 at 0:13
  • @lxrec - Updated answer with additional information. – Carl Edwards Dec 19 '15 at 1:05
  • 4
    That SO answer seems completely baseless to me. Why would a string in the Roles table be any more secure than a boolean in the User table? They're both just values in a database. Unless your database supports different permissioning schemes for each table or something. – Ixrec Dec 19 '15 at 1:36
  • 1
    Just one thing to keep in mind: depending on your application and security model: there might be multiple roles, and even a case where there isn't a "role/user with all permissions". – Sjoerd Job Postmus Dec 19 '15 at 10:49

This decision has nothing to do with security, but your UserRoles idea is more extensible whereas the boolean attributes are simpler.

As with almost everything (and I'm getting more and more tired of having to point this out), there is no blanket rule making one "better" than the other in all cases.

Just pick one. As long as the code you write to interface with the database is isolated, you can easily change it in the future if you find you need to.

For now, you may wish to consider a column level ENUM('normal', 'admin', 'superadmin').

| improve this answer | |
  • 1
    I wouldn't say that "Just pick one" is the best approach. It can tend to be a good approach but not the best one. I think that it comes down to what are the non-functional requirements of your application based on Quality Attributes. When two of these compete with each other is when you typically have a design dilemma. Picking the right design choice becomes a matter of which quality attribute has more priority for you. – maple_shaft Dec 23 '15 at 11:55

keeping my app secure?

It depends somewhat on your definition of "secure". Does it include being able to go back in time and see who had particular permissions months ago? If so, the join table is ideal. Add validFromDateTime and validToDateTime fields and you have a complete audit history.

But if you just need to know who has what permission right now, the Enum solution suggested by Lightness Races in Orbit looks preferable to booleans (Only because the booleans let one user have multiple roles at the same time - a consideration if this was the Code Review forum). As others have rightly said, there is no inherent security difference between the boolean flag and join table approaches.

| improve this answer | |

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