User Credential Passthrough to Oracle DB in Direct Query

Hello All,
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:

  1. Is there a better way to do this that we missed?
  2. 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?
    Thanks

Hi @simpsonk , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

This is a tough one and quite specific. There’s not a whole lot I can add to this unfortunately. Hopefully someone else in the community has dealt with this…

Thanks
Sam

This is from perspective of Microsoft SQL Query. Transform the script to be in oracle format.

SELECT
UserCode,
LoginID,
‘Mydomain'+LoginID as ActiveDirectoryName,
LoginID+’@Mydomain.com’ as Email

From MyUserTable

LoginID : is a variable that specify the username that forms part of the email address of the client
@Mydomain.com is the hard coded part , that is the part you will concatenate with the LoginID to get the email. You have to first understand how the email is formatted.
At the end of the execution , you will have LoginID@Mydomain.com

If you need any further clarification let me know.

Regards,

Hi @simpsonk, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Thanks for taking the time to respond @adesinamk .
I can get the report users email from the Power BI user principal name, and in my case could take the part before the @ symbol as the database User ID. However, Power BI does not let me manipuate that UserPrincipalName string in anyway that I could find. Cant be referenced in DAX. Even if I could, I would then need to pass that as a parameter to the Direct Query SQL so the query would execute against the databse with the logged in users credentials. Still struggling to make this work. It probably just cant be done.

@simpsonk.

I will still need a clearer picture on this. There are 3 steps

  1. formulate the LoginID@Mydomain.com to acts as the active directory email of the user

  2. Load it into power bi and make sure it has a relationship to other model in power BI

  3. Go to modelling in Power BI and create a role to give to the user . This will depends on your relationship.
    roledefined = USERPRINCIPALNAME()

If not then you will need to share your .pbix file and the data in excel

Hi @simpsonk, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further?

Also, here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.

I appreciate the response again adesinamk. I’m not sure there is a solution to this problem but thought I would ask the group. The challenge is not to apply RLS in the model on a per user basis, rather to let the database apply security based on the user running the report. Our database uses a VPD policy to secure each view in the database for Org and Labor sensitive data.

The RLS solution you proposed can work for some situations, however, that solution assignes each row to a single user.
Ken