Goal

Sometimes you have a prefilter which you also would like to have on other tables in your application. For example, a user only has access to customers from a specific region. Now when he opens the outgoing invoices table, he should only see the outgoing invoices of the customers of that specific region. In fact, in every screen with some kind of customer data, you want to restrict the access to that data, based on that region.

This solution automatically generates prefilters throughout the application.

Solution

This solution consists of code for Dynamic Model which generates prefilters based on a prefilter which is locked. With the tag AUTHORIZATION_PREFILTERS_NOT_INHERITED you can exclude prefilters, target tables or references from this mechanism.

Example:

Let's say you have the following code as prefilter on customer:

exists (select 1 
        from region r
        where r.region_id = t1.region_id 
          and r.region_name = 'Northern Europe')

This solution will generate a prefilter on outgoing_invoice which code will look like this:

exists ( select 1 
         from (select t1.customer_id 
               from customer t1 
               where (exists (select 1
                              from region r
                              where r.region_id = t1.region_id
                              and r.region_name = 'Northern Europe')) ) a 
         where a.customer_id = t1.customer_id)

And also, a prefilter is generated on outgoing_invoice_line which code will look like this:

exists (select 1 
        from (select t1.outgoing_invoice_id 
              from outgoing_invoice t1 
              where (exists ( select 1 
                              from (select t1.customer_id 
                                    from customer t1 
                                    where (exists (select 1
                                                   from region r
                                                   where r.region_id = t1.region_id
                                                     and r.region_name = 'Northern Europe')) ) a 
                              where a.customer_id = t1.customer_id)) ) a 
        where a.outgoing_invoice_id = t1.outgoing_invoice_id)

Dynamic model inherit_authorisation_prefilters

This code generates prefilters in Tab Prefilter.

In the attachment you will find the code of this solution.