Database Connector - Design Question

[PRana]

We have to integrate with an application where user's project association and their role in the project are stored available in a database view in following format.

A user can be associated with multiple projects.

Multiple users can be associated with a project.

User and their role for a given project can be either ADMIN or MEMBER but never both.

There are only handful possible values of PROJECT_ROLE

PROJECT	PROJECT_ROLE	USER
P1	ADMIN	U1
P2	MEMBER	U1
P2	MEMBER	U2
P3	ADMIN	U1
P3	MEMBER	U2
P3	ADMIN	U3
P4	ADMIN	U2

I am having hard time envisioning how this can translate into entitlements on an account.

For example, if I setup project as an entitlement type. The entitlement hierarchy will look like the following but then I miss out on PROJECT_ROLE information

   U1 -> PROJECT (Entitlement Type) -> P1, P2, P3

   U2-> PROJECT (Entitlement Type) -> P2, P3, P4

    U3 -> PROJECT (Entitlement Type) -> P3

If I setup project role as a value for entitlement type, then I need to duplicate project entitlement for each entitlement type.

     U1 -> ADMIN (Entitlement Type) -> P1 (Admin), P3 (Admin)

     U1 -> MEMBER (Entitlement Type) -> P2 (Member)

     U2 -> ADMIN (Entitlement Type) -> P4 (Admin)

     U2 -> MEMBER (Entitlement Type) -> P2 (Member), P3 (Member)

     U3 -> ADMIN (Entitlement Type) -> P3 (Admin)

     U3 -> MEMBER (Entitlement Type) -> 

If you have any better solutions, I'd love to get your thoughts.

Thanks in advance.

[rushikeshvartak · Accepted solution]

You need to create Entitlement values as Project - Role combination using concat function of DB 

P1 - ADMIN

P1 - MEMBER

Member vs admin is not allowed hence implement SoD between 2 entitlements values

[PremMahadikar · Accepted solution]

Hi @PRana ,

We had similar requirement in past with three different handful combination. We concatenated all three columns and designed single entitlement inside Saviynt.

In your case it would be, entitlement type (PROJECT) which would have below combination

• P1_ADMIN
• P2_MEMBER
• P3_ADMIN
• P3_MEMBER
• P4_ADMIN

During provisioning, you can use substring to separate the entitlements according.

I am not sure how it as been setup in the target or you are looking for different approach, the above one would be last possible solution which can be achieved. Please try..

If this helps, please select Accept As Solution and hit Kudos

[PRana]

Thank you @rushikeshvartak and @PremMahadikar for the confirmation. I was suspecting that's what was needed but wanted to confirm if there was any other option.