Forum Discussion
Manage OneLake security Preview
I recently got access to try out OnleLake Security for Lakehouse.
I set up a role and implemented RLS choosing the table where I want RLS implmented, using a select statement & adding the user.
SELECT * FROM MyTable WHERE WorkEmail = USERPRINCIPALNAME()
I created a test report in Power BI using the table with RLS. I published it, gave the test user Read permissions on the data model and shared the report with them. Then they get a query user error on the visual that uses data from the table.
"Error retrieving data for this visual.
Details:
Expression.Error: The key didn't match any rows in the table.
Microsoft.Data.Mashup.ErrorCode = 10061
Key = [Schema = "dbo", Item = "MyTable"]
Table = #table({"Name", "Data", "Schema", "Item", "Kind"}, {})
The key didn't match any rows in the table.
The exception was triggered by the IDbCommand interface."
I've seen mentioned in a post from April this year, that USERPRINCIPALNAME isn't available, but I was able to add it to my statement without throwing an error. Is it avavilable now? I then gave read access on the lakehouse to the user, but the same result
Also in the Lakehouse settings, I activated "Use OneLake security for tables (User's identity access mode)"
Am I missing any steps? I'm quite sure I followed all the instructions. in the documenation
Thanks
Joe
Hi Joe_Barry ,
Thank you for reaching out to the Microsoft Community Forum.
Please check the below things
1. Temporarily replace USERPRINCIPALNAME() with a email to test if RLS works.
SELECT * FROM MyTable WHERE WorkEmail = '[email protected]'
2. Check that MyTable is under the dbo schema or update your query to match the actual schema. Check the table is properly registered in the Lakehouse and visible in Power BI.
3. Check that the test user has Read access to the Lakehouse. Access to the table via OneLake security. Matching data in the WorkEmail column.
4. Try querying the table directly using the SQL analytics endpoint with the test user’s credentials to see if the RLS is applied correctly.Please refer below links.
Solved: Re: RLS not wokring In OneLake security - Microsoft Fabric Community
Solved: Row Level Security in OneLake (preview). Major lim... - Microsoft Fabric Community
Row-level security - Microsoft Fabric | Microsoft Learn
OneLake Security for SQL analytics endpoints (Preview) - Microsoft Fabric | Microsoft Learn
Get started with OneLake security (preview) - Microsoft Fabric | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
3 Replies
- v-dineshyaCommunity Support
Hi Joe_Barry ,
Thank you for reaching out to the Microsoft Community Forum.
Please check the below things
1. Temporarily replace USERPRINCIPALNAME() with a email to test if RLS works.
SELECT * FROM MyTable WHERE WorkEmail = '[email protected]'
2. Check that MyTable is under the dbo schema or update your query to match the actual schema. Check the table is properly registered in the Lakehouse and visible in Power BI.
3. Check that the test user has Read access to the Lakehouse. Access to the table via OneLake security. Matching data in the WorkEmail column.
4. Try querying the table directly using the SQL analytics endpoint with the test user’s credentials to see if the RLS is applied correctly.Please refer below links.
Solved: Re: RLS not wokring In OneLake security - Microsoft Fabric Community
Solved: Row Level Security in OneLake (preview). Major lim... - Microsoft Fabric Community
Row-level security - Microsoft Fabric | Microsoft Learn
OneLake Security for SQL analytics endpoints (Preview) - Microsoft Fabric | Microsoft Learn
Get started with OneLake security (preview) - Microsoft Fabric | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
- Joe_BarrySolution Sage
Thanks for your reply v-dineshya
I have got it working when I hardcode the email address
SELECT * FROM MyTable WHERE WorkEmail = '[email protected]'USERPRINCIPALNAME() doesn't seem to work in the statement even though it doesn't throw an error when saving
- v-dineshyaCommunity Support
Hi Joe_Barry ,
Thank you for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. Please do let us know if you have any further queries.
Regards,
Dinesh