Dynamic User related SQL WHERE clause

[Ben_71]

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

[AlexisOlson]

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]

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

Email	ReportName	Criteria
ab.c@google.com	WSUM	WHERE Company IN ('A','B','C')
de.f@google.com	WSUM	WHERE 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] = 'ab.c@google.com'", 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]

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

Not possible. Use Import mode, not Direct Query.

[lbendlin]

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.