I’m using a JDBC connector rule to pass attributes for users entitled to access to iSeries. If the user previously doesn’t have access and is now granted it, it sends a CREATE operation, which translates into inserting into a staging table. Additionally if the user exists (through a filtered down query with a where clause to only identify the unique user identifier.), it will issue an update on the existing record in the staging table.
Once this is done, a separate processing stored procedure is ran on the source system, to scan the staging table for updates or new records. If it is a new record, it will add the user to the iSeries user table. Processing the Modify operation, will just update fields from the staging table for the identified user.
Is the expectation of the JDBC connector rule that it would be maintaining the user table directly, rather than the staging table we are using? And if so, is the staging table route possible with a different method for the rule, or something entirely different?
The developer who made the processing sproc is confused as to why Sailpoint needs to query the values it has passed to the staging table when an attribute sync occurs. Is there a way we can avoid having the connector query for comparison for a sync attribute?
I’m coming in new to SailPoint, and learning as I go, so apologize if I am missing something.
I encountered a similar scenario where we had a user table and a staging table. Data is pulled into Sailpoint from the user table, but all the insertion/update information is stored in the staging table and later processed.
Since you aggregate data from the user’s table, the JDBC rule is connected to that particular database. So, any operation points to the user table. However, you can tweak something in the rule to perform the operation in the staging table, even though you are pulling data from the user’s table. I also researched a lot but couldn’t find anything so came up with an idea. To do that, you have to set up another database connection in the rule that points to the staging table. Then, you can use that connection to perform any database operation in the staging table.
That is correct. Changes received from Sailpoint go into the staging table. When it inserts, a process field is set from N to Y for the record in question and the processing sproc identifies that there is a pending change and updates the true user table of the system. Once done, the processing sproc sets the process field back to N. No records are removed from the staging table.
Thanks, Danish! Yes, this is very similar, however, the staging table has all existing users that are also in the end user table, so we aggregate the accounts from there.
Based on the information provided, it seems like all DM operations to users table is carried out by sproc (hence all records that exist in users table also exist in staging table), and IDN only reads the data from users table. I am also assuming both tables belong to the same DB.
In IDN, provisioning can happen in 2 scenarios:
Add or Remove Access in Downstream system
While adding any access (entitlement) to an identity if an account already exists for the identity in the source, IDN ships the provisioning plan with Modify operation where the attribute will be of type the entitlement being added. If account does not exist, then IDN will trigger a provisioning process with Create operation, followed by Add Entitlement operation (second operation quite similar to Modify operation explained above).
Attribute Sync for existing accounts
This happens when there is any change in an attribute in the Source Account Schema that is included in the Provisioning policy and checked in Attribute Sync.
Now what you want to do inside the JDBC rule is entirely up to you as IDN itself does not execute any query… You can read the contents of staging table and make a decision what kind of CRUD operation you want to carry out. Though I have never done this, I am quite sure you can run any query on any table in the DB as you have connection object for the DB, not table.
However, as you will not be updating the user table during provisioning process, delay in updating the user table might cause some issues while aggregating the updated user back to IDN. ie if an Account Aggregation runs between provisioning and user table updating at downstream, then all the records in IDN for a newly created user may disappear. Also, updates for an existing account may disappear as well. They should show up once sync in the DS is completed though.