Page History

...

1. Do not use the Grouper schema for accesses to the Grouper SQL interface, make another schema which is readonly and only has access to the required data (where clause)
2. Make a view on top of the Grouper tables/views and grant that to the other schema.  This is because when Grouper is upgraded the views are dropped (including grants), so they would be lost or need to be recreated

Groups with attribute

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'

Groups under stem with attribute

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'

Memberships

The main views to use are:

...

SELECT DISTINCT subject_id FROM grouper_membership_lw_v WHERE group_name="{path:to:group}" AND subject_source="{people_subject_source}" AND list_name ="members"

...

grouper_perms_all_v: This view can be used for Grouper permissions.  Note, DISALLOW is exposed through the SQL interface, and is not calculated.  So if DISALLOW is assigned in the data being used, you should either not use the SQL interface, or you would need to calculate the DISALLOWs after selecting the data (note, this is a complicated algorithm though it is documented on the wiki with examples).
...