Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Config for Requestable Entitlement in ARS

Sunny
New Contributor III
New Contributor III

Hi,

We have a use case where we have SaviyntForSaviynt custom Sav role such as

CPO Role:
ROLE_CPO_ASIA
ROLE_CPO_AFRICA
ROLE_CPO_EUROPE

RPO Role:(Each CPO role should have minimum of 3RPO role)
ROLE_RPO_INDIA
ROLE_RPO_BHUTAN
ROLE_RPO_SRILANKA

ROLE_RPO_SOUTHAFRICA
ROLE_RPO_GHANA
ROLE_RPO_SOMALIA

ROLE_RPO_FRANCE
ROLE_RPO_RUSSIA
ROLE_RPO_SPAIN

If you have CPO role then you will be able to request all of the CPO and RPO role itself(Which is ROLE_CPO% and ROLE_RPO%)
However,If you have RPO Role then you will able to request the entitlement of RPO role only.(Which is ROLE_RPO%)

I have tried to query down the Config for Requestable Entitlement in ARS in endpoint section but I am not able to get the desired outcome.

The Query:

Note:
In the Entitlement of RPO I have hardcoded the value of 'RPO' in customproperty3 and 'CPO' in customproperty4
Where as in the CPO entitlement only I have hardcoded the value of'CPO'in customproperty

SELECT ev.entitlement_value
FROM entitlement_values ev
WHERE ev.customproperty4='CPO' AND ev.customproperty4 IN (
SELECT ea.customproperty4
FROM entitlement_values ea
WHERE ea.entitlement_value IN (
SELECT ed.entitlement_value
FROM entitlement_values ed
WHERE ed.entitlement_valuekey IN (
SELECT ael.entitlement_valuekey
FROM account_entitlements1 ael
WHERE ael.accountkey IN (
SELECT ua.accountkey
FROM user_accounts ua
WHERE ua.userkey IN (
SELECT userkey
FROM Users
WHERE userkey in (select userkey from Users where userkey in (${requestor}))
)
)
)
)
) AND (ev.customproperty3='RPO' and ev.customproperty3 IN (
SELECT ea.customproperty3
FROM entitlement_values ea
WHERE ea.entitlement_value IN (
SELECT ed.entitlement_value
FROM entitlement_values ed
WHERE ed.entitlement_valuekey IN (
SELECT ael.entitlement_valuekey
FROM account_entitlements1 ael
WHERE ael.accountkey IN (
SELECT ua.accountkey
FROM user_accounts ua
WHERE ua.userkey IN (select userkey from Users where userkey in (${requestor}))
)
)
)
))

Please let me know where Iogic I am missing here.

Thank you

5 REPLIES 5

rushikeshvartak
All-Star
All-Star

SELECT ev.entitlement_value
FROM entitlement_values ev
INNER JOIN user_accounts ua ON ev.entitlement_valuekey = ua.entitlement_valuekey
WHERE
(
(ev.customproperty4 = 'CPO' AND ev.customproperty3 LIKE 'ROLE_CPO%')
OR
(ev.customproperty3 = 'RPO' AND ev.customproperty3 LIKE 'ROLE_RPO%')
)
AND ua.userkey = ${requestor}


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Sunny
New Contributor III
New Contributor III

Hi @rushikeshvartak 

Thank you for your response however the above query was giving error in data analyzer for that I had modified the query but it is giving me the entitlement which was assigned for the user account in saviynt application.

Query Below:

SELECT ev.entitlement_value
FROM users u
JOIN user_accounts ua ON u.userkey = ua.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
JOIN account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
WHERE ua.userkey = ${requestor}

After that I tried to use of Case Statement in the Config for Requestable Entitlement in ARS however I am not getting the expected outcome as such.

Query:

SELECT
Entitlement_value,
Username,
CASE
WHEN ev.entitlement_value LIKE 'ROLE_CPO%' AND ev.entitlement_value = (
SELECT ev.entitlement_value
FROM users u
JOIN user_accounts ua ON u.userkey = ua.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
JOIN account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
WHERE ua.userkey = 2528
) THEN
(SELECT Entitlement_value
FROM entitlement_values
WHERE entitlementtypekey = 155 AND customproperty4 IS NOT NULL)
ELSE
(SELECT Entitlement_value
FROM entitlement_values
WHERE entitlementtypekey = 155 AND customproperty3 IS NOT NULL)
END AS Entitlement_Value
FROM
users u
JOIN user_accounts ua ON u.userkey = ua.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
JOIN account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey

Could you please help me what information I am missing here.

 

Thank you.

Only entitlement_values  table is exposed in Config for Requestable Entitlement in ARS. Hence create dynamic atribute and refer dynamic attribute in Config for Requestable Entitlement in ARS


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Sunny
New Contributor III
New Contributor III

Hi @rushikeshvartak Thanks for giving me the insight I have acheived the usecase.

Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.