Cell security

MS SQL SERVER

Next

• 1. Calculated Cell or Member
I have a seemingly simple issue that I cannot get past. In my fact table I have a field containing values '1' '2' '3' 'A' 'B' 'C' I need to calculate a sum on this data and convert the string values of '1', '2', and '3' to integers and convert the 'A', 'B', and 'C' to a zero. I am ultimately looking for an Average. I want to figure out how to do this with a calculated measure or a calculated cell. Do I have to create another physical field and convert the A, B, and C to zeros in SQL? thanks, tim
• 2. Building large fact tables
I'm rather new to olap and am curious what the most efficient method is for building large Fact tables. I have a single Fact table with 8 columns, 6 of which are surrogate keys to my 6 Dimension tables. My trusty datawarehouse toolkit book says, "Making one pass over the fact table for each dimension, we quickly substitute the surrogate key for each operational key". My question is, with a Fact table of 15 million rows, what's the most efficient way to make these substitutions? 15M x 6 updates to a database table seems far too resource intensive. I'm considering just pushing the 15M out to a flat file, then building a new flat file with my surrogate keys which I bcp in. Is there a common strategy for this? Thanks, Mike
• 3. List the products whos unit sales increasing for the last 4 months
hai all i want a query for the above subject which analyse the unit sales of products which are increasing for the last four (or) months can anyone help me?
• 4. =?Utf-8?Q?How_to_design_a_cube_when_the_me?= =?Utf-8?Q?asure=E2=80=99s_calculation_depend_one_d?= =?Utf-8?Q?im?=
Hi! Case: The DataBase Olap analyzes the customer portfolio of an organization. Sample transaction: Mr. Bean (Customers), on 03/10/2005 (Time), buy (Type), 3.0000 \$ (Import), of EuroIbex(Assets Financial), in the Spanish Office (Geography) Cubes: Valuations Dimensions - Assets - Time (*) Measures - Value Fact Table: Value ID_Asset Time 13,37 54 03/10/2005 13,31 54 03/11/2005 .. .. .. 13,45 54 06/24/2005 (*) Cells Calculation for levels higher than Time.[Day], to take the last descendants (not null). Portfolio Dimensions - Assets - Geography - Type - Customers - Time Measures - Import (Total amount of the investment) - Number_of_Units Member Calculated - Value=LookupCube(Cotizaciones, (Measures.[Value], Asset.currentmember, Time.currentmember) - Saldo= Number_of_Units * Value Fact Table: Import Number_of_Units ID_Customer ID_Asset ID_Geografy ID_Type Date 3.000,00 =(Import/Value) 234654 54 2 11 10/03/2005 Query: - How to calculate the portfolio when the query no reference to the Assets dimension? By default take Asset.[All], ( this not is correct). - How to calculate the weighted average when the Assets dimension has more of one level? The average depends on the Number of Unit. Sorry about my language, and thanks very much.
• 5. Multiple levels in the measure dimension
Hi! Sorry for the rookie question but is there a way to actually use multiple levels in the measure dimension? I want to create an hierarchy like a profit and loss statement. One example: Profit Total Revenue Revenue by sales Revenue by ads ... Total Costs Costs of labor Costs of material etc etc Thanks, Erik

Cell security

```In my cell secuirty, i have defined the rules as

iif(measures.currentmember = measures.M1,
iif(rule1,0,1),1)
and
iif(measures.currentmember = measures.M1,
iif(rule2,0,1),1)
and
iif(measures.currentmember = measures.M1,
iif(rule3,0,1),1)

In this case, if the cell which holds the current memeber
fails the very first rule, will the other rules be
evaluated? With the AND operator, will teh cell pass
through teh entire set of roles or will teh control come
out as soon as it encounters a failure case.

Thanks
GS

```

CELL SECURITY

```
SECURITY USING CELL-SECURITY:

From what i've read cell security s enforced on the client. If someone
is able to gain access to a machine running the client (for example an
application server or a web server) he is able to get cell values
independently of the fact that those values will be defined as #N/A in
the secured cell value property. The real value is travelling between
theAnalysis Server and the application server. Is this true ? How can we
effectively garantee true security ?

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!
```

Re: CELL SECURITY

```Analysis Services offers two modes of enforcing security: on the client and
on the server. If you are concerned that the secured data can be intercepted
in the communication - you should use server enforcement - in this case
secured data will never leave server.
You can use dimension security with server enforcement.

--
==================================================
Mosha Pasumansky  -  http://www.**--****.com/
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

```

Re: CELL SECURITY

```Does this mean there are no ways of implementing Cell
Security with server enforcement in SQL Server 2000.

on the client and
can be intercepted
in this case
warranties, and
message

client. If someone
(for example an
cell values
defined as #N/A in
travelling between
true ? How can we
***
```

Re: CELL SECURITY

```THAT'S EXACTLY THE PROBLEM !

Our only way to implement security in AS is using Cell-Security beacause
the roles to implement are mainly : IF Users chooses Member A from Dim A
and Member B from Dim B, than he is not allowed to see the values. Rules
are realy very complex !

I Need some additional clarification, expecially from Moshua!

1 ) Is there a way to use cell-security (with enforcement on
client-side) but changing the Secured Cell Value Property to another
value that tell Analysis Server NOT TO SEND BACK the real Confidencial
value ? Which problems are in changing these value with the Isolation
Mode defined also in the connection ?

2 ) I've started to try applying the MDX used in cell-security within a
calculated cell, which gives me the value of 0 (not allowed) and 1
(allowed). This is good as i can use these calculated cell to find out
if the current cell value is allowed for a public profile. A second
measure will always have the real value. So i will have two calculated
members : one which has the public value or the string "Sec" and another
one which has always the real value. A user connecting with a public
profile will see the first value, a user connecting with a private
profile will see the second value. The problem is that i cannot use
dimension security on calculated members to filter which calculated
emmber each role can see. And i realy need to use calculated members
because i need to use server-side coloring ! I think i am almost getting
there as i already have tweo calculated members for each of the roles :
a fiorst that has public values and "Sec" and a second which has all the
values with a red color on those defined as public ! IS THERE ANY WAY OF
KNOWING USING MDX WHICH ROLE IS THE CURRENT LOGGED ON USER IN ? If so i
will use expliity these value to have another calculated member saying :
If CURRENT USER IS IN PUBLIC ROLE, Then value is the first value, else
value is the second value

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!
```

Re: CELL SECURITY

```> 1 ) Is there a way to use cell-security (with enforcement on

With cell security it is not possible to do server enforcement in AS2K. It
will possible in Yukon.

Trying to substitute security with calculated cells or calculated members
simply won't work. You still won't be able to make them enforced on server,
but instead you will open additional holes.
Here are my suggestions:

1. Use HTTPS as the only protocol to connect to Analysis Services - i.e.
close ports 2725 and 2393/4 in the Analysis Server machine. HTTPS uses SSL
for encrypting the data - so nobody will be able to intercept
2. Adopt server-only solution, for example XMLA SDK - where all of the
security checks happen on the server machine. There are commercial products
which allow such middle tier solutions - Proclarity, NovaView - to name a
couple.

--
==================================================
Mosha Pasumansky  -  http://www.**--****.com/
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

```

Re: CELL SECURITY

```
1 ) Don't understand why using calculated cells will not work and also
why does it open security risks. CAN YOU CLARIFY ?
Suppose that i have 2 roles: (1) public and (2) private. Second role
will see all values, first role will se values returned by MDX
statement.
If i use two measures (taken from the same value) then i will define one
calculated cell (calculation subcube will be the measure, calculation
value will be the MDX Statement taken from cell security) named RILE
PUBLIC. This will produce 0 (not allowed) or 1 (allowed). Then i will
use another calculated cell (NAMED PUBLIC VALUE) which is something like
: If Allowed the Value, otherwise "Sec". Another calculated cell
(PRIVATE VALUE) will keep always the real value.
One user connecting with Public role wil see the PUBLIC VALUE (value or
the string "Sec"), another user connecting with the second role will see
always the real value (PRIVATE VALUE) and these is always done on the
server side. WHY DOES THIS NOT WORK AND WHY DOES THIS OPEN ADDITIONAL
HOLES ? (WE WILL ALWAYS USE IPSEC OR HTTPS BETWEEN OUR APPLICATION
SERVER AND ANALYSIS SERVER).

2 ) We will intend to use in fact HTTPS between Proclarity and Analysis
Server. Our problem is not that someone will be able to intercept the
values from the connection but that someone will hijack the current
session id of a public user that is currently authenticated and manages
to get the values taken from AS , as cell-security will be enfornce on
the client (in these case Proclarity application server). We think that
even using Proclarity as a middle tier solution and using HTTPS between
Proclatiry and Analysis Services there is a security risk that someone
will hijack a public session and manages to access Analysis server cubes
using cell-security, anf getting values not allowed for that profile. IS
THIS TRUE ?

WE REALLY NEED SOME HELP IN TRYING TO CLARITY WHICH SECURITY RISKS ARE
IN PLACE !

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!
```

Re: CELL SECURITY

```> 1 ) Don't understand why using calculated cells will not work and also

I did not say that calculated cells won't work for you, but they won't let
you achive something that cell security didn't

I am not clear whether you mean Proclarity's session ids or AS session ids.
If you mean Proclarity session ids, then it is a problem regardless of where
security is enforced. And I don't see how would you be able to hijack AS
session ids, because they will be encrypted in HTTPS.

--
==================================================
Mosha Pasumansky  -  http://www.**--****.com/
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

```

Re: CELL SECURITY

```1 ) I did not say that calculated cells won't work for you, but they
won't let you achive something that cell security didn't

A) Cell security is enforced on the client. Using calculated cells,
enforcement is done on the server. Is this correct ? If it's true then i
can achieve what cell security does not.

2 ) I am not clear whether you mean Proclarity's session ids or AS
session ids. If you mean Proclarity session ids, then it is a problem
regardless of where security is enforced. And I don't see how would you
be able to hijack AS
session ids, because they will be encrypted in HTTPS.

B ) Yes, you're right it's also to confused for me. I understand that
using HTTPS between Proclarity and AS will prevent access to private
data on that specific channel. If someone has registered as public to
our site, after authentication, and access Proclarity using Single Sign
On, Proclarity running as an Application server will return only public
values after enforcing in this middle tier the cell security roles. As
the session is authenticated already in Proclarity is the user loggeed
in he able to hijack the information retured from AS to PAS in any way ?
TThat's something i'm not quite sure and i have some dificulties in
telling something difeent to my security team. In fact what they say is
: Even if HTTPS is used in al layers someone that manages to control the
middle tier will be able to access private data as this really crosses
the AS boundaries. So my question is : Using HTTPS between the browser
and the Web server and using HTTPS between the Application Server and AS
and using cell-security, which security risks are really involved of
having a public user (with an AS Public Role) of having access to
private data ?

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!
```

Re: CELL SECURITY

gt; A) Cell security is enforced on the client. Using calculated cells,

This is not correct. Calculated cells cannot be enforced to be always on
server.

If somebody breaks into middle tier - they will be able to get to the data
of all users who are connected to that middle tier - this is generally true
even when middle tier is not PAS and back end is not AS. And again, it is
not important whether security is enforced on the client or on the server.
And if middle tier is not broken - I don't see additional security risks.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================
"joao rodrigues" < XXXX@XXXXX.COM > wrote in message
news:%23W\$iG% XXXX@XXXXX.COM ...

Re: CELL SECURITY

```Thank you very much for your support Mosha.

1 ) This is not correct. Calculated cells cannot be enforced to be
always on server.

I was really convinced that Calculated Cells and Calculated Members were
ALWAYS calculated on the server side (AS) and always returned as such to
the middle tier, but i guess that from your words i was not correct.

2 ) If somebody breaks into middle tier - they will be able to get to
the data of all users who are connected to that middle tier - this is
generally true even when middle tier is not PAS and back end is not AS.

I some cases this migth be true. For example, using SQLSERVER and
defining two windows accounts (1 with Read Permission and another with
write Permission), if someone breaks into the midddle tier, and as you
say, he will be able to get to the data of all users who are connected
to that middle tier. However if connected users are all public then i
think it's possible to say that only Read Permission will be allowed to
SQLSERVER (as no Write Permission is being used in the middle tier) on
that moment. I agree that if a private user is logged in and someone
breaks the middle tier on that moment of course that in this case
private data on SQLSERVER is also compromised. But that means that the
risk of security is minor than the one observed in AS, as in the later
case no security is applied on the server side. In the former case
(SQLSERVER) this only happens if a private user is currenty logged in,
which i think it decreases the security risk.

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!
```

Re: CELL SECURITY

```1 ) This is not correct. Calculated cells cannot be enforced to be
always on server.

Regarding again calculated cells is there any way of specifying that we
want them calculated server side : using for example, the isolation
location parameter in the PTS ?

*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!
```

Re: CELL SECURITY

```Hi Joao,
completely different idea:

How about splitting the cubes into two physical cubes:
1. public one with just the publically available measures.
2. private one containing the other measures.

A virtual cube is combining the both cubes.
The public users will just see the public cube, the private users the
virtual cube.

Frank

```

Re: CELL SECURITY

```> Regarding again calculated cells is there any way of specifying that we

By using Execution Location=3;Default Isolation Mode=1 you can ask for the
calculations to be performed on the server, but

1. Those flags should be considered as hints (although very strong hints),
i.e. there exist conditions when calculations on the server won't be done
regardless of the flag settings
2. Since you are concerned with somebody breaking into middle tier - they
can change those flags back.

--
==================================================
Mosha Pasumansky  -  http://www.**--****.com/
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

```

Similar Threads:

```Hi All,

I need to implement cell level security on a model so that the windows
username is checked against a username property asigned to the relevent
member of a dimension before the data in the cell is displayed. I have
used the following code in the cell security advanced box of the cube
role:

UserName = [Dim1].CurrentMember.Properties("UserName")

and this allows the user to only see the relevent data. However the
user can't see any of the agregated ansestor nodes in the dimension
either. In the Test Role box on Cube Roles everything has a #N/A value
apart from the few cells that relate to the permitted user.

I need the users to be able to see the entire dataset at a high level
and only be restricted after drilling into the bottom level of the
dimension. How do I do this.

I can't use dimension security because we have a potential user base of
2500 most of whom are in various windows groups (which gives me enough
of a headache!) and I'm not adding hundreds of roles to each of the
approximately 60 models we have. Also, I'm not sure that our poor
server would apreciate having to store so many virtual dimensions in
memory all the time.

Please help.

```

```Thanks for having the time to read these message:

We want to deliver OLAP Cubes over the Internet. We will have about 12
cubes, each cube with two Analysis Server Roles : Public and Private.
Those roles will be defined uppon cell security. Public Role will have
Everyone, Private Role will have specific windows accounts that are
allowed to see all data.

From the internet , we we authenticate external users against some
store (database, LDAP, Active Direcory), etc

1 ) If using LDAP or Active Directory to authenticate our users do thy
need to be defined as really Windows Accounts ? Do those accounts need
to me mapped and syncronized through all the servers until the
Analysis Server ? We don't want to defined external users as Windows
accounts

2 ) In the case of not using Windows Accounts , our idea is to map
each external user to a list of applicational roles (1 user, many
roles), and the roles will be defined based on each of the cubes that
the user has access to. Then we need to know which cube is the user
accessing to map the connection to these well-know user (Windows
account). Can we follow othese approcah ? We intend to use an
application server running with IIS in the DMZ that connect to
Analysis Sever through a second firewall (port 2745). Is these
possible ?
```

```Using the following MDX I limit the access for a certain role to two members (A,B) of dimension Dim2 within one member (X) of dimension Dim1 :

Ancestor([Dim1].CurrentMember,[Dim1].[Level1]).Properties("key")="X"
and
(
Ancestor([Dim2].CurrentMember,[Dim2].[Level2]).Properties("key")="A"
or
Ancestor([Dim2].CurrentMember,[Dim2].[Level2]).Properties("key")="B"
)

Under this role the following MDX (Dim1 and Dim2 are in the foreground) works and returns the correct results :

SELECT { [Time].[Year].&[2004].&[7] } ON COLUMNS , { { { [Dim1].[Level1].[Member X] } * { [Dim2].[Level2].[Member A], [Dim2].[Level2].[Member B] } } } ON ROWS  FROM [Blah] WHERE ( [Measures].[Blah] )

The problem is that the following MDX (Dim2 is now in the background as a aggregate) does not work, it returns #N/A :

WITH MEMBER [Dim2].[ Aggregation] AS ' AGGREGATE( { [Dim2].[Level2].[Member A], [Dim2].[Level2].[Member B] } ) ', SOLVE_ORDER = 0   SELECT { [Time].[Year].&[2004].&[7] } ON COLUMNS , { [Dim1].[Level1].[Member X] } ON ROWS  FROM [KI_SALES] WHERE ( [Dim2].[ Aggregation], [Measures].[Blah] )

.. however access to all of the members required for the aggregate is allowed, so why is the aggregate not returned? Is this a limitation of Analysis Services (I am using SP3a)? If so is there a work around?

Many Thanks in advance,
Regards,
Dos.

```

```Dear All,

We have implemented calculated cell security in our cube. After that
we found that, some of the reports are not working which are developed
by using "Aggregate function" in MDX. If any one know what are the
disadvantages for calculated cell security.

Advance thanks to all..............

Cheers,

Chinni.........

```

Return to MS SQL SERVER

Who is online

Users browsing this forum: No registered users and 21 guest