Grouper Users - Open Discussion List

[Chris Hyzer <>]

Hey,

I spoke to Tom on the phone, and the dev team will discuss the long term 
support for a SQL interface for memberships in October (its possible, and 
hopeful for me that we can do this).

At Penn we use this view, and would have to do a lot of work to do without 
it, so I don't see it going away anytime soon :) .  In 1.5 all the views 
(including this one) are in the auto-DDL upgrade, so it will automatically 
adjust when we rework the underlying tables (which is happening in 1.5).

Anyways, if you want to try it in the short term and see if it is better than 
joining tables, go for it.  If you eventually get LDAP working for Grouper, 
and/or the TomZ component, we all agree that would be better...

Thanks,
Chris

> -----Original Message-----
> From: Tom Barton 
> [mailto:]
> Sent: Thursday, August 13, 2009 5:13 PM
> To: 
> 
> Cc: 
> 
> Subject: Re: [grouper-users] Querying Grouper database to produce
> attributes for Shibboleth
> 
> 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