Forum Discussion

Ben_71's avatar
Ben_71
Regular Visitor
4 years ago

Dynamic User related SQL WHERE clause

Hello friends:

 

I am sure this has been asked before but i could not find a solution. So here goes:

 

We have data in a SQL Indexed View that is grouped inside of the Query Editor. The requirement is to present the data with a dynamic WHERE clause which is also defined in a separate setup table. For e.g., the table has user email, and the WHERE statement.

 

I would like to setup RLS in a way that i can find out who the user is at runtime, determine the associated WHERE clause, append that to the main table (in a way SQL injection) and present the data. If the WHERE clause changes in the table, the data shown on the screen changes.

 

Also to keep in mind, the data is fetched from the main view using Import Method and not Direct Query because of the volume of unfiltered data and to help with performance.

 

Please help!!. I have spent a lot of hours trying to do all sorts of things, but cannot get past this hump.

 

Thanks a bunch.

Ben

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago
    the data is fetched from the main view using Import Method and not Direct Query

    This right here is your show stopper. You cannot have dynamic query parameters in import mode. It is technically impossible. It only works in Direct Query mode.

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    If you're using Import, then the only option is to load all of the data (without user filtering) and then apply RLS as appropriate on that unfiltered data.

     

    lbendlin is correct. Import doesn't allow for dynamic querying, full stop. DirectQuery should work in this scenario, possibly even without needing to write explicit WHERE clauses if table relationships are set up correctly.

    • Ben_71's avatar
      Ben_71
      Regular Visitor
      4 years ago

      Thank you. Unfortunately, the setup table (UserNameQuery) that houses the WHERE Clause has a format like this:

      EmailReportNameCriteria
      [email protected]WSUMWHERE Company IN ('A','B','C')
      [email protected]WSUMWHERE Company = 'A' AND ReportingRegion LIKE 'XX%'

       

      So, what i did was to import the main table using DirectQuery. Then i went into Advanced Editor:

       

      let
      CurrentUser = UserNameQuery, //UserNameQ
      ListOfUsers = Table.ToList(CurrentUser),
      StringOfUsers = Text.Combine(ListOfUsers,""),
      Source1=Sql.Database("xxx-container.database.windows.net", "yy-container", [Query="SELECT [QUERYCRITERIA] FROM [dbo].[Report_Criteria] WHERE QRYNAME = 'WSUM' AND [PrincipalName] = '[email protected]'", CreateNavigationProperties=false]),
      ListOfParts = Table.ToList(Source1),
      StringOfParts=Text.Combine(ListOfParts,""),
      Source = Sql.Database("xxx-container.database.windows.net", "yy-container", [Query="SELECT * FROM dbo.v_WIRESUM_Indexed WITH (NOEXPAND) " & StringOfParts, CreateNavigationProperties=false])
      in
      Source

       

      When i come back to the main screen and refresh data, i get the following:

      Failed to save modifications to the server. Error returned: 'DirectQuery partition 'Query2-2abf1210-e4eb-4ca5-8b0b-110bfdc665fd' has '2' datasource reference(s) in its expression which is not allowed. '.

       

      Please advise.

       

      thanks

      Ben

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        "  what i did was to import the main table using DirectQuery"

         

        Not possible. Use Import mode, not Direct Query.