Main Grouper Loader page

Summary

University of Pennsylvania implemented class-lists with the Grouper Loader which includes lists of students, instructors, assistants (to instructors), guests, and all members.  The students, instructors, and assistants are fed from our student system, so they should be include/exclude.  The guests are an ad hoc group.  The "all" members should include the groups: students, instructors, assistants, guests.  There can be cross listings (multiple names for a course), and the cross listing groups should just contain the primary course groups (5 groups: all, students, instructors, guests, assistants).  Security should be setup on all courses such that there is a global readers group, a global updaters group, and in each course the instructors and assistants should be able to read and update the appropriate groups.   Note you need Grouper 1.4 built after 10/18/2009, or 1.5+ for all of this to work properly.

Naming

Our naming convention is similar to name groups like this (this is just one course of many):

penn:community:student:course   -   root of all courses
penn:community:student:loader   - holds groups with loader rules
penn:community:student:security   - holds security groups (e.g. readers or updaters of all courses)

penn:community:student:course:2009C:EG:CIS:120:203:all

      - holds all members for term 2009C, engineering, computer science, course 120, section 203
      - includes the students, instructors, assistants, and guests

penn:community:student:course:2009C:EG:CIS:120:203:assistants
      - overall assistants to instructors (system of record, add includes, remove excludes)
penn:community:student:course:2009C:EG:CIS:120:203:assistants excludes
      -assistants to instructors excludes list (remove from system of record)
penn:community:student:course:2009C:EG:CIS:120:203:assistants includes
      - assistants to instructors includes list
penn:community:student:course:2009C:EG:CIS:120:203:assistants_systemOfRecord
      - assistants to instructors system or record from student system
penn:community:student:course:2009C:EG:CIS:120:203:guests
      - ad hoc guests group to course
penn:community:student:course:2009C:EG:CIS:120:203:instructors
      - overall instructors group (system of record, add includes, subtract excludes)
penn:community:student:course:2009C:EG:CIS:120:203:instructors excludes
      - instructors excludes, will block members from system of record
penn:community:student:course:2009C:EG:CIS:120:203:instructors includes
      - instructors includes, to add members to the system of record
penn:community:student:course:2009C:EG:CIS:120:203:instructors_systemOfRecord
      - instructors system of record, form student system
penn:community:student:course:2009C:EG:CIS:120:203:students
      - overall students list, made up of system of record, add includes, subtract excludes
penn:community:student:course:2009C:EG:CIS:120:203:students excludes
      - students excludes removes students from the system of record
penn:community:student:course:2009C:EG:CIS:120:203:students includes
      - students includes add members to the system of record
penn:community:student:course:2009C:EG:CIS:120:203:students_systemOfRecord
      - students system of record is fed from student system

Security design

There are two high level global security groups for system or admins:

penn:community:student:security:courseReaders   (can read all course membership lists)
penn:community:student:security:courseUpdaters   (can update all course membership lists)

The instructors and assistants can read all groups in the course (listed above), and all cross-listed courses to that course.

They can update:

• guests
• instructors includes
• instructors excludes
• students includes
• students excludes
• assistants includes
• assistants excludes

Note, no cross listed courses are editable since they mirror the primary course

Primary course students list

For primary courses (not cross listings), lets setup a job which manages the memberships and security: This shows:

• The data is coming from our warehouse (configured in grouper-loader.properties)
• There is a grouper group type applied to the group, which is addIncludeExclude.  This automatically creates the includes, excludes, and overall groups
• The job runs once per day at 8am
• It is a SQL_GROUP_LIST which manages many groups at once
• The membership query: (penn_id and group_name one), returns data like this:
  12345678    penn:community:student:course:2009C:EG:BE:099:001:students_systemOfRecord
  12345679    penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord
  12345677    penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord
  12345676    penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord
  12345675    penn:community:student:course:2009C:EG:BE:101:001:students_systemOfRecord
  12345678    penn:community:student:course:2009C:EG:BE:101:001:students_systemOfRecord
• The groups query, controls empty groups, names groups, and sets the security.  It returns data that looks like this:

  GROUP_NAME	READERS	UPDATERS
  penn:community:student:course:2009C:EG:BE:099:001:students_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors
  penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:100:001:assistants,penn:community:student:course:2009C:EG:BE:100:001:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:100:001:assistants,penn:community:student:course:2009C:EG:BE:100:001:instructors
  penn:community:student:course:2009C:EG:BE:100:201:students_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:100:201:assistants,penn:community:student:course:2009C:EG:BE:100:201:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:100:201:assistants,penn:community:student:course:2009C:EG:BE:100:201:instructors

• Note that the queries in the loader job are built on views, it is important that you do this so you can see when upgrades make things not compile, and so the SQL can be changed without editing the job, and since there is a character limit for attribute values
• First I create a stem name view, which includes if a course is primary or not (cross listed).  Note this also restricts which terms to select from

CREATE OR REPLACE VIEW COURSE_GROUP_STEM_NAME_V
(GROUP_NAME_STEM, TERM, SECTION_ID, XLIST_PRIMARY, PRIMARY_COURSE)
AS select 'penn:community:student:course:' || trim(cs.term) || ':' || trim(cs.section_school)
            || ':' || trim(cs.subject_area)
           || ':' || trim(cs.course_number) || ':' || trim(cs.section_number)
            as group_name_stem,
           cs.term, cs.section_id, cs.XLIST_PRIMARY, decode(cs.SECTION_ID, cs.XLIST_PRIMARY, 'T', 'F') as primary_course
           from course_section cs, course_section cs_xlist_primary, course_term_v ctv ...

• Then I built on that to create a course group name which has all the names of the groups (for easy reuse)

CREATE OR REPLACE VIEW COURSE_GROUP_NAME_V
(GROUP_NAME_STEM, STUDENTS, STUDENTS_SYSTEMOFRECORD, STUDENTS_INCLUDES, STUDENTS_EXCLUDES,
 INSTRUCTORS, INSTRUCTORS_SYSTEMOFRECORD, INSTRUCTORS_INCLUDES, INSTRUCTORS_EXCLUDES, ASSISTANTS,
 ASSISTANTS_SYSTEMOFRECORD, ASSISTANTS_INCLUDES, ASSISTANTS_EXCLUDES, ALLGROUP, GUESTS,
 SECTION_ID, TERM, XLIST_PRIMARY, PRIMARY_COURSE)
AS
select
cgsnv.GROUP_NAME_STEM,
cgsnv.GROUP_NAME_STEM || ':students' as students,
cgsnv.GROUP_NAME_STEM || ':students_systemOfRecord' as students_systemOfRecord,
cgsnv.GROUP_NAME_STEM || ':students_includes' as students_includes,
cgsnv.GROUP_NAME_STEM || ':students_excludes' as students_excludes,
cgsnv.GROUP_NAME_STEM || ':instructors' as instructors,
cgsnv.GROUP_NAME_STEM || ':instructors_systemOfRecord' as instructors_systemOfRecord,
cgsnv.GROUP_NAME_STEM || ':instructors_includes' as instructors_includes,
cgsnv.GROUP_NAME_STEM || ':instructors_excludes' as instructors_excludes,
cgsnv.GROUP_NAME_STEM || ':assistants' as assistants,
cgsnv.GROUP_NAME_STEM || ':assistants_systemOfRecord' as assistants_systemOfRecord,
cgsnv.GROUP_NAME_STEM || ':assistants_includes' as assistants_includes,
cgsnv.GROUP_NAME_STEM || ':assistants_excludes' as assistants_excludes,
cgsnv.GROUP_NAME_STEM || ':all' as allGroup,
cgsnv.GROUP_NAME_STEM || ':guests' as guests,
cgsnv.SECTION_ID,
cgsnv.TERM,
cgsnv.XLIST_PRIMARY,
cgsnv.PRIMARY_COURSE
from COURSE_GROUP_STEM_NAME_V cgsnv

• Then we can build course list view (note the members of cross listed courses are includes in the primary course)

CREATE OR REPLACE VIEW COURSE_PRIMARY_STUDENT_LIST_V
(GROUP_NAME, PENN_ID, TERM, SECTION_ID)
AS
SELECT
  distinct cgnv.STUDENTS_SYSTEMOFRECORD as group_name,
  eav.PENN_ID,
  eav.TERM,
  cs.XLIST_PRIMARY section_id
FROM
  DWADMIN.ENROLLMENT_ALL_V  eav,
  DWADMIN.COURSE_SECTION  cs,
  course_group_name_v cgnv
WHERE
  eav.SECTION_ID=cs.section_id and eav.TERM=cs.TERM
  and cgnv.SECTION_ID = cs.XLIST_PRIMARY
  and cgnv.TERM = cs.term
  and cgnv.PRIMARY_COURSE = 'T'

• Finally we make the primary students group view for the students list which includes the security aspects

CREATE OR REPLACE VIEW COURSE_PRIM_STUD_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS, SECTION_ID, TERM)
AS
select cgnv.STUDENTS_SYSTEMOFRECORD as group_name,
'penn:community:student:security:courseReaders, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as updaters, cgnv.SECTION_ID, cgnv.TERM
from course_group_name_v cgnv where cgnv.PRIMARY_COURSE = 'T'

Primary course instructors list

Note: this is called instructors since it is more of a role than a title, so it is not "Professors".  Here is the loader screenshot:

• This is a query which runs from our warehouse connection (configured in grouper-loader.properties), add include/exclude type to the groups for include exclude lists, runs at 8:05 in the morning
• The membership query builds on a view which gives instructors or admins for a course (or any of its cross listings)

select cpiav.INSTRUCTOR_PENN_ID penn_id,
cpiav.instructors_GROUP_NAME as group_name
from COURSE_PRIMARY_INSTR_ASST_V cpiav
where cpiav.INSTRUCTOR_LOAD <> 0

• This returns data like this:

  PENN_ID	GROUP_NAME
  12345678	penn:community:student:course:2009C:EG:BE:200:204:instructors_systemOfRecord
  12345678	penn:community:student:course:2009C:EG:MGMT:586:501:instructors_systemOfRecord
  12345677	penn:community:student:course:2009C:EG:MSE:990:001:instructors_systemOfRecord
  12345676	penn:community:student:course:2009C:EG:BE:497:001:instructors_systemOfRecord

• The groups view gives the name of the group and the security rules

CREATE OR REPLACE VIEW COURSE_PRI_INSTR_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS, SECTION_ID, TERM)
AS
select cgnv.instructors_SYSTEMOFRECORD as group_name,
'penn:community:student:security:courseReaders, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as updaters, cgnv.SECTION_ID, cgnv.TERM
from course_group_name_v cgnv

• This returns data that looks like this:

  GROUP_NAME	READERS	UPDATERS	SECTION_ID	TERM
  penn:community:student:course:2009C:AS:ANTH:258:401:instructors_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors	ANTH258401	2009C
  penn:community:student:course:2009C:FA:ARCH:727:401:instructors_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors	ARCH727401	2009C

Primary course assistants list

• Note, this is not called "teaching assistants" since this group is more of a role than a title
• Here is the loader screenshot
• This runs from the data warehouse (configured in grouper-loader.properties), at 8:10 in the morning, and includeExclude is added for the include and exclude lists
• Here is a view of the members:

select cpiav.INSTRUCTOR_PENN_ID penn_id,
cpiav.ASSISTANTS_GROUP_NAME as group_name
from COURSE_PRIMARY_INSTR_ASST_V cpiav
where cpiav.INSTRUCTOR_LOAD = 0

• Here is a sample of the data returned:

  PENN_ID	GROUP_NAME
  38430684	penn:community:student:course:2009C:EG:MEAM:210:203:assistants_systemOfRecord
  45535464	penn:community:student:course:2009C:EG:CIS:120:203:assistants_systemOfRecord
  24636453	penn:community:student:course:2009C:EG:ESE:112:001:assistants_systemOfRecord

• Here is the view of the group query:

CREATE OR REPLACE VIEW COURSE_PRIM_ASST_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS, SECTION_ID, TERM)
AS
select cgnv.assistants_SYSTEMOFRECORD as group_name,
'penn:community:student:security:courseReaders, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as updaters, cgnv.SECTION_ID, cgnv.TERM
from course_group_name_v cgnv

• Here is an example of data returned:

  GROUP_NAME	READERS	UPDATERS	SECTION_ID	TERM
  penn:community:student:course:2009C:AS:ANTH:258:401:assistants_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors	ANTH258401	2009C
  penn:community:student:course:2009C:FA:ARCH:727:401:assistants_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors	ARCH727401	2009C
  penn:community:student:course:2009C:EG:BE:099:001:assistants_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors	BE  099001	2009C

•  

Primary guests group

The guests group is an ad hoc group, but we want it auto-created, and managed by the loader for security.  The grouper loader doesnt really support this at the moment (we should add support), but one workaround is just to sync the members from the grouper membership table (immediate memberships).  Here is the loader screenshot

• Note, this is driven from the grouper db connection, since it needs to hit the grouper registry for membership info.  The members view (needs to join with a db link to the warehouse to get the group names), note: driving site means that the names should be brought from the warehouse to the grouper registry, then calculated there.  It runs at 8:15 (even though screen says 8:20, this was changed).  Note this query will need to change in 1.5

CREATE OR REPLACE VIEW COURSE_GUESTS_MEMBERS_V
(GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID)
AS
select /*+DRIVING_SITE(gga)*/
cgnv.guests as group_name, gm.subject_id, gm.SUBJECT_SOURCE as subject_source_id
 from course_group_name_v@authzadm_warehouse cgnv,
grouper_attributes gga,
grouper_fields ggf, grouper_memberships gms, grouper_fields gf,
grouper_members gm
where gga.field_id = ggf.ID and ggf.NAME = 'name'
and gga.VALUE = cgnv.guests
and cgnv.primary_course = 'T'
and gms.OWNER_ID = gga.GROUP_ID
and gms.FIELD_ID = gf.ID
and gms.MSHIP_TYPE = 'immediate'
and gf.NAME = 'members'
and gms.MEMBER_ID = gm.id

• This gives data like this:

  GROUP_NAME	SUBJECT_ID	SUBJECT_SOURCE_ID
  penn:community:student:course:2009C:AS:ANTH:258:401:guests	12345678	pennperson
  penn:community:student:course:2009C:AS:ANTH:258:402:guests	12345677	pennperson

• The groups query is run via db link as well (since the members query must be run from grouper registry, and both queries must run from same db).  Here is the view (on warehouse)

CREATE OR REPLACE VIEW COURSE_GUESTS_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS)
AS
select primary_cgnv.guests as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as updaters
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'

• Here is an example of the data:

  GROUP_NAME	READERS	UPDATERS
  penn:community:student:course:2009C:EG:BE:099:001:guests	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors
  penn:community:student:course:2009C:EG:BE:100:001:guests	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:100:001:assistants,penn:community:student:course:2009C:EG:BE:100:001:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:100:001:assistants,penn:community:student:course:2009C:EG:BE:100:001:instructors
  penn:community:student:course:2009C:EG:BE:100:201:guests	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:100:201:assistants,penn:community:student:course:2009C:EG:BE:100:201:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:100:201:assistants,penn:community:student:course:2009C:EG:BE:100:201:instructors

•  

The 'All' groups for primary courses

Now that we have the students, instructors, assistants and guests, we can roll up all of those into an 'all' group.  Here is the loader screenshot:

• This runs at 8:20 (change from before, the screen says 8:15).  Since this builds on existing groups and we need to know the group id for the subject id.
• Here is the membership view (in warehouse db):

CREATE OR REPLACE VIEW COURSE_ALL_GROUP_MEMBERS_V
(GROUP_NAME, MEMBER_GROUP_NAME)
AS
select primary_cgnv.allgroup as group_name,
primary_cgnv.ASSISTANTS as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
union all
select primary_cgnv.allgroup as group_name,
primary_cgnv.guests as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
union all
select primary_cgnv.allgroup as group_name,
primary_cgnv.students as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
union all
select primary_cgnv.allgroup as group_name,
primary_cgnv.instructors as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'

• Here is the membership view (in grouper db): [note, this query will change in 1.5)

CREATE OR REPLACE VIEW COURSE_ALL_GROUP_V
(GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID, MEMBER_GROUP_NAME)
AS
select /*+DRIVING_SITE(gga)*/
cagmv.GROUP_NAME,
gga.GROUP_ID as subject_id, 'g:gsa' as subject_source_id,
cagmv.member_group_name
from COURSE_ALL_GROUP_MEMBERS_V@authzadm_warehouse cagmv, grouper_attributes gga,
grouper_fields ggf
where gga.field_id = ggf.ID and ggf.NAME = 'name'
and gga.VALUE = cagmv.member_group_name

• Here is an example of the data returned:

• Here is the group view

CREATE OR REPLACE VIEW COURSE_ALL_GROUP_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS)
AS
select primary_cgnv.ALLGROUP as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers,
null as updaters
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'

• Here is an example of the data

  GROUP_NAME	READERS	UPDATERS
  penn:community:student:course:2009C:EG:BE:099:001:all	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors
  penn:community:student:course:2009C:EG:BE:100:001:all	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:100:001:assistants,penn:community:student:course:2009C:EG:BE:100:001:instructors

Crosslisted course lists

This is the screenshot for the crosslist loader job

• Again, since this is loading groups in other groups, we need to run from the grouper DB connection.  It says 6:15, but this is really run at 8:30am.
• Here is the cross list members view in the warehouse:

CREATE OR REPLACE VIEW COURSE_XLIST_GROUP_MEMBERS_V
(GROUP_NAME, MEMBER_GROUP_NAME)
AS
select xlist_cgnv.ASSISTANTS as group_name,
primary_cgnv.ASSISTANTS as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.instructors as group_name,
primary_cgnv.instructors as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.students as group_name,
primary_cgnv.students as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.ALLGROUP as group_name,
primary_cgnv.allgroup as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.GUESTS as group_name,
primary_cgnv.guests as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID

• Here is the members query in the grouper database

CREATE OR REPLACE VIEW COURSE_XLIST_V
(GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID, XLIST_PRIMARY_GROUP_NAME)
AS
select cgm.GROUP_NAME,
ga.GROUP_ID as subject_id, 'g:gsa' as subject_source_id,
cgm.member_group_name as xlist_primary_group_name
from course_xLIST_GROUP_MEMBERS_V@authzadm_warehouse cgm, grouper_attributes ga,
grouper_fields gf
where ga.field_id = gf.ID and gf.NAME = 'name'
and ga.VALUE = cgm.member_group_name

• Here is a sample of the data returned

  GROUP_NAME	SUBJECT_ID	SUBJECT_SOURCE_ID	XLIST_PRIMARY_GROUP_NAME
  penn:community:student:course:2009C:AS:ANTH:258:401:assistants	dc0b03453453453454357d7d23c4	g:gsa	penn:community:student:course:2009C:EG:CIS:106:401:assistants
  penn:community:student:course:2009C:FA:ARCH:727:401:all	234234234234234234234234234	g:gsa	penn:community:student:course:2009C:EG:IPD:527:401:all
  penn:community:student:course:2009C:EG:BE:330:401:instructors	435345345345345345345435345	g:gsa	penn:community:student:course:2009C:EG:MSE:330:401:instructors

• Here is the all group query

CREATE OR REPLACE VIEW COURSE_XLIST_GROUP_GROUP_V
(GROUP_NAME, READERS)
AS
select xlist_cgnv.ASSISTANTS as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.INSTRUCTORS as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.STUDENTS as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.allgroup as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.guests as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID

• Here is sample data:

  GROUP_NAME	READERS
  penn:community:student:course:2009C:AS:ANTH:258:401:assistants	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:CIS:106:401:assistants,penn:community:student:course:2009C:EG:CIS:106:401:instructors
  penn:community:student:course:2009C:FA:ARCH:727:401:instructors	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:IPD:527:401:assistants,penn:community:student:course:2009C:EG:IPD:527:401:instructors

•