Grouper Users - Open Discussion List

[Chris Hyzer <>]

I agree that LDAP is currently the best way to go.  I hope we can provide 
another high performance / high availability option in 1.6 or 1.7 for when 
LDAP isn't convenient...

Another low cost option you could consider to improve performance is a 
materialized view.  Mysql doesn't have these like oracle, but this page at 
the bottom gives an option that seems like it might work:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Create a table 'shib_lookup' with two cols: login_id, and group_name, and 
index the login_id at least.  Shib should query that table.
Run these commands periodically (e.g. every 30 minutes?  You can tradeoff 
up-to-date data vs performance)

CREATE TABLE shib_lookup_new SELECT distinct 
concat('${campusid.get(0)}','@ncl.ac.uk'), ga.value FROM ...
RENAME TABLE shib_lookup to shib_lookup_old, shib_lookup_new to shib_lookup;
DROP TABLE IF EXISTS shib_lookup_old;


Regarding your query, if that was the whole thing (was it truncated?), I 
don't think it is correct.

SELECT DISTINCT ga.value AS grouper_groups FROM grouper_attributes AS ga, 
grouper_members gm, grouper_memberships AS gms WHERE ga.value LIKE '%:%' 
AND ga.value NOT LIKE '% %' 
AND ga.group_id = gms.owner_id
AND gms.member_id = gm.id
AND gm.subject_id = concat('${campusid.get(0)}','@ncl.ac.uk'

This will select all attribute values for a group (with colon and no space) 
the person is in in any list or privilege.  You need to change it so that you 
are only looking at the "name" attribute (assuming you want that and not 
display name) [join attribute to grouper_field], and then you need to only 
look at the members list I would think (not other lists, and not privileges) 
[join membership to grouper_field].  Its probably also a good idea to put the 
subject_source in there as well...


However, if you do the "materialized view" approach, you might try just using 
the grouper view, since the performance of that is a little less important 
(only running once every so often, not with each login).  :)  Actually, I 
have a Jira to put the "lightweight" membership view I use at penn into 
grouper for 1.5... why don't you just add that now:

CREATE OR REPLACE VIEW GROUPER_MEMBERSHIPS_LW_V
(SUBJECT_ID, SUBJECT_SOURCE, GROUP_NAME, LIST_NAME, LIST_TYPE, 
 GROUP_ID)
AS 
select distinct gm.SUBJECT_ID, gm.SUBJECT_SOURCE, ga.VALUE as group_name,
gfl.NAME as list_name, gfl.TYPE as list_type, ga.GROUP_ID
 from grouper_memberships gms, grouper_members gm, 
grouper_attributes ga, grouper_fields gfa, grouper_fields gfl
where gms.OWNER_ID = ga.GROUP_ID and ga.FIELD_ID = gfa.ID
and gfa.NAME = 'name' and gms.FIELD_ID = gfl.ID
and gms.MEMBER_ID = gm.ID


Then select from it like this (where a and b are whatever your subject 
sources are, maybe you only have 1 applicable):

select distinct concat(subject_id, '@ncl.ac.uk') as login_id, group_name 
from grouper_memberships_lw_v gmlv
where list_type = 'list' and list_name = 'members'
and subject_source in ('a','b')

The whole thing would be:

Step 1: Create the table and index:

CREATE TABLE shib_lookup SELECT distinct concat(subject_id, '@ncl.ac.uk') as 
login_id, group_name from grouper_memberships_lw_v gmlv where list_type = 
'list' and list_name = 'members' and subject_source in ('a','b');

alter table shib_lookup add index login_id_idx (login_id);


Step 2: Put these four statements on a cron:

CREATE TABLE shib_lookup_new SELECT concat(subject_id, '@ncl.ac.uk') as 
login_id, group_name from grouper_memberships_lw_v gmlv where list_type = 
'list' and list_name = 'members' and subject_source in ('a','b');

alter table shib_lookup_new add index login_id_idx (login_id);

RENAME TABLE shib_lookup to shib_lookup_old, shib_lookup_new to shib_lookup;

DROP TABLE shib_lookup_old;


Good luck!
Regards,
Chris



> -----Original Message-----
> From: Jim Fox 
> [mailto:]
> Sent: Thursday, August 13, 2009 12:43 PM
> To: 
> 
> Cc: 
> 
> Subject: Re: [grouper-users] Querying Grouper database to produce
> attributes for Shibboleth
> 
> 
> >
> > So we are wondering if anyone has any advice on the best way to get
> data out of the grouper database to be used in situations such as the
> above? If this is not the best route to go down we are also considering
> looking into using the LDAP connector as an alternative.
> >
> 
> LDAP will always be a lot faster for this type of query.  In addition
> LDAP
> servers are very easily replicated.
> 
> Jim