Hi,

I have an AS2000 cube that pulls back "sensitive" information via its
drillthrough.

I have 2 set of users: Group 1 and Group 2.

I need Group 1 to be able to see everything when using the drillthrough, and
I need group 2 to see everthing but 1 column. This 1 column holds the
sensitive data. It is viable that the data is just masked, rather than being
compltely removed.

For performance issues, it's important that I stick with 1 cube - I DONT
WANT 2 CUBES...

Can anyone please tell me how I can achieve this please?

Thanks in advance.

Jon Derbyshire

If you control Group 1 and/or Group 2 via windows groups I can think of
a possible solution. Ideally at least one of these would map to a single
windows group which in turn is mapped onto an OLAP role.

Create a view over the fact table where you create an extra column that
has a CASE statement that masks the sensitive column based on the the
IS_MEMBER() SQL function, which detects if the user is in group 1 or 2.

Then alter the cube to use this new view as the fact table and change
your drill through options to the new masking column from this view
rather than the column from the fact table itself.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <BD08E77E-9A52-4942-BC72-1BD984F92F1A (AT) microsoft (DOT) com>,
JonDerbyshire (AT) discussions (DOT) microsoft.com says...

Darren,

That's Excellent - It works perfectly.

Thanks so much.

Jon D

"Darren Gosbell" wrote:

Hi Darren and Jon,


Maybe I'm missing something, but doesn't the OLAP server drillthrough
SQL query execute in the fixed security context of the
MSSQLServerOLAPService account, rather than in the context of the
individual user - in which case, how would the data source (presumably
SQL Server?) detect the end-user?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

It turns out that you're correct...

I had not properly tested it - stupid me...

Can you possibly suggest a way to do it?

FYI - The drillthrough is being actioned via the MS Analysis Services Excel
Add-in...

Jon Derbyshire

"Deepak Puri" wrote:

Hi Jon,

So far I couldn't come up with a solution in AS 2000. Is AS 2005 an
option for you, in which case drillthrough architecture is different?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

doh... and is seemed like such a reasonable solution

Deepak is right, I can't think of any secure ways of achieving this in
AS2k given the context of the drillthrough connection. I played with a
few options this morning, trying to get some sort of information about
the user context passed through to the drillthrough query, but without
success.

You could modify or write your own drillthrough add-in that implements
the masking, but that is inherently insecure as anyone who queried the
cube directly would bypass the security.

You could use actions and a reporting services report to do the
drillthrough and mask the column. This is a lot more work, but it could
be set up to implement the masking in a lot more secure manner. But if
you have any dimensional security, a user could bypass it by altering
the parameters sent to the report.

So unless you are not using dimensional security Deepak's suggestion of
looking into AS2k5 might be the only option to get this type of
drillthrough working.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <EC6CE1B3-15F3-4A90-AA05-6B71A2414C3A (AT) microsoft (DOT) com>,
JonDerbyshire (AT) discussions (DOT) microsoft.com says...