Now we need a meta view which has information about the rollup group: note the top two levels are filtered out
CREATE OR REPLACE FORCE VIEW ORG_LOADER_ROLLUP_META_V ( GROUP_NAME, GROUP_DISPLAY_NAME, GROUP_DESCRIPTION, READERS, VIEWERS, ORG_ID, GROUP_OVERALL_NAME, PARENT_ID ) AS SELECT DISTINCT 'penn:community:employee:org:' || olv.org_name || ':' || olv.org_name || '_rolluporg_systemOfRecord' AS group_name, 'penn:community:employee:org:' || olv.org_name || ' - ' || olv.ORG_DISPLAY_NAME || ':' || olv.org_name || ' - ' || olv.ORG_DISPLAY_NAME || ' system of record' AS group_display_name, 'Members of ' || olv.org_name || ' and all groups underneath the hierarchy' AS group_description, 'penn:community:employee:orgSecurity:orgReaders' AS readers, 'penn:community:employee:orgSecurity:orgViewers' AS viewers, olv.org_name AS org_id, 'penn:community:employee:org:' || olv.org_name || ':' || olv.org_name || '_rolluporg' AS group_overall_name, olv.PARENT_ID FROM org_list_v olv WHERE EXISTS (SELECT OLV2.ORG_NAME FROM org_list_v olv2 WHERE OLV2.PARENT_ID = OLV.ORG_NAME) AND olv.PAYROLL_FLAG = 'N' AND olv.ORG_NAME NOT IN ('TOPU', 'UNIV', 'NOTU') ORDER BY 2; |
GROUP_NAME | GROUP_DISPLAY_NAME | GROUP_DESCRIPTION | READERS | VIEWERS | ORG_ID | GROUP_OVERALL_NAME | PARENT_ID |
---|---|---|---|---|---|---|---|
penn:community:employee:org:00XX:00XX_rolluporg_systemOfRecord | penn:community:employee:org:00XX - General University Parent:00XX - General University Parent system of record | Members of 00XX and all groups underneath the hierarchy | penn:community:employee:orgSecurity:orgReaders | penn:community:employee:orgSecurity:orgViewers | 00XX | penn:community:employee:org:00XX:00XX_rolluporg | UOTH |
penn:community:employee:org:02XX:02XX_rolluporg_systemOfRecord | penn:community:employee:org:02XX - School of Arts and Sciences Parent:02XX - School of Arts and Sciences Parent system of record | Members of 02XX and all groups underneath the hierarchy | penn:community:employee:orgSecurity:orgReaders | penn:community:employee:orgSecurity:orgViewers | 02XX | penn:community:employee:org:02XX:02XX_rolluporg | USCH |
CREATE OR REPLACE FORCE VIEW ORG_LOADER_ROLLUP_V ( GROUP_NAME, MEMBER_GROUP_NAME ) BEQUEATH DEFINER AS (SELECT DISTINCT rollup_parent.GROUP_NAME AS group_name, /* records which are rollups directly under the rollup */ rollup_child.GROUP_OVERALL_NAME AS subject_identifier FROM ORG_LOADER_ROLLUP_META_V rollup_parent, ORG_LOADER_ROLLUP_META_V rollup_child WHERE rollup_child.PARENT_ID = rollup_parent.ORG_ID) UNION /* payroll orgs (which hold people) directly under the rollup */ (SELECT DISTINCT rollup_parent.GROUP_NAME, 'penn:community:employee:org:' || olv_child.ORG_NAME || ':' || olv_child.ORG_NAME || '_personorg' AS subject_identifier FROM ORG_LOADER_ROLLUP_META_V rollup_parent, org_list_v olv_child WHERE olv_child.PARENT_ID = rollup_parent.org_id AND olv_child.PAYROLL_FLAG = 'Y'); |
GROUP_NAME | MEMBER_GROUP_NAME |
---|---|
penn:community:employee:org:00XX:00XX_rolluporg_systemOfRecord | penn:community:employee:org:0001:0001_personorg |
penn:community:employee:org:00XX:00XX_rolluporg_systemOfRecord | penn:community:employee:org:0007:0007_personorg |
penn:community:employee:org:02XX:02XX_rolluporg_systemOfRecord | penn:community:employee:org:BIOB:BIOB_rolluporg |
CREATE OR REPLACE FORCE VIEW ORG_LOADER_ROLLUP2_V ( GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID, SUBJECT_GROUP_NAME ) AS SELECT olrv.GROUP_NAME group_name, gg.ID AS subject_id, 'g:gsa' AS SUBJECT_SOURCE_ID, olrv.MEMBER_GROUP_NAME subject_group_name FROM org_loader_rollup_v olrv, grouper_groups gg WHERE gg.NAME = olrv.MEMBER_GROUP_NAME; COMMENT ON TABLE ORG_LOADER_ROLLUP2_V IS 'shows rollup group assignments'; |
GROUP_NAME | SUBJECT_ID | SUBJECT_SOURCE_ID | SUBJECT_GROUP_NAME |
---|---|---|---|
penn:community:employee:org:TOPU:UNIV:UADM:UADM_rolluporg_systemOfRecord | e5c4834ca09e45f8b635422cc1b6d4c1 | g:gsa | penn:community:employee:org:TOPU:UNIV:UADM:88XX:88XX_personorg |
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg_systemOfRecord | 0dd6217005be4b2996574fbcac0c1eec | g:gsa | penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG13:AG13_rolluporg |
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg_systemOfRecord | a2af451090644ed4b1d1d0ed57adf5d4 | g:gsa | penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG98:AG98_rolluporg |
penn:community:employee:org:TOPU:UNIV:UADM:UADM_rolluporg_systemOfRecord | 2fca9dbef7144c198b50bf48163d4cd4 | g:gsa | penn:community:employee:org:TOPU:UNIV:UADM:90XX:90XX_rolluporg |
gsh 4% rollupGroup = addGroup("penn:community:employee", "orgRollupConfig", "orgRollupConfig"); group: name='penn:community:employee:orgRollupConfig' displayName='penn:community:employee:orgRollupConfig' uuid='8b329babf720413d81f5004fb3729c02' gsh 6% groupAddType("penn:community:employee:orgRollupConfig", "grouperLoader"); true gsh 7% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderDbName", "grouper"); true gsh 8% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderQuartzCron", "0 06 7 * * ? "); true gsh 9% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderQuery", "select GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID from ORG_LOADER_ROLLUP2_V"); true gsh 10% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderScheduleType", "CRON"); true gsh 12% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderType", "SQL_GROUP_LIST"); true gsh 13% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderGroupQuery", "select group_name, group_display_name, group_description, readers, viewers from org_loader_rollup_meta_v"); true gsh 14% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderGroupsLike", "penn:community:employee:org:%_rolluporg_systemOfRecord"); true gsh 15% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoaderGroupTypes", "addIncludeExclude"); true gsh 18% rollupGroup = GroupFinder.findByName(grouperSession, "penn:community:employee:orgRollupConfig"); group: name='penn:community:employee:orgRollupConfig' displayName='penn:community:employee:orgRollupConfig' uuid='8b329babf720413d81f5004fb3729c02' gsh 19% loaderRunOneJob(rollupGroup); |
CREATE OR REPLACE FORCE VIEW ORG_CENTER_LIST_V ( CENTER_CODE, CENTER_NAME ) AS SELECT DISTINCT olv.CENTER_CODE, olv.CENTER_NAME FROM org_list_v olv WHERE center_code IS NOT NULL; COMMENT ON TABLE ORG_CENTER_LIST_V IS 'list of centers to make groups for'; |
CENTER_CODE | CENTER_NAME |
---|---|
26 | University Museum |
90 | Development and Alumni Relations |
87 | Division of Finance |
CREATE OR REPLACE FORCE VIEW ORG_CENTER_META_V ( GROUP_OVERALL_NAME, GROUP_NAME, GROUP_DISPLAY_NAME, GROUP_DESCRIPTION, READERS, VIEWERS, CENTER_CODE, CENTER_NAME ) AS SELECT 'penn:community:employee:center:' || oclv.CENTER_CODE || '_center:' || oclv.CENTER_CODE || '_center' AS group_overall_name, 'penn:community:employee:center:' || oclv.CENTER_CODE || '_center:' || oclv.CENTER_CODE || '_center_systemOfRecord' AS group_name, 'penn:community:employee:center:' || oclv.CENTER_CODE || ' center ' || oclv.CENTER_NAME || ':' || oclv.CENTER_CODE || ' center ' || oclv.CENTER_NAME || ' system of record' AS group_display_name, 'Center ' || oclv.CENTER_CODE || ' ' || oclv.CENTER_NAME || ' is a rollup of orgs and their includes/excludes' AS group_description, 'penn:community:employee:orgSecurity:orgReaders' AS readers, 'penn:community:employee:orgSecurity:orgViewers' AS viewers, center_code, center_name FROM org_center_list_v oclv; COMMENT ON TABLE ORG_CENTER_META_V IS 'list of groups managed by the center loader job'; |
CREATE OR REPLACE VIEW ORG_CENTER_ROLLUP_V (GROUP_NAME, MEMBER_GROUP_NAME) AS (select distinct ocmv.GROUP_NAME as group_name, /\* records which are rollups directly under the rollup \*/ rollup_child.GROUP_OVERALL_NAME as subject_identifier from org_center_meta_v ocmv, ORG_LOADER_ROLLUP_META_V rollup_child, org_list_v olv_child where olv_child.CENTER_CODE_ASSIGN = ocmv.CENTER_CODE and olv_child.ORG_NAME = rollup_child.ORG_ID ) union all /\* payroll orgs (which hold people) directly under the rollup \*/ (select distinct ocmv.GROUP_NAME , olpmv.GROUP_NAME as subject_identifier from org_center_meta_v ocmv, org_list_v olv_child, org_loader_person_meta_v olpmv where olv_child.CENTER_CODE_ASSIGN = ocmv.CENTER_CODE and olv_child.ORG_NAME = olpmv.ORG_ID) |
GROUP_NAME MEMBER_GROUP_NAME penn:community:employee:center:86_center:86_center_systemOfRecord penn:community:employee:org:86XX:86XX_rolluporg penn:community:employee:center:98_center:98_center_systemOfRecord penn:community:employee:org:98XX:98XX_rolluporg penn:community:employee:center:32_center:32_center_systemOfRecord penn:community:employee:org:32XX:32XX_rolluporg penn:community:employee:center:02_center:02_center_systemOfRecord penn:community:employee:org:0120:0120_personorg |
CREATE OR REPLACE VIEW ORG_CENTER_ROLLUP2_V (GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID, SUBJECT_GROUP_NAME) AS select ocrv.GROUP_NAME group_name, ga.GROUP_ID as subject_id, 'g:gsa' as SUBJECT_SOURCE_ID, ocrv.MEMBER_GROUP_NAME subject_group_name from org_center_rollup_v ocrv, grouper_attributes ga, grouper_fields gf where gf.NAME = 'name' AND gf.ID = ga.field_id and ga.VALUE = ocrv.MEMBER_GROUP_NAME |
This data looks like this
GROUP_OVERALL_NAME | GROUP_NAME | GROUP_DISPLAY_NAME | GROUP_DESCRIPTION | READERS | VIEWERS | CENTER_CODE | CENTER_NAME |
---|---|---|---|---|---|---|---|
penn:community:employee:center:22_center:22_center | penn:community:employee:center:22_center:22_center_systemOfRecord | penn:community:employee:center:22 center Domestic Subsidiaries:22 center Domestic Subsidiaries system of record | Center 22 Domestic Subsidiaries is a rollup of orgs and their includes/excludes | penn:community:employee:orgSecurity:orgReaders | penn:community:employee:orgSecurity:orgViewers | 22 | Domestic Subsidiaries |
gsh 4% centerGroup = addGroup("penn:community:employee", "centerRollupConfig", "centerRollupConfig"); group: name='penn:community:employee:centerRollupConfig' displayName='penn:community:employee:centerRollupConfig' uuid='8b329babf720413d81f5004fb3729c02' gsh 6% groupAddType("penn:community:employee:centerRollupConfig", "grouperLoader"); true gsh 7% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderDbName", "grouper"); true gsh 8% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderQuartzCron", "0 36 7 * * ? "); true gsh 9% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderQuery", "select GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID from ORG_CENTER_ROLLUP2_V"); true gsh 10% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderScheduleType", "CRON"); true gsh 12% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderType", "SQL_GROUP_LIST"); true gsh 13% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderGroupQuery", "select group_name, group_display_name, group_description, readers, viewers from org_center_meta_v"); true gsh 14% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderGroupsLike", "penn:community:employee:center:%_center_systemOfRecord"); true gsh 15% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperLoaderGroupTypes", "addIncludeExclude"); true gsh 18% centerGroup = GroupFinder.findByName(grouperSession, "penn:community:employee:centerRollupConfig"); group: name='penn:community:employee:centerRollupConfig' displayName='penn:community:employee:centerRollupConfig' uuid='8b329babf720413d81f5004fb3729c02' gsh 19% loaderRunOneJob(centerGroup); |
Add this config group
We dont have groupings of contractors in our payroll system, but we can set a flag in "Penn Community" our person database. So lets organize a way to automatically make groups based on flags. Lets make a table which identifies the flags we are looking for, and which group extension
CREATE TABLE ORG_SPONSOR_GROUP ( GROUP_EXTENSION VARCHAR2(128 CHAR) NOT NULL, LIKE_STRING_UPPER VARCHAR2(128 CHAR) ) |
* Currently we only have one row
GROUP_EXTENSION LIKE_STRING_UPPER 96XX_consultants 96XX |
* Lets make the meta view that describes the groups managed by this loader
CREATE OR REPLACE VIEW ORG_SPONSOR_META_V (GROUP_NAME, GROUP_DISPLAY_NAME, GROUP_DESCRIPTION, READERS, VIEWERS, GROUP_OVERALL_NAME, GROUP_EXTENSION) AS select 'penn:community:employee:sponsororg:' || osg.GROUP_EXTENSION || '_sponsororg:' || osg.GROUP_EXTENSION || '_sponsororg_systemOfRecord' as group_name, 'penn:community:employee:sponsororg:' || osg.GROUP_EXTENSION || '_sponsororg:' || osg.GROUP_EXTENSION || '_sponsororg system of record' as group_display_name, 'people in penn community with a substring of ' || osg.LIKE_STRING_UPPER || ' in their sponsor_org field somewhere' as group_description, 'penn:community:employee:orgSecurity:orgReaders' as readers, 'penn:community:employee:orgSecurity:orgViewers' as viewers, 'penn:community:employee:sponsororg:' || osg.GROUP_EXTENSION || '_sponsororg:' || osg.GROUP_EXTENSION || '_sponsororg' as group_overall_name, osg.GROUP_EXTENSION from org_sponsor_group osg |
* The data from that view looks like this
GROUP_NAME GROUP_DISPLAY_NAME GROUP_DESCRIPTION READERS VIEWERS GROUP_OVERALL_NAME GROUP_EXTENSION penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consultants_sponsororg_systemOfRecord penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consultants_sponsororg system of record people in penn community with a substring of 96XX in their sponsor_org field somewhere penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consultants_sponsororg 96XX_consultants |
* Make an assignment view which links up people with their one-off group
CREATE OR REPLACE VIEW ORG_SPONSOR_LIST_V (GROUP_NAME, SUBJECT_ID) AS (Select distinct osmv.GROUP_NAME as group_name, sav.v_penn_id as subject_id from ORG_SPONSOR_group osg, comadmin.ssn4_affiliation_view sav, ORG_SPONSOR_META_V osmv Where sav.v_active_code = 'A' and upper(sav.v_sponsor_org) like '%' || osg.LIKE_STRING_UPPER || '%' and osmv.GROUP_EXTENSION = osg.GROUP_EXTENSION) |
* The data from that view looks like this
GROUP_NAME SUBJECT_ID penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consultants_sponsororg_systemOfRecord 10128438 penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consultants_sponsororg_systemOfRecord 68214103 penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consultants_sponsororg_systemOfRecord 10135159 penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consultants_sponsororg_systemOfRecord 10114304 |
* Create the config group, and run the loader
gsh 4% sponsorGroup = addGroup("penn:community:employee", "orgSponsorConfig", "orgSponsorConfig"); group: name='penn:community:employee:orgSponsorConfig' displayName='penn:community:employee:orgSponsorConfig' uuid='8b329babf720413d81f5004fb3729c02' gsh 6% groupAddType("penn:community:employee:orgSponsorConfig", "grouperLoader"); true gsh 7% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderDbName", "grouper"); true gsh 8% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderQuartzCron", "0 16 7 * * ? "); true gsh 9% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderQuery", "select GROUP_NAME, SUBJECT_ID from ORG_SPONSOR_LIST_V"); true gsh 10% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderScheduleType", "CRON"); true gsh 12% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderType", "SQL_GROUP_LIST"); true gsh 13% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderGroupQuery", "select group_name, group_display_name, group_description, readers, viewers from org_sponsor_meta_v"); true gsh 14% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderGroupsLike", "penn:community:employee:sponsororg:%_sponsororg_systemOfRecord"); true gsh 15% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoaderGroupTypes", "addIncludeExclude"); true gsh 18% sponsorGroup = GroupFinder.findByName(grouperSession, "penn:community:employee:orgSponsorConfig"); group: name='penn:community:employee:orgSponsorConfig' displayName='penn:community:employee:orgSponsorConfig' uuid='8b329babf720413d81f5004fb3729c02' gsh 19% loaderRunOneJob(sponsorGroup); |
We need a group for all employees in Facilities and Real Estate Services. We need to add the VP (who is not in the Facilities org), and we need to add the contractors. I will add this in the org structure, so people can easily find it.
Protect a web resource in apache by requiring members to be in this group (note: authnz_ldap apache module grabs all members of the group unless a patch is applied that Penn developed which is not yet public)
<Directory "[directory]"> CosignProtected on AuthType Cosign CosignRequireFactor UPENN.EDU AuthzLDAPAuthoritative on AuthLDAPCompareDNOnServer on AuthLDAPBindPassword [password] AuthLDAPBindDN uid=[service principal],ou=entities,dc=upenn,dc=edu AuthLDAPLimitAttribute cn # custom attribute via local patch AuthLDAPURL ldaps://url.ldap.private/cn=penn:community:employee:org:96XX:96XX_andConsultants,ou=groups,dc=upenn,dc=edu?hasMember require ldap-dn cn=penn:community:employee:96XX:96XX_andConsultants,ou=groups,dc=upenn,dc=edu </Directory> |