Best user role permissions database design practice? : <p>I want to design database for a web app in which user can access particular tabs based on the permissions given to a role.</p>

<p>What I have done so far is I created two tables <strong>USER_TABLE</strong> and <strong>USER_ROLES</strong>.</p>

<p><strong>USER_TABLE</strong> has below fields:</p>

<ul>
<li>id (primary key)</li>
<li>user_name</li>
<li>password</li>
<li>first_name</li>
<li>last_name </li>
<li>created_date</li>
<li>role_id_fk (foreign key)</li>
</ul>

<p><strong>USER_ROLES</strong> has below fields:</p>

<ul>
<li><p>id (primary key)</p></li>
<li><p>role_name (e.g. ADMIN, TAB1_USER, TAB2_USER)</p></li>
<li><p>created_date</p></li>
</ul>

<p>Here, the user having role_name "<strong>ADMIN</strong>" can see all the tabs, other users can access specific tabs only. </p>

<p>My question is do I need to create a table <strong>USER_PERMISSIONS</strong> having foreign key in <strong>USER_ROLES</strong> table with below fields:</p>

<ul>
<li>id (primary key)</li>
<li>permission_type (ALL, TAB1, TAB2....)</li>
</ul>

<p>or should I manage this at my code level? What would be the cons and pros of both approaches?</p>
