Forum Discussion
is column level security really available in sql server 2019
hi ricardo explained at https://community.fabric.microsoft.com/t5/Service/rls-and-pbi-the-engine-and-ssas/m-p/4689806#M275450 that ols (to me that means column level also) is available not only in pbi and ssas but the sql engine as well. but im not sure if he is saying it too can be detected by the user principal. his final comments didnt include a reference to the engine. his earlier ones did.
we are torn between putting our sometimes sensitive cost info into a table SEPARATE from our revenue facts. i just read an analysis by one of our peers that says the best option is to store the costs in a separate table because rls will hide all columns on a "protected" row not just sensitive cost columns. but we'vew always been taught to store same granularity facts in the same fact table. and i dont want to create a separate fact table for every category of "protection" that ever comes up.
can the community give me the real story? i dont see anything straightforward on the web about ols (specifically column level) in the engine. this link https://learn.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql?view=sql-server-ver17 makes me think its ultimately on a table with columns being a factor, but not an ols at the column level. i tend to believe ricardo but just want to be sure im understanding.
i just found this https://www.datasunrise.com/knowledge-center/column-level-security-in-sql-server/#:~:text=Column%20Level%20Security%20in%20SQL%20Server%20allows%20you%20to%20control,sensitive%20information%20within%20the%20database. it appears to be colun level using adifferent syntax. can pbi detect this thru user principal?
this was the recommendation from our peer. one in which costs are stored on a separate table.
Hi stanteitelbaum ,
SQL Server does not support true Column-Level Security (OLS) natively only Row-Level Security (RLS) is supported. OLS can be simulated using views and SYSTEM_USER(), but this approach is manual and not secure for sensitive data unless combined with other controls.
Power BI does not detect SQL column restrictions via USERPRINCIPALNAME() unless you are using Direct Query.
SQL enforces security using views and SYSTEM_USER() or SUSER_SNAME().
To ensure safer, scalable security that aligns with Power BI’s behaviour, split sensitive data into separate tables and apply RLS.
Thank you!!
9 Replies
Hi stanteitelbaum ,
You check data masking, it is possible in SQL database too.
https://learn.microsoft.com/en-us/fabric/data-warehouse/dynamic-data-masking
Thanks,
Sai Teja
thx sai, forgot to mention i saw masking and encryption but i dont think they would be possible solutions to our problem. costs are summed depending on the filters chosen and i suspect we'll decide not to show their aggregation or a measure eg margin) that uses them if even 1 rls or cls restriction was encountered in the rollup this executive asked for.
Hi stanteitelbaum ,
Thank you for reaching out to Microsoft Fabric Community.
Below documentation could help you in implementing OLS. Please review it and let us know if need any further assistance.
https://learn.microsoft.com/en-us/fabric/security/service-admin-object-level-security
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you!!
that seems to be related to a different subject. but thx.
Hi stanteitelbaum ,
May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you!!
not to my satisfaction v-sathmakuri. thx.
Hi stanteitelbaum ,
SQL Server does not support true Column-Level Security (OLS) natively only Row-Level Security (RLS) is supported. OLS can be simulated using views and SYSTEM_USER(), but this approach is manual and not secure for sensitive data unless combined with other controls.
Power BI does not detect SQL column restrictions via USERPRINCIPALNAME() unless you are using Direct Query.
SQL enforces security using views and SYSTEM_USER() or SUSER_SNAME().
To ensure safer, scalable security that aligns with Power BI’s behaviour, split sensitive data into separate tables and apply RLS.
Thank you!!
Hi stanteitelbaum ,
Could you please confirm if the provided response addressed your issue? If it did, kindly mark the helpful reply and accept it as the solution. This will assist other community members in resolving similar problems more quickly.
Thank you!!
Hi stanteitelbaum ,
I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail.
Thank you!!
