I’m a new member this week and have real world challenge for a project Im working on. The desire is to use Direct Query to an Oracle DB (not AD enabled) and pass the Power BI report users client ID (UPN) to Oracle so the data can be filtered at the database based on the report users access level.
We looked at Kerberos delegation but found that it won’t work with a database that is not AD enabled.
We looked at using a procedure or function injected into the SQL defined for the Direct Query with some success. Adding the subquery into the SQL below with the hard coded ‘Client ID’ appears to make Oracle execute the session under that ID.
select * from dba_admin.FUNC_CLIENT_ID_CONTEXT(‘CLIENT_ID’)) client_id from SCHEMA.TABLE
My question to the expert here is twofold:
- Is there a better way to do this that we missed?
- Is there a way I can capture the UserPricipalName (UPN) of the report user and pass that into the SQL used in the direct query?