Direct Query SSO connector

[tens]

Hi Qlik Community, 

I'm working with on new project and my IT team had some contraints about it. We're working on qlik sense client managed.  

We need to develop dashboard containing sensitive data.

 As it's sensitive data, here's the contraints I have to design architecture: 

- Qlik Sense app can't store any data ( no qvd and standard loading is possible) the data must remain in an Oracle database and qlik is used only to query in database when graph has to render. Section Access on binary load is not a viable solution for my project manager. 

- qlik user who's consuming the dashboard are authorized to see only the data they Can request. The authentification on the database is handle by Kerberos. 

For instance, my user A is authorized to watch data for USA in Oracle, so when he watchs his dashboard only data about usa have to be display in graph even if there are data for Europe and Asia in oracle. For my user b who's authorized to see data for Europe in Oracle, only data for Europe will be display for him. 

To handle these constraints, I was thinking to use Direct Query feature but it seems that Direct Query feature isn't compatible with sso, pass-trough: 

All of the users using the same app with the Direct Discovery capability use the same connection. Authentication pass-through and credentials-per-user are not supported.

Source: https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/DirectDiscovery/limitatio...

Does anyone have resolved similar issue ? I know that qlik direct query on premise is very restrictive and I'm quite dubitative there's a solution that can cover all these constraints with qlik ? Does developping analytics connection or developing a Qlik Data Direct Query Connector that handle sign on is possible with an qlik sdk ? 

For our use case, we can develop a specific direct query connector if needed if there's an sdk that exists or anything else to do the job.

Thanks you very much for your help and any indications that can help us. 

Tens

[M_B]

Maybe you can join the userids/usernames as a column, map them to the country/department/group in the Oracle view/query and match the logged in user?

See this post about getting the user: https://community.qlik.com/t5/New-to-Qlik-Analytics/Get-User-Name-in-Qlik-Sense-SSO-connection/td-p/...

The following code is taken from the "License Monitor" app:

LIB CONNECT TO 'monitor_apps_REST_user_condensed';

User:
Load
LOWER(userDirectory & '\' & userId) AS UserId,
[name] AS [User Name],
userDirectory as [User Directory]
;
SQL SELECT 
"userId",
"userDirectory",
"name"
FROM JSON (wrap on) "root";

 You can then try adding Only({<UserId={'$(=SubField(OSUser(), '=', -1))'}>}[User Name]) for user names or Only({<UserId={'$(=SubField(OSUser(), '=', -1))'}>}) for user ids to a chart as a measure to filter based on the user that is logged in or just join them on userid = SubField(OSUser(), '=', -1) in script.

Hope this helps somehow.

[tens]

Hi M_B,

Thanks for your solution but it won't work for our use case. Indeed, it's a self bi environnment and I'm not the report developper. I'm just administrating the Qlik Sense Cluster and developpers are just standard user that can access only data they are accredated for.

I have to find a solution that restrict the access to this data for developper and consumer by an administrative way. I can't use a tip in script or via part, because it can be changed/altered by any developper on the platform and they have to get access only to the data they can see in Oracle Database :(. I saw that no improvments will be made by Qlik on direct Query in on premise version. So I'm quite stuck my only solution seems to find a way to develop my own connector if there's an sdk toolkit ( and try to find a way to handle the conditions)  or announce that the project can't be securely on Qlik and try other solution on the market ... 😞