Storing user permissions on rows of data

Question

I have a table with rows of data for different experiments.

experiment_id    data_1    data_2
-------------    ------    -------
      1
      2
      3
      4
     ..

I have a user database on django, and I would like to store permissions indicating which users can access which rows and then return only the rows the user is authorized for.

What format should I use to store the permissions? Simply a table with a row for each user and a column for each experiment with Boolean? And in that case I would have to add a row to this table each time an experiment is added?

user    experiment_1    experiment_2    experiment_3    ..
----    ------------    ------------    ------------    --
user_1     True             False          False        ..
user_2     False            True           False        ..
 ..

Any reference literature on the topic would also be great, preferably related to sqlite3 functionality since that is my current db backend.

I wouldn't be adding columns per experiment. But that's me. See [Junction Tables](http://stackoverflow.com/a/32620163) ignore the csv part. The rest applies — Drew, Sep 19 '16 at 16:12
Have you tried looking at a solution involving "cross reference tables"? I.e. Tables "Experiment", "User", "User_Experiment_Permission" ? — Frito, Sep 19 '16 at 16:14
you need to learn about [database normalization](https://en.wikipedia.org/wiki/Database_normalization). That should guide your eventual design: tip: what you're proposing will work, but will be a maintenance nightmare. — Marc B, Sep 19 '16 at 16:15

score 0 · Answer 1 · answered Sep 19 '16 at 16:22

0

I'm not 100% sure what all will work best for you but in the past I find using a solution as follows to be the easiest to query against and maintain in the future.

Table: Experiment
Experiment_Id  |  data_1  |  data_2
-----------------------------------
 1             |  ...     |  ...
 2             |  ...     |  ...

Table: User
User_Name  | Password  | ...
----------------------------
 User1     | ...
 User2     | ...

Table: User_Experiment_Permissions
User_Name | Experiment | Can_Read | Can_Edit
--------------------------------------------
 User1    | 1          | true     | false
 User2    | 1          | false    | false
 User1    | 2          | true     | true
 User2    | 2          | true     | false

As you can see, in the new table we reference both the user and the experiment. This allows us fine grain control over the relationship between the user and the experiment. Also, if this relationship had a new permission that arose, such as can_delete then you can simply add this to the new cross reference table with a default and the change will be retrofit into your your system :-)

answered Sep 19 '16 at 16:22

Frito

1,423
1
15
27

i think this way it will not scale, it is good for small number of users and permission, but in case this table will have a big number of users and permission this will make it hard to update, delete records – Amr Magdy Sep 19 '16 at 16:49
@AmrMagdy That can be true. We really don't know what the OPs scale / performance requirements are. There are always considerations and optimizations that will be needed for software to function at scale. Thanks for pointing out the the consideration! – Frito Sep 19 '16 at 16:53
I think this is a more elegant solutions than what I had proposed in the question. The issue is more so using the appropriate convention rather than something that will work. Scalability is not a huge concern, I would guess that at most there would be 100 users and 100 experiments. – nven Sep 19 '16 at 17:31
@AmrMagdy What about it won't scale? The schema is normalized (except for possibly the `data_#` columns) and most RDBMS' scale plenty fine to millions of rows. – Colonel Thirty Two Sep 20 '16 at 00:53

score 0 · Answer 2 · answered Sep 19 '16 at 16:42

It depends on the way you will use the permissions for.

- In case you will use this values inside a query you have two options for example to get the users with specific permiss

Create a bit masking number fields and every bit will represent permission, and you can use AND/OR to get whatever combinations of permissions you need.
- Advantage : small size, very efficient
- Disadvantage: complex to implement
Create a field for each permission ( your solution ).
- Advantage : To easy to add
- Disadvantage: Have to edit schema with each permission

- In case you will not use it for any query and will process it at the code you can just dump a JSON into one column include all the permission the user has like :

{"experiment_1": 1, "experiment_2": 0, "experiment_3": 1}

The JSON method is exactly what I was thinking of doing. I'd have a table with the primary key being username and the permission column being a JSON blob. The only problem is that I'll have to parse the json strings, generate a tuple containing all the experiment_ids and then make the SQL call. So it'll be two calls with some python overhead in the middle. I was worried that although this word be easy to implement it wouldn't scale extremely well. — nven, Sep 19 '16 at 17:28

Storing user permissions on rows of data

2 Answers2