In regards to what you said in this resolved post:
This is something I’m currently working on and struggling with. I have a JDBC connector to a SQL database server that I am aggregating Accounts from.
That server has a Group table on it that I have been tasked with aggregating into IDN as Entitlements. (not hard.)
But the database server ALSO has a relational table in it (called AccountGroupsRel) that ties the User Accounts table to the Groups table.
Is there a way to bring this relational table into IDN so that IDN User Accounts will already be tied to their respective Entitlements? I’m struggling finding anything in the Sailpoint JDBC connector documentation on this. Is there a feature in Sailpoint I’m missing or is this a SQL query I need to write?
In your account SQL query you need to join in that AccountGroupsRel table so that you can fetch the entitlements assigned to each account. Once you have that join working properly, the results for one user are typically spread across multiple rows if a user has more than one entitlement assigned, something like this:
User1,Ent1
User1,Ent2
User2,Ent1
...
Very basic account query example
SELECT Users.ID
AccountGroupsRel.GroupID
FROM Users
LEFT JOIN AccountGroupsRel ON AccountGroupsRel.UserID = Users.ID
ORDER BY Users.ID
Next, data merging comes into play so you can combine all of the entitlements assigned to that user into a single multi-valued attribute, the attribute which you will mark as the entitlement attribute on your account schema as well.
The last important thing here is that you want the column that represents the entitlements assigned to the user to be the unique identifier of the group in the Group table, and then on your group schema, the Entitlement ID should be that same unique identifier. This essentially links up the user’s entitlement attribute values to the entitlements in the catalog properly.
Lastly, make sure that entitlement attribute on the account schema’s type is set to group as well.
Also a lesson I learned, if there’s any chance that the user Ids might be inconsistently cased and they’re text, go ahead and wrap a LOWER around it to ensure they’re all the same case.
I ran into this once where one user account in an app had been assigned a role via AD and another one manually in the application, and the casing between the two email addresses (the unique identifier) was different despite it being the same email address, resulting in the aggregation process losing its mind because apparently the multiple user entitlement merging is case-sensitive.
Ok, so it looks like I’ll have to write some SQL queries…
Based on your example, I worry that the aggregation into IDN will get messed up…
After building a table with all the joined tables, wont duplicates happen in IDN?
For example:
Access Level
Permission
Name
Admin
Read
Tim
Admin
Write
Tim
Admin
Execute
Tim
Operator
Read
Frank
Operator
Execute
Frank
Audit
Read
Sarah
If we aggregate the above table (using the Account SQL Query in the JDBC settings within IDN) won’t that create a bunch of duplicate users called Tim and Frank? How do we separate the entitlements out of the query to …
I think I may have figure it out while I was typing… So we use the same query in both the Account SQL Query and Group SQL Query script.
BUT
In the Account SQL Query Script, we only select the Name.
In the Group SQL Query Script, we only select the Permission and Access Level…
Then in the Schemas we tie everything together…
Am I on the right track or am I just confusing myself more? XD
@bburrell Generate a primary key from composite keys, you need to append the columns ‘Access Level’ and ‘Permissions’. This ensures that your table does not contain duplicates and that each row has a unique entitlement ID.
Access Level
Permission
UserID
Group ID
Admin
Read
1
Admin_Read
Admin
Write
1
Admin_Write
Admin
Execute
1
Admin_Execute
SELECT Users.ID
Concat(AccountGroupsRel.AccessLevel,'_',AccountGroupsRel.Permission) as Group ID
FROM Users
LEFT JOIN AccountGroupsRel ON AccountGroupsRel.UserID = Users.ID
ORDER BY Users.ID
If you are aggregating entitlements your entitlement aggregation query also changes to.
select Concat(AccountGroupsRel.AccessLevel,'_',AccountGroupsRel.Permission) as Group ID
From GROUPS
Access Level, Permission)
Overall, the drawback of this method is that the amount of entitlements generated is equivalent to the number of access levels multiplied by the permissions.
OK! We’re really close everyone! This type of aggregation with LEFT JOIN doesn’t work though. If there’s a NULL “entry” in one of the Columns/Rows, the aggregation/schema generation fails. It says there is no value found in the Access Level…
Access Level
Permission
Name
Admin
Read
Tim
Admin
Write
Tim
NULL
Execute
Tim
NULL
Read
Frank
Operator
Execute
Frank
Audit
Read
Sarah
Is this a common Sailpoint IDN problem or am I missing something? It seems to me IDN should be able to populate NULL fields with NULL…
In this instance, is NULL an acceptable value for that field, or is that a data cleanup issue that should be taken up with the downstream teams?
If you need to handle it, there are a couple ways to do it, depending on which DB Server you are using:
You can use the where clause to skip rows that have NULL by adding “AND AccessLevel IS NOT NULL” (for MSSQL) for each field that should not have nulls in it.
You could see if your DB has a function to default a field to a value if none exists, such as "Select ISNULL(AccessLevel, “FoundNULL”) (for MSSQL) which would return the string “FoundNULL” for the access level or any other field it is used with. ** NOTE: This is less desirable, as if there are more fields with null, those could be overwritten with the last value found.
Hello! Sorry I was out and didn’t get a chance to respond to all of this! You good sir definitely know your stuff! I was able to get this all working and I’m glad to say I’m importing accounts and entitlements into IDN and keeping their relationship at the same time!
Thank you!
Sailpoint is pretty powerful, but I didn’t realize I would have to write so much SQL in order to get this to work. It’s a good thing I didn’t fall asleep during my database classes! XD hahahahah!
Anyway, thanks to ALL who helped! I really appreciate all the brains that went into this. You are all amazing. Thank you!
In the final sql query to get the data, for every field that are linked to an entitlement entity in sailpoint you need to have all the combination.
If you have 2 fields then you sql query must generate duplicates the line with all the combinations, etc etc.
I tested it and it worked that way and to avoid any issues you need to check your query generate all the line for a single user without mixing other users in it.
If you also want another field multivalued that is not an entitlement coming for another table, you can, but then you must add an order clause, if not you’ll have an error during aggregation.
For instance, giving that your accounts are in accounts table, your first entitlement type with key name ent_id (in the account schema in idn) is in table account_entitlements1 and your second entitlement type with key name ent2_id (in the account schema in idn) is in table account_entitlements2, the query to use in sailpoint will be :
select acc.*, links1.ent_id, links2.ent_id as ent2_id from accounts acc left outer join account_entitlements1 links1 on acc.acount_id = links1.acount_id left outer join account_entitlements2 links2 on acc.acount_id = links2.acount_id
Sure! What @pbourgi said is pretty spot on. I had to write a SQL Query that would join all my disparate tables together before I could aggregate them into Sailpoint IDN. I’m working on a blog post detailing what I did and will post a link to it here once it’s approved.
It’s a good thing I didn’t fall asleep during my database classes! XD hahahahah!