Forum Discussion
Helper IVSELECT permissions or external policy action error. OneLake Security issue
Hi mkjit256,
Despite the Fabric documentation clearly supporting this scenario in principle, configuring access in SQL Endpoint via OneLake Security is still maturing. For your use case, it would be best to limit user access in:
- Lakehouse: using OneLake Security
- SQL Endpoint: Grant/Deny T-SQL statements (switch back to Delegated Identity mode)
This approach works like a charm!
Super User
Hi mkjit256,
Despite the Fabric documentation clearly supporting this scenario in principle, configuring access in SQL Endpoint via OneLake Security is still maturing. For your use case, it would be best to limit user access in:
- Lakehouse: using OneLake Security
- SQL Endpoint: Grant/Deny T-SQL statements (switch back to Delegated Identity mode)
This approach works like a charm!
mkjit256Thanks for your reply. I will give it a try and update
- mkjit256
Thank you , i did exactly what you had suggested and it worked like a Charm. Summarizing the implemented solution for others in case needed.
In the SQL Endpoint mode of the lakehouse, set the OneLake access mode to Delegated.
At the level of the sql endpoint the only permission granted was Read. (ReadData and ReadAll will expose all the tables).
If you want to for example to grant access of sales table to a role called salesreader:
Start by creating the role
create role salesreader; goTo grant access to the sales table :
GRANT SELECT ON OBJECT::dbo.sales TO [salesreader]To add a member to the role you use:
ALTER ROLE [salesreader] add member [[email protected]]and that should do it