Modeling object permissions in database

Discussion in 'Mixed Languages' started by Stannieman, Mar 9, 2016.

  1. Stannieman

    Stannieman MDL Guru

    Sep 4, 2009
    #1 Stannieman, Mar 9, 2016
    Last edited: Mar 9, 2016
    Hi all,

    I think I can use some advice because I believe I'm in a situation where no solution is 100% right.

    I'm building a system that collects sensor data and makes part of the data visible to it's owner through a website. All data of all sensors must be visible to the administrator of the website. Think of it as a smart thermostat. The owner of the thermostat can see the data of it's own thermostat, but maintenance people can view data of all thermostats their company sold. Our system works a little bit different and it are not thermostats, but the principle is the same.

    The idea was to build a REST API, so all data must go through REST calls. Also the website itself communicates with the database through this REST API.

    Our basic security mechanism will look something like this:
    - Users can have 0 or more roles.
    - Different user roles can access different pages -> we have page permissions and each role can be assigned to several pages.
    - Different user roles can do different REST calls -> we have REST call (url + method) permissions and each role can be assigned to several calls.
    - Different user roles can access different sensors through the same REST call. For example GET /users/{id}/sensors gives info about all of the sensors for user with id {id}. This only if this is your own sensor of if you have permissions for this sensors through a role.
    - Different users can change different users data. For example PUT /users/{id} should work if you are logged in as user {id} or if you have a role that has permissions for user {id}.

    We can now see 4 'securable objects', being a users, sensors, REST calls and pages.
    To give the permissions I now have 4 tables:
    - role_page_permissions: this says which roles can view which age.
    - role_rest_permissions: says which roles can do which rest calls.
    - role_sensor_permissions: says which roles can view which sensor data.
    - role_user_permissions: says which roles can edit which users.

    Problem with this approach
    There are a few things I don't like about this solution though.
    One is that although the permissions through roles are solved now, the permissions for your own sensor or account are not. When an admin wants to change your account details he can because there's a permission from the admin role to your user through role_user_permissions. However when you're changing your own details there is no permission, only the fact that you 'own' your own account. The same goes for sensors: is giving access to a sensor's data just because it's assigned to your account a clean solution? A solution could be to make a user_user_permissions and a user_sensor_permissions table that gives explicit permission to your own account and sensor.

    But can you see what happens then? When we invent new securable objects in the future we have a continuously growing number of other tables for permissions that are cluttering our database schema.

    Possible solution
    One solution I've thought of is threating all 'securable objects' the same. Then I can use 1 role_securable_permissions table and 1 user_securable_permissions table. (This is starting to look like how AD does things.) The only thing I need to take care of then is that all users, sensors, pages and rest calls have a 'global unique ID' and not just within their own table, but that's easy to do. The only other disadvantage I can think of is that you can't set a foreign key constraint between the permissions tables and the securable objects and this would break Hibernate.
    So... any opinions on this? I think I'm going for the last solution, but If someone has already some experience with this then any input is appreciated.
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...