title: how to implement row level security in spring data jpa using hibernate filter or other ways?
body: <p>One of the very important problems in information softwares is the existence of users with different roles with different duties and access levels. For instance, think of an organization with the structure (hierarchy) like below:</p>
  
  <pre><code>[Organization Role ]     [Organization ID]
   CEO                        org01
     Financial Assistant      org0101
             personnel 1
  
     Software Assistant       org0102
             personnel 2
  
     Commercial Assistant     org0103
             personnel 3
  </code></pre>
  
  <p>Imagine that this organization has a system that manages personnel’s information. The rule of showing personnel’s information in this system is that each user can see personnel’s information of the organizations that he has access to; For example, ‘user1’ has access to ‘Financial Assistant’ and ‘Commercial Assistant’ levels, so he can only see information of ‘personnel 1’ and ‘personnel 3’. Likewise, ‘user2’ only has access to ‘Commercial Assistant’ level, so he can only see information of ‘personnel 3’. Therefore, each of the users in this system has a specific access level.
  Now consider that in this system, each user only sees the personnel information that he has access to after he logs in. Having that the table structure of this system is like this:</p>
  
  <pre><code>[Organization]
  id
  code
  name
  
  [Employee]
  id
  first_name
  last_name
  organization_id
  
  [User]
  id
  user_name
  password
  
  [UserOrganization]
  user_id
  organization_id
  </code></pre>
  
  <p>the below query would be enough to get the proper personnel information results for each user:</p>
  
  <pre><code>select *
  
  from employee e 
  
  where e.organization_id in
  
  (select uo.organization_id
  
   from user_organization uo
  
   where uo.user_id=:authenticatedUserId)
  </code></pre>
  
  <p>as we can see, the below condition defines the access logic for showing the right data:</p>
  
  <pre><code>e.organization_id in
  
  (select uo.organization_id
  
   from user_organization uo
  
   where uo.user_id=:authenticatedUserId)
  </code></pre>
  
  <p>This kind of access level is also known as ‘Row Level Security’ (RLS).
  On the other hand, the corresponding repository class, probably has a couple of methods responsible for reading the data, all of which has to fulfill the proper access level condition. In this case the access level condition will be repeated in some places (methods). It seems that using a ‘hibernate filter’ would be a proper solution for this problem. The only thing needed is a filter that gets the id of the authenticated user and executes the ‘enablefilter’ command before every read method.</p>
  
  <pre><code>@Filters( {
    @Filter(name=“EmployeeAuthorize", condition="(organization_id in (select uo.organization_id from user_organization uo where uo.user_id=:authenticatedUserId) )  ")
  } )
  </code></pre>
  
  <p>Now the question is that, is the proposed solution right? If yes, how can this method be utilized in spring data?
  PS: Given that we don’t want to be dependent on databases, implementation on the database side cannot be a candidate solution, for this reason we’re obliged to implement it on the application side (level).</p>