Database security

Hervé BEURET

Hello,

 

How can I grant access to the full entity "section code" (in order to see all actual data) but give limited access to the payroll data (only few section code) ?

The dynamic selection doesn't work in the security setting...

Many thanks

 

Hervé

Etienne CAUSSE

Hi Hervé BEURET

Short answer : you can't !

I had this situation several times. In some situations, my solution was to replicate the entity and have it twice in the cube.

For example : if you have a Fiannce cube by Month, Section Code, you can create a Payroll cube by Month, Section Code, Section Code Payroll.

 

You map the same data to Section Code and Section code Payroll, but then you restrict authorizations only on Section code Payroll, while keeping all authorizations on Section Code.

 

This can be transparent for the user.

Etienne CAUSSE

Of course it's only feasible if your cubes are not too big already.

Hervé BEURET

Thanks Etienne, it could be a solution...but as you said difficult with cube already big !

Unknown

Hello Hervé,

 

I am not sure I have understood the problem, but what about creating a matrix User - Section Code? In the matrix you put 1 if the user has to be able to see data for that Section Code, and 0 if he is not allowed.

You can then put in the layout the original cube Payroll (block a), the matrix (block b), and an algorithm (IF(b=1,a,0)) and you display only the algorithm.

Of course, you would need the entity User to be selected, but this can be done through the @user function within the database security E-learning Crumb: Security and @user (CR61).

 

I hope it helps

Davide

Etienne CAUSSE

Hi,

Just to mention that Davide Genini solution only works if your user is not able to modify the layout. If he is, he can remove the cube and be free of the restriction.

It is the case especially with web client and with office addin client.

Unknown

Hello Etienne,

 

you are right, good observation. If Hervé has to let users modify the layout, my solution would not be applicable.

 

Davide

Paul Wyatt

Etienne CAUSSE, Would a multi db solution work to get over the layout modification rights issue, where the settings are in one locked down database and utilised in the target capsule?

Etienne CAUSSE

Hi Paul Wyatt - Avison Young UK, I'm not sure I understand your solution ? How would a multi db solution would be implemented in that case ? Since a layout is always mono-DB, I don't see the trick...

Paul Wyatt

Hi Etienne CAUSSE Firstly, like Davide Genini, I do not think I understand fully Hervé BEURET requirements. The reference to a multiple database model was to respond to a percieved requirement to prevent the user from circumventing security and seeing what they should not. Secondly, I did not read the complete exchange between Hervé BEURET, Davide and yourself as I was responding on the train using my phone. - Sorry for adding to any confusion.

 

My first point hasn't changed.  My response is based on an assumption that Hervé BEURET needs to restrict access to entity members for specific users. If this was the intention, then a single db and capsule can be used but the control must also be incorporated throughout the database architecture.

 

I develop models for external clients that share a database. The security is handled in the way you have already described but also with a matrix mapping cube. Db security is set as follows where a client user group are restricted to seeing data on for client number 1:

 

Sample entity: PROPERTY > PORTFOLIO

Code + Desc

1 - Client 1a

2 - Client 1b

3 - Client 2a

4 - Client 2b

5 - Client 2c

 

@user
SELECT CLIENT = 1
SELECT [SECURITY_CLIENT_PROPERTY]=1
SELECT [SECURITY_CLIENT_LEASE]=1
SELECT [SECURITY_CLIENT_EVENT]=1

 

NOTE:

1. Entities CLIENT and each of the mapped entities are all duplicates and share the same mapping using the mapping cube.

2. Each tree datareader (Property, Lease and Event) contains the relevant SECURITY entity.

3. Each Cube datareader contains only the entity CLIENT - which has been mapped to the SECURITY entities and a single matrix cube.

 

With the above configuration, I am able to control what members of shared/common entities each, disparate client, can see. I also secure the cube values that they have access to.

 

users with this config would see only:

 - PORTFOLIO entity members for the first 2 members (1 & 2)

- Cubes would only show values / entries mapped to the PROPERTY / LEASE / EVENT for client 1

- GVA Analysts will see all entries and can simply select CLIENT 1 to see exactly what the external client sees

 

The use of the mapping cube means that GVA analysts can have unrestricted access to the database and select the CLIENT or CLIENTS they wish to view.  Using a procedure to select the SECURITY ENTITYs based on the Mapping cube, internal analysts can work in controlled and secure stable data structures relating only to what they need.

 

Now, it is probable that I have completely missed the point and if this is the case I'm really sorry. However, in the event that I assumed correctly, then I have at least suggested that it is possible to control access to entity members without using the local dataview/object configurations.

It is my intention to demonstrate this structure at some point, once I have the time to create the test model, video and screen shots.