This topic is discussed in the Advanced Topics training video. |
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' |
SELECT gaaag.group_name, gaaag.value_string FROM penngrouper.grouper_aval_asn_asn_group_v gaaag where gaaag.attribute_def_name_name2 like '%grouperLoaderQuery' |