Perhaps I have a misunderstanding of how the JDBC connector works in ISC, so apologies in advance.
We are trying to use a JDBC connector to provision entitlements to a user_groups table using the provisioning queries options in the ISC UI instead of provisioning rules. We have a users table, and a groups table. The groups table has entitlement names that we have loaded in ourselves.
My account schema has an attribute called entitlement that is set to entitlement and multi-valued.
The account provisioning options are as follows:
Create: entitlement.addEntitlementSQL - SELECT id FROM groups WHERE name = $plan.entitlement$; insert into user_groups (chicagoid,groupid) values($plan.chicagoid$, $response.id$)
Update:
Insert - entitlement.addEntitlementSQL - SELECT id FROM groups WHERE name = $plan.entitlement$; insert into user_groups (chicagoid,groupid) values($plan.chicagoid$, $response.id$)
Delete - entitlement.removeEntitlementSQL - SELECT id FROM groups WHERE name = $plan.entitlement$; delete from user_groups where chicagoid = $plan.chicagoid$ and groupid = $response.id$
I have made a role that can be requested to assign an entitlement but when it does so it says that the first parameter is missing, which I take to be $plan.entitlement$… What I really want it to do is to find the id of the entitlement and then insert that into the user_groups table, but can’t seem to figure it out. Is there a variable that I can reference that takes in the entitlement that was requested so that this query can work? As it stands, the account profile entitlements field can be empty but would eventually return rows of users and entitlements.
Looks like you has used the latest feature to provision access. Instead of using plan.entitlement you have to give the name of the entitlement which you have used in the account schema. For example if your entitlement name is roles then you have to use $plan.roles$ instead of $plan.entitlement$
Also make sure you are using the correct data type as per the doc. See this doc under Type Safety
And I’m putting the queries I pasted above into the query settings for account → Provisioning Query Settings.
The thing is, I was hoping that I could grab the entitlement that was approved and insert it into the table. This connector appears to be able to insert only entitlements that are appearing in the entitlements field for a user account, is this correct?
E.g. If I can’t define/link entitlements before SailPoint, it cannot insert them elsewhere? So SailPoint’s entitlement insert is really just using the value(s) from the entitlement field on account but cannot access or insert the requested entitlement name from a request-able entitlement on a JDBC source?
I would want it to insert the requested entitlement into the JDBC database without needing a value in the entitlement field because the entitlement name is in the Role request.
I figured this one out. Our entitlements were configured incorrectly. I was unaware they needed a value attached to them. If you don’t have the entitlement_id set to be different than the name, my queries did not work so it was complaining about a null value, thus the subquery did not have a value to work with. Once I did that, it works like I’d expect!
Final group query: SELECT id as entitlement_id, name as entitlement_desc from groups
Final Insert example: insert into user_groups (uniqueid,groupid) values($plan.uniqueid$, $plan.entitlement$) (note plan.entitlement will now be the entitlement id from the join
Final Remove example: delete from user_groups where chicagoid = $plan.uniqueid$ and groupid = $plan.entitlement$ (note plan.entitlement will now be the entitlement id from the join)
For those that also run into this issue, I’d recommend ensuring that your joins for the account schema for the entitlements do not return null as well.
