Question Re: How do I implement table defined row level security? in Technology Q&A

How do I implement table defined row level security?

omacoder — Thu, 28 Jun 2018 23:46:38 GMT

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?

Re: How do I implement table defined row level security?

TammyPowlas — Fri, 29 Jun 2018 10:08:14 GMT

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

Re: How do I implement table defined row level security?

omacoder — Fri, 29 Jun 2018 14:14:36 GMT

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.

Re: How do I implement table defined row level security?

FCI — Fri, 29 Jun 2018 14:26:17 GMT

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

Re: How do I implement table defined row level security?

former_member435026 — Wed, 04 Jul 2018 08:37:05 GMT

Not actually sure this is possible directly in SAC. I`m guessing if you connect to a Universe using a users SAP BI credentials that the Universe level row level security would be respected. Not tried though!

Re: How do I implement table defined row level security?

FCI — Mon, 09 Jul 2018 14:56:49 GMT

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

Re: How do I implement table defined row level security?

omacoder — Mon, 09 Jul 2018 15:01:49 GMT

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.

Re: How do I implement table defined row level security?

FCI — Tue, 10 Jul 2018 07:11:29 GMT

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.

Re: How do I implement table defined row level security?

omacoder — Tue, 10 Jul 2018 15:15:44 GMT

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

5 models. All linked by the keys.

When user123 logs in, it finds all Jobs/Locations/Companies this user has access to see by looking up in tblRowLevelSecurityDim
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.

Re: How do I implement table defined row level security?

FCI — Tue, 10 Jul 2018 15:50:00 GMT

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) ?

Re: How do I implement table defined row level security?

omacoder — Tue, 10 Jul 2018 15:54:22 GMT

Discovery 2.1 SP1.

Re: How do I implement table defined row level security?

omacoder — Tue, 10 Jul 2018 15:59:18 GMT

If I attempt this with a live unx connection, when I set up a new connection in SAC, it asks me for the BI Platform username and password.

Is this the username that is passed to the database when a query is ran? If so, then every user who opens a SAC story will still see the same data based upon the username I put in the live connection?

Re: How do I implement table defined row level security?

former_member435026 — Thu, 19 Jul 2018 12:51:48 GMT

Hi,

I think with a live connection it does actually pass the user and password to the BI Platform. You`d still have to setup row level security in the Universe using your SAP BI users and groups for it to work though. Or possibly wondering if the SAP BI username is passed this could be used in a Universe filter. Thinking off the top of my head here, I`d have to try it!

Re: How do I implement table defined row level security?

omacoder — Thu, 19 Jul 2018 18:04:16 GMT

It does appear the Live Universe Connection does carry through all of the correct security that is defined in the user profile on the BI Platform.

However, using Live Connection to UNX has some huge limitations in SAC, so this wouldn't be an acceptable workaround.

SAC needs to be able to support table driven row level security, or automatically create the roles and assign them to the users based upon data in the model.