How do I implement table defined row level security?

[omacoder]

I understand in SAC you can create roles, and then assign those roles and map them to certain columns.

However, each user will have different access to different rows in the model.

I'm not seeing a way around this without creating a role for each user?

[TammyPowlas]

Hi Brian - I would assume this would work better implementing on the back end; what is your data source?

[omacoder]

You're right, handling it at the DBMS would definitely be preferred, but I'm trying to do a POC in SAC to present to our enterprise and the only option for a data source is Excel via google drive. So that is the data source that I am working with.

[FCI]

And in planning scenarios, you have to handle this kind of security at the SAC level.

[FCI]

Hi Brian,

By enabling the data access control on a dimension you can define who has the right to read or write on a given value of a dimension.

Isn't it what you want to achieve ?

Regards,

Frederic

[omacoder]

Based upon this implementation, I would have to set up a separate role for each individual user. This is not feasible as the data the user has permission to is controlled within the database itself and is constantly changing with changes to users (eg new users, promotions, demotions, etc). Each time one of these actions occurs, I would need to track and trace that action and then also update that individual user's role.

[FCI]

But you can't handle row security level at a role level. Can you ?

AFAIK, it is handled at the dimension level. And yes this has to be maintained manually, I was hoping you could fill these security fields through a dataSource but these fields seem to not be available for mapping.

[omacoder]

Here's the models that I would like to implement in SAC.

5 models. All linked by the keys.

1. When user123 logs in, it finds all Jobs/Locations/Companies this user has access to see by looking up in tblRowLevelSecurityDim
2. Based upon the inner join from this table to tblSalesFact, they will only see the sales amount that is applicable to their jobs/locations/companies

In order to do this with role based, from my understanding, I would have to create a role for every JobKey and assign each user to be able to view that job key. I would then have to maintain that list of roles and watch for any changes/adds to that JobKey and get the roles updated ASAP. Essentially I'd have to make a role for each user.

Currently, in the BI Platform, this is handled via inner joins at the row level at the DBMS by adding a dynamic where clause on tblRowLevelSecurityDim that says where AuthenticatedUserNameAtSQLServer = @UserName

We attempted to do this in Lumira in BI Platform, but the performance and defects we keep running us into has SAP Support continuing to push us to use SAC. Because we could have 200 area managers/regional managers/CEO/CIO etc logging into this dashboard, it is MOST IMPORTANT that the users authenticating only see the sales numbers applicable to their region.

[FCI]

I understand perfectly well the business need. But as the SAC roles don't handle row level security, I didn't see how this is going to multiply your roles. Row level security is handled at the dimension level which should be maintained manually (again AFAIK).

I'm "amused" by you remark on Lumira (and the pressure to move to the SAC), which version are you using (designer or discovery) ?

[omacoder]

Discovery 2.1 SP1.