Best User Role Permissions Database Design Practice

Best user role permissions database design practice?

As krokodilko wrote in his comment, it depends on the level of flexibility you need.

I have implemented role based permissions for one of my clients as follows:

1. User (user id (PK), user name (unique), password (salted and hashed!), first name, last name, phone etc')
2. Role (role id (PK), role name (unique), role description)
3. Permission (permission id (PK), permission name (unique)) - the tabs / screens / actions goes here
4. User To Role (user id, role id) - PK is both columns combined
5. Role to Permission (role id, permission id) - PK is both columns combined

But my requirement was to be as flexible as possible, and it is a system that is still growing (6 years and counting).

I guess a lot of applications can have the user to role as a one to many relationship, instead of a many to many like in my case, but I wouldn't go hard coding permissions or role to permissions in any application.

Further explanation: Role based security database design on What the # do I know?

Best Practice for Designing User Roles and Permission System?

The pattern that suits your needs is called role-based access control.

There are several good implementations in PHP, including Zend_Acl (good documenation), phpGACL and TinyACL. Most frameworks also have their own implementations of an ACL in some form.

Even if you choose to roll your own, it'll help you to review well factored solutions such as those.

User-Role-Permission based database design

But this way no. of rows will be more.

Databases are good in managing a huge number of rows, million of rows is not a problem.

Is there any way to store tabs in a array like structure?

No no no - don't go that way.
This breaks the Database normalization rules, esspecially the first normal form thast says:

First normal form (1NF) is a property of a relation in a relational
database. A relation is in first normal form if and only if the domain
of each attribute contains only atomic (indivisible) values, and the
value of each attribute contains only a single value from that domain.

This aproach (storing many values in one colum) seems attractive at first glance, but some day you will fall into trobles when someone will ask you for a query "give me all users who have access to tab TAB-233".
Some other disadvantages of this approach:

1. each query must parse the array
2. RDBMS can't use indexes on multivalue columns, each query like SELECT ... WHERE tab='TAB-333' must always use a full table scan and is not scaleable.
3. how to update or delete ? Instead of simple UPDATE ... WHERE or DELETE .. WHERE ... each update command must unpack the array, change/delete one value and pack and store changed array back to the column.
4. You can't use any referential integrity on this column.

Please see an aswer to this question for more details.

---

In your model there are the following relationships:

1. The user has roles
2. The role has permission codes
3. The permission code has tabs

---

But there are rather n:n relationships, not 1:n, because I can see that:

1. The role has many users (because several users can have the same role)
2. The permission code has many roles (because several roles can have the same permission code)
3. The tab has many permission codes (because several permission codes can have the same tab)

---

So in this model there would be the following tables:

• USERS(id, user_name, password, etc)
• ROLES(id, role_name, etc )
• PERMISSION_CODES( id, code_name, etc )
• TABS( id, tab_name, etc )
• USER_ROLES( user_id, role_id )
• ROLE_PERMISSIONS( user_id, perrmission_id )
• PERMISSION_TABS( permission_id, tb_id )

user, role, permissions and a specific group RBAC?

Based on your question and answer to my comment, here is the model I came up with:

This meets your requirements. The main difference with your model is how the groups are setup.

• Group has a 1:1 relation with Role, which can be null (i.e. a group can exist even it no role is associated to it).
• In each Group, you have a foreign key to the Role table.
• If you need to know the roles a users has: individual roles through User_has_Role + role attached to each group the user is a member of (through Group_has_User).

Best practice for designing user/roles/people in database

How about getting rid of the user_role and user_role_link tables, since you already have the required information in the other tables and then you don't have to maintain all these links:

user (id, email, username, password)
company (id, user_id, name, ...)
client (id, user_id, first_name, last_name, ...)
employee (id, user_id, first_name, last_name, start_date, end_date, ...)

Note there is no such thing as "role" anymore, because clients, companies, and employees "have a" user. So what you have is an aggregation model in the above.

Going further than this, what you might be proposing is an object-oriented inheritance approach. Seen through that filter, you would just have user, company, client, and employee, where the latter three are "kinds of" users. In fact company, client and employee can actually share the same IDs with user in this case since they are essentially the same object! They are all derived from user and are users. (But unless you know what you're doing I wouldn't necessarily advise this option.)

So those are the two options I see here - aggregation or inheritance.

Notice if you lose the link tables, you can still figure out which type of object you have by doing an outer join on all three of the tables and checking which table's ID field comes back with an ID as opposed to a null. Once you know the type you have, the type-specific code has very straightforward querying, with just a single join, user to whichever derived table. For example, let's say you know you have a client, the client code can query directly against the client table and just do one join to user and that's all that's needed.

For more information on some common ways of laying out object-oriented data in a conventional database, the reference material on Python's SQLAlchemy provides some general approaches:

http://docs.sqlalchemy.org/en/rel_0_9/

SQLAlchemy is a library that is specifically designed to aid with object-relational mapping issues, so you can see how they do things. I'm suggesting this as a reference for that reason, and not for any Python connection.

How to design Users, Roles and permission schema?

This is a loaded question, setting up members, roles, etc. is no small feat. I suggest you take a look at already written libraries to see if they may suit your needs. What you should start googling for, is 'Access Control Lists' (acl) for short.

---

Related Topics