Grouper Developers Forum

[Chris Hyzer <>]

I fixed this bug:

https://bugs.internet2.edu/jira/browse/GRP-231

Which adds the SQL view we primarily use at Penn for SQL Grouper interface.  
If we need to use this and not LDAP or WS, we typically make a view on top of 
it which limits which memberships can be seen (e.g. where group_name in 
('a:b', 'c:d')), then we grant SELECT on that new view to the schema who 
needs access.

My suggestion that we should discuss at the member meeting perhaps, is that 
this view should be officially supported as a view applications can query 
directly.

The columns are:

SUBJECT_ID, SUBJECT_SOURCE, GROUP_NAME, LIST_NAME, LIST_TYPE, GROUP_ID

With this view you are able to do the following things at least (assume 
members list) [note, you should use prepared statements with bind variables]:

1. list the members of a group:

select source_id, subject_id from grouper_memberships_lw_v
where group_name = 'a:b:c' and list_type = 'list' and list_name = 'members'

2. list the groups of a member

select group_name from grouper_memberships_lw_v
where subject_source = 'jdbc' and subject_id = 'abc'
and list_type = 'list' and list_name = 'members'

3. see if a member is in a group

select 'T' from grouper_memberships_lw_v
where group_name = 'a:b:c' and subject_source = 'jdbc' and subject_id = 'abc'
and list_type = 'list' and list_name = 'members'

4. select all the groups a person can read (note, grouperAll, grouperSystem, 
and wheelGroups are not implemented here, though they could be)

select group_name from grouper_memberships_lw_v
where subject_source = 'jdbc' and subject_id = 'abc'
and list_type = 'access' and list_name in ('readers', 'admins')

Regards,
Chris