3 Replies Latest reply on Jul 30, 2015 5:24 AM by Bas Piepers

    Row filter constraint not performed in case view table references source model table with different name

    Bas Piepers Novice

      I have a very simple VDB with a source model and a view model. I have multiple data roles and one of them is a data role that is applied to all users. In this data role I have set up row filter constraints for a number of tables. Say my source model is called "source" and my view model is called "generic". If I have a view table called "TEST" which just has the query transformation set to:

       

      select * from source.TEST

       

      I can set a constraint that limits the rows by some kind of value in another table like:

       

      <permission>

           <resource-name>generic.TEST</resource-name>

           <condition constraint="true">TEST_DIM_ID IN (SELECT UM.TEST_DIM_ID FROM source.RESTRICTED_RECORD_DIM AS UM WHERE UM.USERNAME=ucase(substring( user(), 1, locate( '@', user())-1)))</condition>

      </permission>

       

      Note that I am restricting generic.TEST (so the table in the View Model) based on a table in the Source Model (source.RESTRICTED_RECORD_DIM).

       

      However why can't I use the same type of condition on a View Model table that has its query transformation set to a table with a different name than the table in the View Model? Consider the following example: the table in the View Model is called FOO, it has a query transformation of:

       

      select * from source.BAR

       

      The following constraint will NOT be executed:

       

      <permission>

           <resource-name>generic.FOO</resource-name>

           <condition constraint="true">TEST_DIM_ID IN (SELECT UM.TEST_DIM_ID FROM source.RESTRICTED_RECORD_DIM AS UM WHERE UM.USERNAME=ucase(substring( user(), 1, locate( '@', user())-1)))</condition>

      </permission>

       

      It will ONLY be executed if I put the constraint on the source table instead, so like this:

       

      <permission>

           <resource-name>source.BAR</resource-name>

           <condition constraint="true">TEST_DIM_ID IN (SELECT UM.TEST_DIM_ID FROM source.RESTRICTED_RECORD_DIM AS UM WHERE UM.USERNAME=ucase(substring( user(), 1, locate( '@', user())-1)))</condition>

      </permission>

       

      Why is that? Should it not restrict the records as well if I put it on the View Model's table here?

       

      PS.: you can assume that the referenced columns are existent in the mentioned tables (so TEST_DIM_ID in the constrained tables and USERNAME in the Source Model table).

       

      Thanks