There are tables to keep track of provisioning state

The identifier in the provisioning tables is the type of provisioner and the name.  e.g. for SQL sync this could be 

Provisioner type: sqlTableSync

Provisioner name: someMembershipSync

Create a view that has these memberships (note, change the provisioner name, subject source)

Simple view of memberships from a query like this


select distinct gmlv.group_id, gmlv.group_name, gmlv.subject_id, gmlv.member_id
  FROM grouper_memberships_lw_v gmlv, grouper_sync gsync, grouper_sync_group gsg
  WHERE gmlv.SUBJECT_SOURCE = 'jdbc' and gmlv.list_name = 'members'
       and gsync.sync_engine = 'SQL_SYNC_ENGINE' and gsync.provisioner_name = 'someLdap'
       and gsync.id = gsg.grouper_sync_id and gsg.group_id = gmlv.group_id 
       and gsg.provisionable = 'T'

This query option is more complex but might perform better especially on mysql or database where views of views are not ideal


select distinct gg.id as group_id, gg.name as group_name, gm.subject_id, gm.id as member_id
  FROM grouper_memberships ms, grouper_group_set gs, grouper_groups gg, grouper_fields gfl, grouper_members gm,
       grouper_sync gsync, grouper_sync_group gsg
  WHERE ms.owner_id = gs.member_id AND ms.field_id = gs.member_field_id and ms.enabled = 'T'
       and gs.OWNER_GROUP_ID = gg.id AND gs.FIELD_ID = gfl.ID and GM.SUBJECT_SOURCE = 'jdbc'
       and gfl.name = 'members'
       and gsync.sync_engine = 'SQL_SYNC_ENGINE' and gsync.provisioner_name = 'someLdap'
       and gsync.id = gsg.grouper_sync_id and gsg.group_id = gg.id 
       and gsg.provisionable = 'T'

Simple view of memberships and privileges (if you need privileges too)


select distinct gmlv.group_id, gmlv.group_name, gmlv.subject_id, gmlv.member_id, gmlv.list_name
  FROM grouper_memberships_lw_v gmlv, grouper_sync gsync, grouper_sync_group gsg
  WHERE gmlv.SUBJECT_SOURCE = 'jdbc' 
       and gsync.sync_engine = 'SQL_SYNC_ENGINE' and gsync.provisioner_name = 'someLdap'
       and gsync.id = gsg.grouper_sync_id and gsg.group_id = gmlv.group_id 
       and gsg.provisionable = 'T'

Simple view of memberships / privileges


select distinct gg.id as group_id, gg.name as group_name, gm.subject_id, gm.id as member_id
  FROM grouper_memberships ms, grouper_group_set gs, grouper_groups gg, grouper_fields gfl, grouper_members gm,
       grouper_sync gsync, grouper_sync_group gsg
  WHERE ms.owner_id = gs.member_id AND ms.field_id = gs.member_field_id and ms.enabled = 'T'
       and gs.OWNER_GROUP_ID = gg.id AND gs.FIELD_ID = gfl.ID and GM.SUBJECT_SOURCE = 'jdbc'
       and gfl.name = 'members'
       and gsync.sync_engine = 'SQL_SYNC_ENGINE' and gsync.provisioner_name = 'someLdap'
       and gsync.id = gsg.grouper_sync_id and gsg.group_id = gg.id 
       and gsg.provisionable = 'T'


Complex view of memberships / privileges might perform better especially in mysql
  • No labels