In
SSAS your are very flexible to build a complex security implementation. You can
also use dynamic security and AS Stored procedures to enhance the security
concept.
Some
time ago my employer raised the requirement for splitting the access to our
cubes in users which are allowed to analyse the cube and users which are only
allowed to run Standard reports against the cube. And of course a user could be
a Reporter for Cube 1 but an Analyser for Cube 2.
My first answer was: If a user is allowed on the cube, he is allowed.
The answer of my employer was: not acceptable. –puuh
So I went for holidays and thought about it while I was lying on the beach
In
our infrastructure we use a Webportal for our Standard reports and Excel for analysing
(in detail: XLCubed).
Users
can only access over the Datapump to our cubes. So there is no direct access
possible.
My
first approach was to build up a service, like profiler, checking all
connections and if it was established by a user which is not allowed – cancel it.
Unfortunately the response of the query was faster then my evaluation of the
connection.
My
second approach was to build up a custom module and listen on the traffic and modifying
the response stream based on the rights. Unfortunately the stream is encrypted.
Details can be found here http://sqlblog.com/blogs/mosha/archive/2005/12/02/analysis-services-2005-protocol-xmla-over-tcp-ip.aspx
So
coming to my last approach: SSAS Proxy. The basic code can be found here https://code.msdn.microsoft.com/office/SSASProxy-Custom-HTTP-7db6d016/
I
created 2 roles on the cube (Analyser and Reporter). Both roles uses dynamic
security with factless facttables. In the Analyser role every user with
appropriate rights is added over an own Active Directory Group. This role uses
the MDX Function Username() for the security.
In the
second role only one system user is added. The security implementation is the
same except the MDX Function. Here I use CustomData().
Until
now only the Analysing users can access the cube.
Next
step is implementing the custom handler on the IIS. Just saying the all calls
to eg. .cube extensions should be handled by my customized SSAS Proxy.
In my
proxy I just impersonate the actual user to the system user which has access to
the cube over the Reporting role.
Finally:
the Webportal has the ability of some connectionmappings. So the Analyser
designs a report, mapping to the basic datapump and upload this report. In the
Webportal this report is automatically mapped to my custom handler. If a user
downloads the excel he get the connection to the basic datapump again and if
this user is just a reporter he won’t get access to the cube.
