Forum Discussion
Role-Level Security for Oracle & Power BI
Hi Team
We have built a Power BI dashboard using Oracle data and would like to apply the same role-level security that exists in Oracle to this dashboard.
Thanks & Regards
Pravin Angane
Hi u503294 ,
Great question. Integrating database-level security with Power BI is one of the most critical architectural decisions you will make.
While the other answers correctly identify the two main paths (DirectQuery vs. Import), the real challenge usually lies in the infrastructure setup, specifically the Gateway.
Here is the "Deep Dive" on how to actually make this work, depending on your priority.
Path 1: True Inheritance (DirectQuery + SSO)
If your strict requirement is "apply the same security that exists in Oracle," this is your only real option. You do not want to rebuild logic; you want Power BI to pass the user's ID to Oracle so Oracle can enforce its Virtual Private Database (VPD) rules.
The Hidden Hurdle: The Gateway Simply selecting "DirectQuery" is not enough. By default, the Gateway connects to Oracle using a single service account (e.g., PBI_Service_User). This means Oracle sees every request as that one user, effectively bypassing your RLS.
To fix this, you must configure Single Sign-On (SSO) on your On-Premises Data Gateway:
Kerberos Delegation: You configure the Gateway to "impersonate" the Power BI user (via Active Directory) when talking to Oracle.
Result: When User A views the report, Oracle receives the query as "User A". Oracle then applies the RLS policies, and returns only the rows User A is allowed to see.
Path 2: Performance First (Import Mode)
If DirectQuery is too slow (which is common with complex Oracle RLS logic), you must use Import Mode.
The Trade-off: You cannot "inherit" the security. You must replicate it.
The How-To:
Create a view in Oracle that maps [Username] to [Allowed_Region] or [Allowed_Dept].
Import that table into Power BI.
In Power BI Manage Roles, use the DAX function: [Email] = USERPRINCIPALNAME()
Set up a relationship to filter your fact tables based on this user mapping.
Recommendation
Go with Path 1 (SSO) if your data is highly sensitive (banking, HR) and you cannot risk the "replication gap" between Oracle and Power BI.
Go with Path 2 (Import) if you need the dashboard to load in under 2 seconds and the security logic is simple enough to maintain in two places.
Next Step: Would you like me to provide the Microsoft documentation link for configuring Kerberos SSO for Oracle, or would you prefer a DAX pattern for the Import Mode approach?
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
4 Replies
Hi u503294 ,
Great question. Integrating database-level security with Power BI is one of the most critical architectural decisions you will make.
While the other answers correctly identify the two main paths (DirectQuery vs. Import), the real challenge usually lies in the infrastructure setup, specifically the Gateway.
Here is the "Deep Dive" on how to actually make this work, depending on your priority.
Path 1: True Inheritance (DirectQuery + SSO)
If your strict requirement is "apply the same security that exists in Oracle," this is your only real option. You do not want to rebuild logic; you want Power BI to pass the user's ID to Oracle so Oracle can enforce its Virtual Private Database (VPD) rules.
The Hidden Hurdle: The Gateway Simply selecting "DirectQuery" is not enough. By default, the Gateway connects to Oracle using a single service account (e.g., PBI_Service_User). This means Oracle sees every request as that one user, effectively bypassing your RLS.
To fix this, you must configure Single Sign-On (SSO) on your On-Premises Data Gateway:
Kerberos Delegation: You configure the Gateway to "impersonate" the Power BI user (via Active Directory) when talking to Oracle.
Result: When User A views the report, Oracle receives the query as "User A". Oracle then applies the RLS policies, and returns only the rows User A is allowed to see.
Path 2: Performance First (Import Mode)
If DirectQuery is too slow (which is common with complex Oracle RLS logic), you must use Import Mode.
The Trade-off: You cannot "inherit" the security. You must replicate it.
The How-To:
Create a view in Oracle that maps [Username] to [Allowed_Region] or [Allowed_Dept].
Import that table into Power BI.
In Power BI Manage Roles, use the DAX function: [Email] = USERPRINCIPALNAME()
Set up a relationship to filter your fact tables based on this user mapping.
Recommendation
Go with Path 1 (SSO) if your data is highly sensitive (banking, HR) and you cannot risk the "replication gap" between Oracle and Power BI.
Go with Path 2 (Import) if you need the dashboard to load in under 2 seconds and the security logic is simple enough to maintain in two places.
Next Step: Would you like me to provide the Microsoft documentation link for configuring Kerberos SSO for Oracle, or would you prefer a DAX pattern for the Import Mode approach?
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.Thanks for quick guidance!. Please share microsoft documentation with SSO for Oracle.
Hi u503294
Best Practice (Recommended): Enforce Security in Oracle, Not Power BI
Power BI should consume already-secured data from Oracle. This ensures:
- A single source of truth for security
- No duplication of security logic
- Compliance with least-privilege and audit requirements
Step 1: Identify How Oracle Security Is Implemented
Common Oracle security patterns:
- Roles + GRANTs
- Row-level security via VPD / FGAC
- Context-based filtering (SYS_CONTEXT)
- Security views that already filter data
Confirm which one is in use (VPD is the most common for row-level).
Step 2: Use DirectQuery from Power BI to Oracle (Critical)
To honor Oracle security at query time, Power BI must use:
DirectQuery (not Import mode)
Why:
- Import mode bypasses Oracle security after data is loaded
- DirectQuery executes every query as the Oracle user
Step 3: Map Power BI Users to Oracle Users
You have three viable patterns:
Option A (Best): Per-User Oracle Authentication
- Power BI passes the end-user identity to Oracle
- Oracle enforces RLS via VPD / roles
Requirements
- Oracle supports external authentication (SSO / Kerberos / OAuth)
- Power BI uses Single Sign-On (SSO)
This gives true end-to-end security parity
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Power BI cannot automatically “inherit” Oracle’s RLS. You have to enforce it deliberately, either in Oracle or in Power BI, depending on your architecture.
Option 1: Enforce RLS in Oracle (DirectQuery)
Use Oracle as the security boundary by using DirectQuery from Power BI to Oracle. With this Power BI just passes the query through.
Result
Same data security as Oracle
One security definition (Oracle)
No duplication of logic
Option 2: Power BI RLS that mimics Oracle RLS (Import)
Create a User ↔ Data mapping table (from Oracle or elsewhere)
Apply Power BI RLS using:
[UserEmail] = USERPRINCIPALNAME()Filter fact tables via relationships
Pros
Works in Import mode
Faster visuals
Cons
Security logic duplicated
Must be maintained in two places
Risk of mismatch with Oracle
