Syncing objects to Grouper from SQL
This feature is available in Grouper v2.5.44+. If you are running large syncs make sure your daemon has a lot of memory and that large jobs do not run at the same time
This example shows you can provide your own SQL. If you are syncing from another Grouper instance you can let Grouper automatically generate the queries and detect which columns are available.
Example from training environment
Get the env up and running, identify some folders to sync. Lets get the app:vpn and the ref folders
We need a database connection to that database from our database
Note: you need a consistent subject source
Configure the sync to grouper from the training env
Use these queries, adjust the where clause so it matches the folders you want to sync. Run them first and make sure they work.Folders: select name, id, id_index, display_name, description, alternate_name from grouper_stems gs where name = 'app:vpn' or name like 'app:vpn:%' or name = 'ref' or name like 'ref:%' Groups: select name, alternate_name, description, disabled_timestamp, display_name, enabled_timestamp, id, id_index, type_of_group from grouper_groups gg where gg.name like 'app:vpn:%' or gg.name like 'ref:%' Composites: SELECT gc.id, group_owner.name AS owner_name, group_left_factor.name AS left_factor_name, group_right_factor.name AS right_factor_name, gc.type FROM grouper_composites gc, grouper_groups group_owner, grouper_groups group_left_factor, grouper_groups group_right_factor WHERE gc.owner = group_owner.id AND gc.left_factor = group_left_factor.id AND gc.right_factor = group_right_factor.id and ( group_owner.name like 'app:vpn:%' or group_owner.name like 'ref:%' ) Memberships: v2.4+ SELECT gmav.immediate_membership_id AS immediate_membership_id, gg.name AS group_name, gm.subject_source AS subject_source_id, gm.subject_id, gm.subject_identifier0 AS subject_identifier, gmav.immediate_mship_disabled_time, gmav.immediate_mship_enabled_time FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_groups gg, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.owner_group_id = gg.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.name = 'members' and ( gg.name like 'app:vpn:%' or gg.name like 'ref:%' ) v2.3 SELECT gmav.immediate_membership_id AS immediate_membership_id, gg.name AS group_name, gm.subject_source AS subject_source_id, gm.subject_id, (select gg2.name from grouper_groups gg2 where gm.subject_source='g:gsa' and gg2.id = gm.subject_id) as subject_identifier, gmav.immediate_mship_disabled_time, gmav.immediate_mship_enabled_time FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_groups gg, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.owner_group_id = gg.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.name = 'members' and ( gg.name like 'app:vpn:%' or gg.name like 'ref:%' ) Group privileges: v2.4+ SELECT gmav.immediate_membership_id AS immediate_membership_id, gg.name AS group_name, gm.subject_source AS subject_source_id, gm.subject_id, gm.subject_identifier0 AS subject_identifier, gf.name as field_name FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_groups gg, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.immediate_mship_enabled = 'T' AND gmav.owner_group_id = gg.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.type = 'access' and ( gg.name like 'app:vpn:%' or gg.name like 'ref:%' ) v2.3 SELECT gmav.immediate_membership_id AS immediate_membership_id, gg.name AS group_name, gm.subject_source AS subject_source_id, gm.subject_id, (select gg2.name from grouper_groups gg2 where gm.subject_source='g:gsa' and gg2.id = gm.subject_id) as subject_identifier, gf.name as field_name FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_groups gg, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.immediate_mship_enabled = 'T' AND gmav.owner_group_id = gg.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.type = 'access' and ( gg.name like 'app:vpn:%' or gg.name like 'ref:%' ) Folder privileges: v2.4+ SELECT gmav.immediate_membership_id AS immediate_membership_id, gs.name AS stem_name, gm.subject_source AS subject_source_id, gm.subject_id, gm.subject_identifier0 AS subject_identifier, gf.name as field_name FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_stems gs, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.immediate_mship_enabled = 'T' AND gmav.owner_stem_id = gs.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.type = 'naming' and ( gs.name = 'app:vpn' or gs.name like 'app:vpn:%' or gs.name = 'ref' or gs.name like 'ref:%' ) v2.3 SELECT gmav.immediate_membership_id AS immediate_membership_id, gs.name AS stem_name, gm.subject_source AS subject_source_id, gm.subject_id, (select gg2.name from grouper_groups gg2 where gm.subject_source='g:gsa' and gg2.id = gm.subject_id) as subject_identifier, gf.name as field_name FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_stems gs, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.immediate_mship_enabled = 'T' AND gmav.owner_stem_id = gs.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.type = 'naming' and ( gs.name = 'app:vpn' or gs.name like 'app:vpn:%' or gs.name = 'ref' or gs.name like 'ref:%' )Sample config: grouper-loader.properties
db.grouperTraining.url = jdbc:mysql://localhost:3306/grouper?CharSet=utf8&useUnicode=true&characterEncoding=utf8 db.grouperTraining.user = root ldap.grouperTrainingLdap.pass = password ldap.grouperTrainingLdap.searchResultHandlers = org.ldaptive.handler.DnAttributeEntryHandler,edu.internet2.middleware.grouper.ldap.ldaptive.GrouperRangeEntryHandler ldap.grouperTrainingLdap.tls = false ldap.grouperTrainingLdap.uiTestAttributeName = ou ldap.grouperTrainingLdap.uiTestExpectedValue = people ldap.grouperTrainingLdap.uiTestFilter = (ou=people) ldap.grouperTrainingLdap.uiTestSearchDn = dc=internet2,dc=edu ldap.grouperTrainingLdap.uiTestSearchScope = ONELEVEL_SCOPE ldap.grouperTrainingLdap.url = ldap://localhost:389/ ldap.grouperTrainingLdap.user = cn=root,dc=internet2,dc=edu otherJob.syncToGrouperFromTrainingDb.class = edu.internet2.middleware.grouper.app.syncToGrouper.SyncToGrouperFromSqlDaemon otherJob.syncToGrouperFromTrainingDb.quartzCron = 0 03 5 * * ? otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperAutoconfigureColumns = false otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperCompositeDeleteExtra = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperCompositeInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperCompositeSql = SELECT gc.id, group_owner.name AS owner_name, group_left_factor.name AS left_factor_name, group_right_factor.name AS right_factor_name, gc.type FROM grouper_composites gc, grouper_groups group_owner, grouper_groups group_left_factor, grouper_groups group_right_factor WHERE gc.owner = group_owner.id AND gc.left_factor = group_left_factor.id AND gc.right_factor = group_right_factor.id and ( group_owner.name like 'app:vpn:%' or group_owner.name like 'ref:%' ) otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperCompositeSync = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperCompositeSyncFieldIdOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperCompositeUpdate = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperDatabaseConfigId = grouperTraining otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperFromAnotherGrouper = false otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupDeleteExtra = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSql = select name, alternate_name, description, disabled_timestamp, display_name, enabled_timestamp, id, id_index, type_of_group from grouper_groups gg where gg.name like 'app:vpn:%' or gg.name like 'ref:%' otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSync = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSyncFieldAlternateName = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSyncFieldDescription = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSyncFieldDisplayName = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSyncFieldEnabledDisabled = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSyncFieldIdIndexOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSyncFieldIdOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupSyncFieldTypeOfGroup = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperGroupUpdate = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperLogOutput = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperMembershipDeleteExtra = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperMembershipInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperMembershipSql = SELECT gmav.immediate_membership_id AS immediate_membership_id, gg.name AS group_name, gm.subject_source AS subject_source_id, gm.subject_id, gm.subject_identifier0 AS subject_identifier, gmav.immediate_mship_disabled_time, gmav.immediate_mship_enabled_time FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_groups gg, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.owner_group_id = gg.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.name = 'members' and ( gg.name like 'app:vpn:%' or gg.name like 'ref:%' ) otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperMembershipSync = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperMembershipSyncFieldIdOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperMembershipSyncFieldsEnabledDisabled = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperMembershipUpdate = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeGroupDeleteExtra = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeGroupInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeGroupSql = SELECT gmav.immediate_membership_id AS immediate_membership_id, gg.name AS group_name, gm.subject_source AS subject_source_id, gm.subject_id, gm.subject_identifier0 AS subject_identifier, gf.name as field_name FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_groups gg, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.immediate_mship_enabled = 'T' AND gmav.owner_group_id = gg.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.type = 'access' and ( gg.name like 'app:vpn:%' or gg.name like 'ref:%' ) otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeGroupSync = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeGroupSyncFieldIdOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeStemDeleteExtra = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeStemInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeStemSql = SELECT gmav.immediate_membership_id AS immediate_membership_id, gs.name AS stem_name, gm.subject_source AS subject_source_id, gm.subject_id, gm.subject_identifier0 AS subject_identifier, gf.name as field_name FROM grouper_memberships_all_v gmav, grouper_members gm, grouper_stems gs, grouper_fields gf WHERE gmav.mship_type = 'immediate' AND gmav.immediate_mship_enabled = 'T' AND gmav.owner_stem_id = gs.id AND gmav.member_id = gm.id AND gmav.field_id = gf.id AND gf.type = 'naming' and ( gs.name = 'app:vpn' or gs.name like 'app:vpn:%' or gs.name = 'ref' or gs.name like 'ref:%' ) otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeStemSync = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperPrivilegeStemSyncFieldIdOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperReadonly = false otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemDeleteExtra = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemSql = select name, id, id_index, display_name, description, alternate_name from grouper_stems gs where name = 'app:vpn' or name like 'app:vpn:%' or name = 'ref' or name like 'ref:%' otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemSync = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemSyncFieldAlternateName = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemSyncFieldDescription = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemSyncFieldDisplayName = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemSyncFieldIdIndexOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemSyncFieldIdOnInsert = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperStemUpdate = true otherJob.syncToGrouperFromTrainingDb.sqlSyncToGrouperTopLevelStems = app:vpn, refRun readonly and see report
Run read/write and see changes