Grouper Users - Open Discussion List

[Tom Barton <>]

Chris, won't this whole approach cease to return indirect memberships 
with 1.5?

Richard, I also have concern since we don't commit to supporting a 
specific SQL form of logical group information access. Any SQL-based 
approach may break across certain grouper versions.

The new LDAP provisioner TomZ is developing, or one of its components, 
*might* prove to be pluggable into a shibboleth IdP, providing a grouper 
data connector to the IdP. You might want to look into that prospect for 
the longer term as an alternative to using LDAP provisioning to meet the 
IdP's needs.

Tom

Chris Hyzer wrote:
> 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