March 18, 2015 at 2:12 pm
I have created row level security on two views and adding these two views to particular role.
Today I have got an requirement that, middle level managers shouldn't see the all the columns. So I have created another role for Middle level managers and assign securables as those two views with selected columns by grant, and map all the middle level managers to this role. I thought my job is done. But these managers uses this view on SSAS(tabular model) and Excel, In those applications they are not able to load the data.
Later I come to know we can't use -- select * from ViewA ( in viewA I have restristced few columns in the role level)
Work around is creating another view and assigning to the role. But how can we achieve column level security to implement this in either SSAS/SSRS/EXCEL?
Many Thanks
March 18, 2015 at 2:16 pm
I believe you would need to handle that on the reporting platform side not the database side(other than creating new views with the appropriate columns), for example in excel unless you specify a query it will by default pull all the columns from a table/view which breaks your security and is working exactly as intended that they get an error when trying to run that report.
March 18, 2015 at 2:27 pm
If you want "column level security" for data coming out of SSAS, then you have to apply the security in SSAS.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 18, 2015 at 2:32 pm
You say you can't use SELECT * FROM viewA. Have you tried listing out the columns? Let's say the view has 5 columns and column 3 is restricted.
SELECT col1, col2, col4, col5
FROM viewA
Would that work? I'm thinking the issue is the select is attempting to access columns you have restricted the user from seeing.
-SQLBill
March 18, 2015 at 2:36 pm
If I create new view with only selected columns and assign to role, then it will work either in excel or ssas or ssrs. But I am wondering without creating one more view, can't we achieve this in database level??. If i restrict the users in database level, then when they login to ssas tabular, they can't see any other table or views apart of the views which are assigned to role.
March 18, 2015 at 2:42 pm
Hi SQLBill,
I have checked with select col1,col2,col3, col4 from table, it works fine, but i have more than 130 columns and moreover when the MANAGERS access with excel or ssas, they can't filter out what columns they need to mention and not what columns they no need to mention it.
I have implemented Row level security and now trying to achieve column level security, but it is not going smooth.
Example: when Manager A logins, By his login I am restricting row level data on view.
But now i got requirement for another hierarchy managers I need to use same row level security, along with that i need to deny or eliminate some columns on the same view.
March 18, 2015 at 2:50 pm
Sangeeth878787 (3/18/2015)
If I create new view with only selected columns and assign to role, then it will work either in excel or ssas or ssrs. But I am wondering without creating one more view, can't we achieve this in database level??. If i restrict the users in database level, then when they login to ssas tabular, they can't see any other table or views apart of the views which are assigned to role.
No it won't your users are trying to pull data they don't have access to, it's working as intended that they can't.
Technically they shouldn't even have access to those reports since they can't run them as they contain data you've decided they shouldn't see.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply