Investigate ability to update relational data

Tony Sheehy 6 years ago • updated by anonymous 3 years ago 3

Consider the potential for updating relational data through a single core adapter. Need to consider:

  • Adds vs. Updates
  • If adds, default values
  • User expectations
  • What it means if Join criteria aren't met (adds or exception)
  • Supported transformations
  • 1:1 vs. 1:many
Affected Versions:
Fixed by Version:

Since I can't update the "relates to" links on this issue, could someone please create a link to CSODBB-295 for me?

CSODBB are presently using IdB 3.X, and so should this issue lead to support for my requirement, I would either have to upgrade to version 4.2 (problematic right now because Identity Broker for PHRIS HR is only supported on version 3.X and support for 4.2 would therefore be a prerequisite) or stand up a separate IdB 4.2 instance.

My requirement is essentially the reverse of what we usually use Identity Broker for now ... i.e. instead of converting relational into hierarchical (directory style) data for import to FIM, we are talking about exporting a hierarchy into an equivalent relational format. In this case I need to handle (for starters) provisioning the following 3 object classes (tables in SQL) and their references (think FK GUID columns) to each other:

  • person
  • position
  • organisation

I am yet to be supplied with the desired target SQL schema (CENet agreed that we should be able to get this from the Sydney diocese who've supposedly implemented something already), but I'm assuming that while there will need to be support for 1-to-many relationships, i.e. specifically

  • person <= position
  • position <= organisation
  • person <= organisation

I am not expecting us to need to support many-to-many at this stage (i.e. necessitating the use of "link tables" such as personPositio, positionOrganisation, etc.). I am hoping that all I will need to do is create the following FKs:

  • person.positionID
  • person.organisationID
  • position.organisationID

I see the goal as using a single FIM management agent with a composite IdB 4.* adapter which supports exports to all 3 objects such that the references can be translated to the corresponding FK IDs.

If I was to use FIM today to try to achieve this I would probably resort to a separate adapter/MA for each object class, and use FIM workflows to populate non-reference attributes in the FIM Metaverse for direct export to the FK columns. I haven't got my head around how hard this would be to do yet, but I would find a way ... just that it's unlikely to be very pretty.

I don't know how often this sort of requirement would come up - but it could be a case of not having previously seen a use for doing this sort of thing. I saw an example when at DEEWR whereby we had to export CLAIMS data to a SQL table - this could become more common (and more complex) as we move towards claims-based authentication and authorisation models.

Another use case from Bob Bradley:

3 Connectors in a (IdB 4) composite adapter:

  • user (atts include employeeId, email => FIM)
  • userEmployment (atts include domainAccountName <= FIM)
  • userEmail (atts include emailAddress <= FIM)


  • User 1-0 userEmployment
  • User 1-0 userEmail

Export Attribute flows:

  • FIM.user.domain+’\’+AccountName => CS.userEmployement.domainAccountName
  • FIM.user.email => CS.userEmail.emailAddress

Presently this requires all 3 connectors to be defined as separate entities in the composite adapter.
An improvement would be only to require 1 entity (User) with JOIN transforms to both userEmployment and userEmail when supporting write-backs. This would need to include the scenario whereby a userEmail record doesn’t already exist and needs to be created. Idea would be that the USER adapter could be used to determine which connector entity updates should be triggered by simply writing back to:

  • user.domainAccountName
  • user.emailAddress