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>It depends on the way you will use the permissions for.<br>
<br>
 - In case you will use this values inside a query
you have two options for example to get the users with specific permiss </p>

<ul>
<li>Create a <a href="https://en.wikipedia.org/wiki/Mask_(computing)" rel="nofollow">bit masking</a> number fields and every bit will represent permission, and you can use AND/OR to get whatever combinations of permissions you need.
<ul>
<li>Advantage : small size, very efficient</li>
<li>Disadvantage: complex to implement</li>
</ul>
</li>
<li>Create a field for each permission ( your solution ).
<ul>
<li>Advantage : To easy to add</li>
<li>Disadvantage: Have to edit schema with each permission</li>
</ul>
</li>
</ul>

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

<pre><code>{"experiment_1": 1, "experiment_2": 0, "experiment_3": 1}
</code></pre>

python mysql django database sqlite3