ProjectThree
New Member
- Joined
- Mar 21, 2019
- Messages
- 4
Afternoon All,
Before I begin, I know that you can't implement true RLS in excel - but due to pricing, Power BI is a non-starter. These excel models will also not be shared outside of my organisation.
The solution I'm using is obtaining the windows username via VBA, pasting it into a cell/table (on a hidden sheet) which is connected to the powerpivot model. My fact table then has a calculated column using 'filter' & 'contains' functions to check if the user is allowed access by comparing the fact row cost centre against a disconnected entitlement table, and returning either true or false. All measures are filtered to only include rows where the entitlement is calculated to be true.
The report uses pivot tables - via the PowerPivot window I've hidden all of the original value fields from client tools, forcing use of the measures. The workbook structure and VBA module are both protected. I'm also using vba to unlock the workbook, and refresh the power pivot model on workbook open.
I'm assuming the pivot cache would only include the measures not source fields, and the workbook structure is protected.I understand users could get lists of unsecured dimension data, but no facts.
My question is whether this a secure solution? Where are it's vulnerabilities? and is there a better solution?
The user needs to be able to interact and drill down on the pivot & an SSAS Tabular Model is not an option
Before I begin, I know that you can't implement true RLS in excel - but due to pricing, Power BI is a non-starter. These excel models will also not be shared outside of my organisation.
The solution I'm using is obtaining the windows username via VBA, pasting it into a cell/table (on a hidden sheet) which is connected to the powerpivot model. My fact table then has a calculated column using 'filter' & 'contains' functions to check if the user is allowed access by comparing the fact row cost centre against a disconnected entitlement table, and returning either true or false. All measures are filtered to only include rows where the entitlement is calculated to be true.
The report uses pivot tables - via the PowerPivot window I've hidden all of the original value fields from client tools, forcing use of the measures. The workbook structure and VBA module are both protected. I'm also using vba to unlock the workbook, and refresh the power pivot model on workbook open.
I'm assuming the pivot cache would only include the measures not source fields, and the workbook structure is protected.I understand users could get lists of unsecured dimension data, but no facts.
My question is whether this a secure solution? Where are it's vulnerabilities? and is there a better solution?
The user needs to be able to interact and drill down on the pivot & an SSAS Tabular Model is not an option
