Grouper's views can be granted to schemas to make it possible to access Grouper data from SQL.
Notes/Warnings
Best practices to using the SQL interface
select gaagv.group_name from grouper_attr_asn_group_v gaagv, grouper_groups gg where gaagv.attribute_def_name_name = 'test:attrName' and gaagv.group_id = gg.id and gaagv.enabled = 'T' and gg.enabled = 'T' |
select gg.name from grouper_attr_asn_stem_v gaasv, grouper_stem_set gss, grouper_groups gg where gaasv.attribute_def_name_name = 'test:attrDef1' and gaasv.stem_id = gss.then_has_stem_id and gg.parent_stem = gss.if_has_stem_id and gg.enabled = 'T' and gaasv.enabled = 'T' |
select distinct gm.subject_id, gm.description from grouper_pit_memberships_lw_v gpmlv, grouper_pit_groups gpg, grouper_members gm where gpg.source_id ='b9452d5e6a824b598a5fc83939f2677d' and gpg.id = owner_group_id and the_start_time > '1616385600000000' and gm.id = gpmlv.member_id ; |
The main views to use are:
This view contains all memberships (direct, indirect, groups, people, etc). To return a simple list of effective membership for people try:
SELECT DISTINCT subject_id FROM grouper_memberships_lw_v WHERE group_name='{path:to:group}' AND subject_source='{people_subject_source}' AND list_name ='members'
Find all group admins
select group_name, subject_id from grouper_memberships_lw_v gmlv where list_name = 'admins' and SUBJECT_SOURCE = 'mySubjectSource' |
Find all stem admins
select stem_name, subject_id from grouper_mship_stem_lw_v gmslv where gmslv.LIST_NAME = 'stemAdmins' and gmslv.SUBJECT_SOURCE = 'mySubjectSource' ; |
Find groups someone can update (or admin)
select distinct gmlv.group_name from grouper_memberships_lw_v gmlv where gmlv.list_name in ('admins', 'updaters') and gmlv.subject_source = 'xyz' and gmlv.subject_id = '123' order by gmlv.group_name limit 100 |
select * from grouper_sync gs, grouper_sync_group gsg where gs.provisioner_name = 'blah' and gs.id = gsg.grouper_sync_id and gsg.provisionable = 'T' |
Replace provisionerId with the ID of the provisioner
select gaaagv.group_name, 'group' as owner_type from grouper_aval_asn_asn_group_v gaaagv where gaaagv.attribute_def_name_name2 like '%etc:provisioning:provisioningTarget' and gaaagv.value_string = 'provisionerId' union all select gaaasv.stem_name, 'stem' as owner_type from grouper_aval_asn_asn_stem_v gaaasv where gaaasv.attribute_def_name_name2 like '%etc:provisioning:provisioningTarget' and gaaasv.value_string = 'provisionerId' |
SELECT gaaag.group_name, gaaag.value_string FROM penngrouper.grouper_aval_asn_asn_group_v gaaag where gaaag.attribute_def_name_name2 like '%grouperLoaderQuery' |
create table temp_provisioning_assigns as SELECT gpaa_marker.source_id as attribute_assign_id_marker, case when gpaa_marker.owner_group_id is not null then 'group' when gpaa_marker.owner_stem_id is not null then 'stem' end as assign_type, case when gpaa_marker.owner_group_id is not null then gpg.name when gpaa_marker.owner_stem_id is not null then gps.name end as assign_name, gpadn_value.name attribute_name, gpaav.value_string, gpaav.end_time FROM grouper_pit_attribute_assign gpaa_marker left outer join grouper_pit_groups gpg on gpaa_marker.owner_group_id = gpg.id left outer join grouper_pit_stems gps on gpaa_marker.owner_stem_id = gps.id join grouper_pit_attribute_assign gpaa_value on gpaa_marker.id = gpaa_value.owner_attribute_assign_id join grouper_pit_attr_assn_value gpaav on gpaa_value.id = gpaav.attribute_assign_id join grouper_pit_attr_def_name gpadn_marker on gpaa_marker.attribute_def_name_id = gpadn_marker.id and gpadn_marker.name = 'etc:provisioning:provisioningMarker' join grouper_pit_attr_def_name gpadn_value on gpaa_value.attribute_def_name_id = gpadn_value.id WHERE (gpaa_marker.owner_group_id is not null or gpaa_marker.owner_stem_id is not null) and gpaav.end_time/1000000 > 1695814657 order by 1, 2, 3, 4; |
Make a SQL to generate GSH
select distinct attribute_assign_id_marker, assign_name, 'new Provisionable' || (case when exists (select 1 from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker and tpa2.assign_type = 'group') then 'Group' else 'Stem' end) || 'Save().assignTargetName("' || (select tpa2.value_string from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker and tpa2.attribute_name = 'etc:provisioning:provisioningTarget') || '").assign' || (case when exists (select 1 from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker and tpa2.assign_type = 'group') then 'Group' else 'Stem' end) || 'Name("' || (select distinct assign_name from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker) || '")"' || (case when exists (select 1 from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker and tpa2.assign_type = 'group') then 'Group' else 'Stem' end) || '".save();' as script from temp_provisioning_assigns tpa where exists (select 1 from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker and tpa2.value_string = 'true' and tpa2.attribute_name = 'etc:provisioning:provisioningDirectAssign') and assign_type = 'stem' order by 2; |