Provision Access where value is present in user's custom property

[Shubhamjain27]

Hi Everyone,

We have a requirement where we need to define the AD groups to be assigned based on the values present in user's CP.

Usecase and current process:

We have users coming from Workday and a dataset to decide the AD group to be assigned to the new AD account.

AD group is assigned on the basis of location. Few of the location need one AD group and some need 2-3 AD groups.

For example - user coming from WD - John Doe

Location - L1

Now in Dataset prepare like:

Location - AD Group

 L1 - AD Group 1

L1 - AD Group 2

L2 - AD Group 1

Now in this case, what we thought we will use preprocessor to add the groups to be assigned in user's cp31 as comma separated value.

So John Doe user CP31 would be having AD Group 1, AD Group 2

Now the question is, how we can provision both the groups to the newly created AD account.

1. Is it possible to separate out groups to provision using technical rules? or maybe reports?

2. If only one AD group is added in cp31, can we use user.cp31 in technical rule like below:

Any answers would be appreciated.

[rushikeshvartak]

You need to create 3 different rule considering 3 maximum group can be assigned.

Use substring to get 2nd / 3rd group name from cp31

[Shubhamjain27]

That we can do but we have 1000 groups, and dont want to create 1000 technical rules.

If needed we wont store them in the cp as comma separate if you have any other solution in mind.

[rushikeshvartak]

Then its not possible using technical rules\. Use actionable report with provision access 

[Shubhamjain27]

Do you have a sample SQL to separate comma separate values into separate rows so that we can use provision access actionable report?

[rushikeshvartak]

SELECT
TRIM(SPLIT_STR(column_name, ',', 1)) AS separated_value
FROM
table_name
UNION ALL
SELECT
TRIM(SPLIT_STR(column_name, ',', 2))
FROM
table_name
-- Add more UNION ALL statements for additional values as needed

[Shubhamjain27]

It is not working.

Query used:

SELECT
TRIM(SPLIT_STR(customproperty20, ',', 1)) AS separated_value
FROM
users
UNION ALL
SELECT
TRIM(SPLIT_STR(customproperty20, ',', 2)) AS separated_value
FROM
users

Logs:

[rushikeshvartak]

SELECT
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(customproperty20, ',', 1), ',', -1)) AS separated_value
FROM
users
UNION ALL
SELECT
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(customproperty20, ',', 2), ',', -1)) AS separated_value
FROM
users;

[Shubhamjain27]

Tried the below query and it gives an error as subquery returns more than 1 rows which is expectd:

select ev.ENTITLEMENT_VALUEKEY, ev.ENTITLEMENT_VALUE,a.accountkey as acctKey, u.userkey, u.username, 'Provision Access' as Default_Action_For_Analytics from entitlement_values ev, accounts a, users u, user_accounts ua , endpoints e where ev.ENTITLEMENT_VALUE = (select SUBSTRING_INDEX(customproperty16, ',', 1) AS separated_value
FROM
users) and u.userkey = ua.userkey
and ua.accountkey = a.accountkey and u.username = 'MB07766' and e.endpointkey = 92 and a.endpointkey = e.endpointkey and ev.ENTITLEMENTTYPEKEY=141

The issue is only with the subquery as it is returning more than 1 row. Any idea on it?

[rushikeshvartak]

SELECT
ev.ENTITLEMENT_VALUEKEY,
ev.ENTITLEMENT_VALUE,
a.accountkey AS acctKey,
u.userkey,
u.username,
'Provision Access' AS Default_Action_For_Analytics
FROM
entitlement_values ev
JOIN
accounts a ON a.endpointkey = 92
JOIN
user_accounts ua ON a.accountkey = ua.accountkey
JOIN
users u ON ua.userkey = u.userkey
JOIN
endpoints e ON a.endpointkey = e.endpointkey
JOIN
(SELECT DISTINCT
SUBSTRING_INDEX(customproperty16, ',', 1) AS separated_value,
userkey
FROM
users) AS sub_u
ON u.userkey = sub_u.userkey
AND ev.ENTITLEMENT_VALUE = sub_u.separated_value
WHERE
u.username = 'MB07766'
AND ev.ENTITLEMENTTYPEKEY = 141;

[Shubhamjain27]

I will try it out, thanks

[Shubhamjain27]

I made some more changes to get it working, below is the query. Now I want the query to fetch those entitlement which are coming through cp60 and not assigned to the respective accounts. Can you check if the below query works and you see any limitations for the same:

SELECT distinct
ev.ENTITLEMENT_VALUEKEY as entvaluekey,
ev.ENTITLEMENT_VALUE,
a.accountkey AS acctKey,
a.name as accName,
u.userkey as userKey,
u.username,
'Provision Access' AS Default_Action_For_Analytics
FROM
entitlement_values ev
JOIN
accounts a ON a.endpointkey
JOIN
user_accounts ua ON a.accountkey = ua.accountkey
JOIN
users u ON ua.userkey = u.userkey
JOIN
endpoints e ON a.endpointkey = e.endpointkey
JOIN
(SELECT replace(concat('CN',TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(customproperty60, 'CN', 2.2), 'CN', -1))), 'com,', 'com') as separated_value, userkey FROM Users) AS sub_u
ON u.userkey = sub_u.userkey
LEFT JOIN account_entitlements1 AE
ON AE.entitlement_valuekey = EV.entitlement_valuekey
AND AE.accountkey = A.accountkey
WHERE AE.accountkey IS NULL
AND ev.ENTITLEMENT_VALUE = sub_u.separated_value and a.status in ('1', 'Manually Provisioned')

Union All

SELECT distinct
ev.ENTITLEMENT_VALUEKEY as entvaluekey,
ev.ENTITLEMENT_VALUE,
a.accountkey AS acctKey,
a.name as accName,
u.userkey as userKey,
u.username,
'Provision Access' AS Default_Action_For_Analytics
FROM
entitlement_values ev
JOIN
accounts a ON a.endpointkey
JOIN
user_accounts ua ON a.accountkey = ua.accountkey
JOIN
users u ON ua.userkey = u.userkey
JOIN
endpoints e ON a.endpointkey = e.endpointkey
JOIN
(SELECT replace(concat('CN',TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(customproperty60, 'CN', 3.3), 'CN', -1))), 'com,', 'com') as separated_value, userkey FROM Users) AS sub_u
ON u.userkey = sub_u.userkey
LEFT JOIN account_entitlements1 AE
ON AE.entitlement_valuekey = EV.entitlement_valuekey
AND AE.accountkey = A.accountkey
WHERE AE.accountkey IS NULL
AND ev.ENTITLEMENT_VALUE = sub_u.separated_value and a.status in ('1', 'Manually Provisioned')

[rushikeshvartak]

How data is stored in cp60 multiple or single ?

SELECT DISTINCT
ev.ENTITLEMENT_VALUEKEY AS entvaluekey,
ev.ENTITLEMENT_VALUE,
a.accountkey AS acctKey,
a.name AS accName,
u.userkey AS userKey,
u.username,
'Provision Access' AS Default_Action_For_Analytics
FROM
entitlement_values ev
JOIN accounts a ON a.endpointkey = ev.endpointkey
JOIN user_accounts ua ON a.accountkey = ua.accountkey
JOIN users u ON ua.userkey = u.userkey
JOIN endpoints e ON a.endpointkey = e.endpointkey
JOIN (
SELECT
REPLACE(CONCAT('CN', TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(customproperty60, 'CN', 2.2), 'CN', -1))), 'com,', 'com') AS separated_value,
userkey
FROM Users
) AS sub_u ON u.userkey = sub_u.userkey
LEFT JOIN account_entitlements1 AE ON AE.entitlement_valuekey = ev.entitlement_valuekey
AND AE.accountkey = a.accountkey
WHERE
AE.accountkey IS NULL
AND ev.ENTITLEMENT_VALUE = sub_u.separated_value
AND a.status IN ('1', 'Manually Provisioned')

UNION ALL

SELECT DISTINCT
ev.ENTITLEMENT_VALUEKEY AS entvaluekey,
ev.ENTITLEMENT_VALUE,
a.accountkey AS acctKey,
a.name AS accName,
u.userkey AS userKey,
u.username,
'Provision Access' AS Default_Action_For_Analytics
FROM
entitlement_values ev
JOIN accounts a ON a.endpointkey = ev.endpointkey
JOIN user_accounts ua ON a.accountkey = ua.accountkey
JOIN users u ON ua.userkey = u.userkey
JOIN endpoints e ON a.endpointkey = e.endpointkey
JOIN (
SELECT
REPLACE(CONCAT('CN', TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(customproperty60, 'CN', 3.3), 'CN', -1))), 'com,', 'com') AS separated_value,
userkey
FROM Users
) AS sub_u ON u.userkey = sub_u.userkey
LEFT JOIN account_entitlements1 AE ON AE.entitlement_valuekey = ev.entitlement_valuekey
AND AE.accountkey = a.accountkey
WHERE
AE.accountkey IS NULL
AND ev.ENTITLEMENT_VALUE = sub_u.separated_value
AND a.status IN ('1', 'Manually Provisioned')

[Shubhamjain27]

I will try the above query.

Also do you know if cp31 contains only 1 value ie AD Group 1. In that case can we use technical rules to dynamically populate access shown below:

[rushikeshvartak]

Use ${user.customproperty31}

[Shubhamjain27]

I will try it out, thanks Rushikesh.

[RAVINDERAALLA]

Hi Shubham,

You can refer below proposed solution:

Location	AD Group
Location 1	Location1_ADGroup1
Location 1	Location1_ADGroup2
Location 2	Location2_ADGroup1
Location 2	Location2_ADGroup2

AD group(s) should be assigned based on the location.

Recommended Solution:

Create a Roles such as

• Role_Location1_ADGroup
  • Location1_ADGroup1
  • Location1_ADGroup2
• Role_Location2_ ADGroup
  • Location2_AD Group1
  • Location2_AD Group2

User Update Rule:  Trigger update rule when the location is updated/changed & trigger technical rule:

Technical Rule:

Condition: Users city not equals Null

Objecte Type: Enterprise Roles

Object: Role_${city}_ADGroup assign

Attribute: Assign

https://forums.saviynt.com/t5/identity-governance/dynamic-role-assignment/m-p/64341#M39983

https://forums.saviynt.com/t5/saviynt-knowledge-base/how-to-configure-dynamic-assignment-in-technica...

[Shubhamjain27]

Hi Ravinder,

We cant go ahead with the role approach as there are 2k AD groups depending on the location and it would be hard for the customer to manage it going forward incase they want to add new AD groups.

Any other solution you could think of?