Storing user permissions on rows of data
<p>I have a table with rows of data for different experiments.</p>

<pre><code>experiment_id    data_1    data_2
-------------    ------    -------
      1
      2
      3
      4
     ..
</code></pre>

<p>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.</p>

<p>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?</p>

<pre><code>user    experiment_1    experiment_2    experiment_3    ..
----    ------------    ------------    ------------    --
user_1     True             False          False        ..
user_2     False            True           False        ..
 ..
</code></pre>

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

<p>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.</p>

<pre><code>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
</code></pre>

<p>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 <code>can_delete</code> 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 :-)</p>

python mysql django database sqlite3