Database Design – Role-Based Database Schema

The way this is normally done is with user roles and a role access matrix.

Each user has a role, saved in the database along with the user name, password, etc.

Then, there is a two-dimensional role access matrix somewhere, specifying for each role and for each screen field what kind of access is to be had by users who have that specific role on that specific screen field. The access may be:

• "none", meaning that the field should not be shown to users of this role
• "read-only", meaning that the field should be displayed, but not be editable, and
• "read-write", meaning that the field should be displayed and editable.

In a variation of this scheme, each user can have multiple roles, so the access that a user has to a certain field is computed to be the maximum access permitted by any of the roles held by the user. When this variation is used, the 'roles' may be called 'groups', in the sense that a user may belong to several groups simultaneously.

In yet another, more sophisticated but even more complicated variation, we introduce permissions as an intermediate step, replacing the role-access matrix with a much smaller role-permission matrix. In this scenario, each field has a required permission for "read-only" access, and another required permission for "read-write" access. (These required permissions can be hard-coded for each field: they don't need to be configurable.) If a user has a role which has none of the required permissions, the user does not see the field at all. So, for example, an "email address" field may require some "view sensitive information" permission in order to have "read-only" access to it, and some "edit sensitive information" permission in order to have "read-write" access to it. Each user has or does not have these permissions depending on their role. Presumably there will be many fields that will require the same permissions for the same kind of access, so the total number of permissions will be very small, which in turn means that the role-permission matrix will be small.

So, one of these approaches should solve your problem nicely. In fact, I would be willing to bet that they more closely parallel what your client really wants to accomplish: they probably don't want to tailor the type of access of every single user to every single screen field, they probably have some user groups and possibly some field groups vaguely in mind, and they want different groups of users to have different types of access to different groups of fields.

The role-access matrix or the role-permission matrix can be stored in the database, or they can be stored in an external configuration file, or, if you are building your user interface using some scripting language like PHP, they can even be placed directly in the code, and let the customer modify the PHP source file to alter the permissions.

Your current design is this:

You should ask yourself:

• does entity PERMISSION_LEVEL represent actual levels?

Is they are actual levels it means that when a user is granted two different roles and both roles have the same command but with different levels, the app should take the highest level of them. In role-permission models with no permission level, set logic is used, so if an user ends up with the same permission twice, it doesn't matter. What matters is that the permission exists in the set, not how many times it exists. But your model has permission level and so you should decide what to do when a user ends up having the same role command more than once with different permission levels.

In the other hand in the update of your question you mention a new entity that is not shown in the model which is OBJECT.

• Where does object fall into the model?

For what I can see the model would be updated like this:

That would actually considerably increase the row number of the resulting table. But that should not worry you too much if you create the proper indexes and FK. What does worry me is the complexity assembling the roles but at least that should be done only once.

I'm not sure if I ended up raising more questions that the answers I gave.