title: MySQL: what is the most efficient way to retrieve all permissions from RBAC database?
body: <p>Good day.</p>
  
  <p>I have a role-based access control database and looking for an efficient way to get all permissions user has. This is the schematics:</p>
  
  <p><img src="https://i.stack.imgur.com/CHMPE.png" alt="RBAC database design"></p>
  
  <p>Now I need to get all user's permission in the most (possibly) efficient way. Tried this query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT p.name
    FROM permission p
    WHERE p.id = (
      SELECT rpl.permission_id
      FROM role_permission_list rpl
      WHERE rpl.role_id = (
        SELECT url.role_id
        FROM user_role_list url
        WHERE url.user_id = 2
      )
  )
  </code></pre>
  
  <p>But this fails. Since sub-query returns more than 1 result. Tried to think of a join — couldn't figure it out.</p>
  
  <p>Thanks in advance.</p>
  
  <p>PS: In future there will be an permission_overrides table, since one standalone access-controlled entity may have exclusive set of permissions.</p>