Menu
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?
415
Role will be calculated as sum of permissions. Example : If user 'Ketan' having permission of 'User-Add' (bit=1) and 'Blog-Delete' (bit-64) so role will be 65 (1+64). If user 'Mehata' having permission of 'Blog-View' (bit=128) and 'User-Delete' (bit-4) so role will be 132 (128+4).
A user-defined user role simplifies the administration and management of privileges by allowing the administrator to group authorities and privileges into a single role and then grant this role to the users that need those authorities and privileges to perform their jobs.
A role is a collection of privileges that can be granted to one or more users or other roles. Roles help you grant and manage sets of privileges for various categories of users, rather than grant those privileges to each user individually.
User Roles give Administrators the ability to control what users can do within the system, without giving full administrator access. A Role is a collection of Permissions which could be based on a job function. Permissions are assigned to Roles and Roles are assigned to Users.
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.
54
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.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
