Jorge's Quest For Knowledge!

All You Need To Know About Identity And Security On-Premises And In The Cloud. It's Just Like An Addiction, The More You Have, The More You Want To Have!

(2009-06-05) Exporting Multi-valued Attribute To SQL Table

Posted by Jorge on 2009-06-05


Based upon my post about "Multiple Authoritative Sources For Group Memberships And How About Precedence In ILM", a technology partner and I were setting up and test/demo environment. The idea was as follows.

The MGMT app is authoritative for groups and group memberships, which then flow into AD. Group Membership is established on business logic like for example:

  • Everyone with "JobTitle=Admin" and "Department=ICT" becomes a member of the group "R1Grp_EMPLOYEES_JOB_ICT_ADMIN"
  • Everyone with "employeeType=EMPLOYEES" becomes a member of the group "R1Grp_EMPLOYEES"
  • Etc.

However, in AD it must be possible to adjust/establish group memberships that do not follow the business logic. For example, a contractor is added to the group "R1Grp_EMPLOYEES". That new group membership flows (import) from AD to the MV through the "ADDS-Group-IMP" MA. From the MV it flows (export) to the SQL Database (multi-valued table) through the "MGMT-Group-EXP" MA.

When a group membership is established in the MGMT APP the following flags should be set in the SQL multiple valued tabled: MGMT=YES & IDM=NO (as properties of that specific group membership)

When a group membership is established in the MGMT APP the following flags should be set in the SQL multiple valued tabled: MGMT=NO & IDM=YES (as properties of that specific group membership)

This way the MGMT APP can check on eventual business conflicts by checking the flags and report on it!

So as a test we wanted to test this by adding a contractor person to the "R1Grp_EMPLOYEES" group. Initially the group "R1Grp_EMPLOYEES" contained 32 employee persons and after the change an extra contractor person was added to it.

What was the expect end result?

  • 32 employee group memberships with the flags MGMT=YES & IDM=NO
  • 1 contractor group membership with the flags MGMT=NO & IDM=YES

So I imported the group membership from AD into ILM and exported it to the SQL database.

What was the REAL end result?

  • 32 employee group memberships with the flags MGMT=NO & IDM=YES
  • 1 contractor group membership with the flags MGMT=NO & IDM=YES

What the heck?!?! Why are the flags of ALL group memberships for the group "R1Grp_EMPLOYEES" changed as if they were exported? I expect only one INSERT into the table and not 33 INSERTS.

The way to find out is to use SQL Server Profiler and check what’s happening under the hood! So let’s do this.

Before exporting to ILM I checked the ILM statistics. See picture below.

image

After exporting I checked the SQL Server Profiler Trace and saw the following…

image

Let’s take a look at this trace

  • Yellow marked text: Delete all existing group memberships for the group "R1Grp_EMPLOYEES"
  • Green marked text: Add the new group membership for the new member (the one established in AD)
  • Blue marked text: Add the new group membership for the previously existing members (the ones established in MGMT APP)

I wonder WHY ILM works this way…Anyone from the Product Group care to explain? Please do so!

Cheers,
Jorge
———————————————————————————————
* This posting is provided "AS IS" with no warranties and confers no rights!
* Always evaluate/test yourself before using/implementing this!
* DISCLAIMER:
https://jorgequestforknowledge.wordpress.com/disclaimer/
———————————————————————————————
############### Jorge’s Quest For Knowledge #############
#########
http://JorgeQuestForKnowledge.wordpress.com/ ########
———————————————————————————————

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: