Date: Fri, 29 Mar 2024 11:21:01 +0000 (UTC) Message-ID: <1551758937.7883.1711711261868@ip-10-10-7-29.ec2.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_7882_1039952511.1711711261867" ------=_Part_7882_1039952511.1711711261867 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
This= is continued from part 1
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:empl= oyee:org:' || olv.org_name || ':' || olv.org_name || '_rolluporg_systemOfRecord' AS group_name, 'penn:community:empl= oyee:org:' || olv.org_name || ' - ' || olv.ORG_DISPLAY_NAME || ':' || olv.org_name || ' - ' || olv.ORG_DISPLAY_NAME || ' system of record' AS group_display_nam= e, 'Members of ' || olv.org_name || ' and all groups underneath th= e hierarchy' AS group_description= , 'penn:community:employee:orgSecur= ity:orgReaders' AS readers, 'penn:community:employee:orgSecur= ity:orgViewers' AS viewers, olv.org_name AS org_id, 'penn:community:empl= oyee:org:' || olv.org_name || ':' || olv.org_name || '_rolluporg' AS group_overall_nam= e, olv.PARENT_ID FROM org_list_v olv WHERE EXISTS (SELEC= T OLV2.ORG_NAME  = ; FROM org_list_v olv2  = ;WHERE OLV2.PARENT_ID =3D OLV.ORG_NAME) AND olv.PAYROLL_FLAG =3D '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_rollupo= rg_systemOfRecord |
penn:community:employee:org:00XX - General Un= iversity Parent:00XX - General University Parent system of record |
Members of 00XX and all groups underneath the= hierarchy |
penn:community:employee:orgSecurity:orgReader= s |
penn:community:employee:orgSecurity:orgViewer= s |
00XX |
penn:community:employee:org:00XX:00XX_rollupo= rg |
UOTH |
penn:community:employee:org:02XX:02XX_rollupo= rg_systemOfRecord |
penn:community:employee:org:02XX - School of = Arts and Sciences Parent:02XX - School of Arts and Sciences Parent system o= f record |
Members of 02XX and all groups underneath the= hierarchy |
penn:community:employee:orgSecurity:orgReader= s |
penn:community:employee:orgSecurity:orgViewer= s |
02XX |
penn:community:employee:org:02XX:02XX_rollupo= rg |
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 =3D 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 =3D rollup_parent.org_id AND olv_child.PAYROLL_FLAG =3D 'Y');
GROUP_NAME |
MEMBER_GROUP_NAME |
---|---|
penn:community:employee:org:00XX:00XX_rollupo= rg_systemOfRecord |
penn:community:employee:org:0001:0001_persono= rg |
penn:community:employee:org:00XX:00XX_rollupo= rg_systemOfRecord |
penn:community:employee:org:0007:0007_persono= rg |
penn:community:employee:org:02XX:02XX_rollupo= rg_systemOfRecord |
penn:community:employee:org:BIOB:BIOB_rollupo= rg |
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_nam= e FROM org_loader_rollup_v olrv, grouper_groups gg WHERE gg.NAME =3D 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:UA= DM_rolluporg_systemOfRecord |
e5c4834ca09e45f8b635422cc1b6d4c1 |
g:gsa |
penn:community:employee:org:TOPU:UNIV:UADM:88= XX:88XX_personorg |
penn:community:employee:org:TOPU:NOTU:HCAG:99= XX:99XX_rolluporg_systemOfRecord |
0dd6217005be4b2996574fbcac0c1eec |
g:gsa |
penn:community:employee:org:TOPU:NOTU:HCAG:99= XX:AG13:AG13_rolluporg |
penn:community:employee:org:TOPU:NOTU:HCAG:99= XX:99XX_rolluporg_systemOfRecord |
a2af451090644ed4b1d1d0ed57adf5d4 |
g:gsa |
penn:community:employee:org:TOPU:NOTU:HCAG:99= XX:AG98:AG98_rolluporg |
penn:community:employee:org:TOPU:UNIV:UADM:UA= DM_rolluporg_systemOfRecord |
2fca9dbef7144c198b50bf48163d4cd4 |
g:gsa |
penn:community:employee:org:TOPU:UNIV:UADM:90= XX:90XX_rolluporg |
gsh 4% = rollupGroup =3D addGroup("penn:community:employee", "orgRollupConfig", "org= RollupConfig"); group: name=3D'penn:community:employee:orgRollupConfig' displayName=3D'penn= :community:employee:orgRollupConfig' uuid=3D'8b329babf720413d81f5004fb3729c= 02' gsh 6% groupAddType("penn:community:employee:orgRollupConfig", "grouperLoad= er"); true gsh 7% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoad= erDbName", "grouper"); true gsh 8% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoad= erQuartzCron", "0 06 7 * * ? "); true gsh 9% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoad= erQuery", "select GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID from ORG_LOADER= _ROLLUP2_V"); true gsh 10% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoa= derScheduleType", "CRON"); true gsh 12% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoa= derType", "SQL_GROUP_LIST"); true gsh 13% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoa= derGroupQuery", "select group_name, group_display_name, group_description, = readers, viewers from org_loader_rollup_meta_v"); true gsh 14% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoa= derGroupsLike", "penn:community:employee:org:%_rolluporg_systemOfRecord"); true gsh 15% setGroupAttr("penn:community:employee:orgRollupConfig", "grouperLoa= derGroupTypes", "addIncludeExclude"); true gsh 18% rollupGroup =3D GroupFinder.findByName(grouperSession, "penn:commun= ity:employee:orgRollupConfig"); group: name=3D'penn:community:employee:orgRollupConfig' displayName=3D'penn= :community:employee:orgRollupConfig' uuid=3D'8b329babf720413d81f5004fb3729c= 02' 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_lis= t_v olv_child where olv_child.CENTER_CODE_ASSIGN =3D ocmv.CENTER_CODE and olv_child.ORG_N= AME =3D 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 =3D ocmv.CENTER_CODE and olv_child.ORG_N= AME =3D olpmv.ORG_ID)
GROUP_N= AME MEMBER_GROUP_NAME penn:community:employee:center:86_center:86_center_systemOfRecord penn:comm= unity:employee:org:86XX:86XX_rolluporg penn:community:employee:center:98_center:98_center_systemOfRecord penn:comm= unity:employee:org:98XX:98XX_rolluporg penn:community:employee:center:32_center:32_center_systemOfRecord penn:comm= unity:employee:org:32XX:32XX_rolluporg penn:community:employee:center:02_center:02_center_systemOfRecord penn:comm= unity: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 SU= BJECT_SOURCE_ID, ocrv.MEMBER_GROUP_NAME subject_group_name from org_center_rollup_v ocrv, grouper_attributes ga, grouper_fields gf where gf.NAME =3D 'name' AND gf.ID =3D ga.field_id and ga.VALUE =3D ocrv.ME= MBER_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_cent= er | penn:community:employee:center:22_center:22_cent= er_systemOfRecord | penn:community:employee:center:22 center Domesti= c Subsidiaries:22 center Domestic Subsidiaries system of record | Center 22 Domestic Subsidiaries is a rollup of o= rgs and their includes/excludes | penn:community:employee:orgSecurity:orgReaders= td> | penn:community:employee:orgSecurity:orgViewers= td> | 22 | Domestic Subsidiaries |
gsh 4% = centerGroup =3D addGroup("penn:community:employee", "centerRollupConfig", "= centerRollupConfig"); group: name=3D'penn:community:employee:centerRollupConfig' displayName=3D'p= enn:community:employee:centerRollupConfig' uuid=3D'8b329babf720413d81f5004f= b3729c02' gsh 6% groupAddType("penn:community:employee:centerRollupConfig", "grouperL= oader"); true gsh 7% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperL= oaderDbName", "grouper"); true gsh 8% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperL= oaderQuartzCron", "0 36 7 * * ? "); true gsh 9% setGroupAttr("penn:community:employee:centerRollupConfig", "grouperL= oaderQuery", "select GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID from ORG_CEN= TER_ROLLUP2_V"); true gsh 10% setGroupAttr("penn:community:employee:centerRollupConfig", "grouper= LoaderScheduleType", "CRON"); true gsh 12% setGroupAttr("penn:community:employee:centerRollupConfig", "grouper= LoaderType", "SQL_GROUP_LIST"); true gsh 13% setGroupAttr("penn:community:employee:centerRollupConfig", "grouper= LoaderGroupQuery", "select group_name, group_display_name, group_descriptio= n, readers, viewers from org_center_meta_v"); true gsh 14% setGroupAttr("penn:community:employee:centerRollupConfig", "grouper= LoaderGroupsLike", "penn:community:employee:center:%_center_systemOfRecord"= ); true gsh 15% setGroupAttr("penn:community:employee:centerRollupConfig", "grouper= LoaderGroupTypes", "addIncludeExclude"); true gsh 18% centerGroup =3D GroupFinder.findByName(grouperSession, "penn:commun= ity:employee:centerRollupConfig"); group: name=3D'penn:community:employee:centerRollupConfig' displayName=3D'p= enn:community:employee:centerRollupConfig' uuid=3D'8b329babf720413d81f5004f= b3729c02' 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_E= XTENSION LIKE_STRING_UPPER 96XX_consultants 96XX
* Lets make the meta view that describes the groups managed by this load= er
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 || '_sp= onsororg:' || osg.GROUP_EXTENSION || '_sponsororg_systemOfRecord' as group_= name, 'penn:community:employee:sponsororg:' || osg.GROUP_EXTENSION || '_sponsoror= g:' || osg.GROUP_EXTENSION || '_sponsororg system of record' as group_displ= ay_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 || '_sponsoror= g:' || 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_N= AME GROUP_DISPLAY_NAME GROUP_DESCRIPTION READERS VIEWERS GROUP_OVERALL_NAME= GROUP_EXTENSION penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consult= ants_sponsororg_systemOfRecord penn:community:employee:sponsororg:96XX_cons= ultants_sponsororg:96XX_consultants_sponsororg system of record people in p= enn community with a substring of 96XX in their sponsor_org field somewhere= penn:community:employee:orgSecurity:orgReaders penn:community:employee:org= Security:orgViewers penn:community:employee:sponsororg:96XX_consultants_spo= nsororg: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 =3D 'A' and upper(sav.v_sponsor_org) like '%' || osg.LIKE_STRING_UPPER || '%' and osmv.GROUP_EXTENSION =3D osg.GROUP_EXTENSION)
* The data from that view looks like this
GROUP_N= AME SUBJECT_ID penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consult= ants_sponsororg_systemOfRecord 10128438 penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consult= ants_sponsororg_systemOfRecord 68214103 penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consult= ants_sponsororg_systemOfRecord 10135159 penn:community:employee:sponsororg:96XX_consultants_sponsororg:96XX_consult= ants_sponsororg_systemOfRecord 10114304
* Create the config group, and run the loader
gsh 4% = sponsorGroup =3D addGroup("penn:community:employee", "orgSponsorConfig", "o= rgSponsorConfig"); group: name=3D'penn:community:employee:orgSponsorConfig' displayName=3D'pen= n:community:employee:orgSponsorConfig' uuid=3D'8b329babf720413d81f5004fb372= 9c02' gsh 6% groupAddType("penn:community:employee:orgSponsorConfig", "grouperLoa= der"); true gsh 7% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoa= derDbName", "grouper"); true gsh 8% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoa= derQuartzCron", "0 16 7 * * ? "); true gsh 9% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLoa= derQuery", "select GROUP_NAME, SUBJECT_ID from ORG_SPONSOR_LIST_V"); true gsh 10% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLo= aderScheduleType", "CRON"); true gsh 12% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLo= aderType", "SQL_GROUP_LIST"); true gsh 13% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLo= aderGroupQuery", "select group_name, group_display_name, group_description,= readers, viewers from org_sponsor_meta_v"); true gsh 14% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLo= aderGroupsLike", "penn:community:employee:sponsororg:%_sponsororg_systemOfR= ecord"); true gsh 15% setGroupAttr("penn:community:employee:orgSponsorConfig", "grouperLo= aderGroupTypes", "addIncludeExclude"); true gsh 18% sponsorGroup =3D GroupFinder.findByName(grouperSession, "penn:commu= nity:employee:orgSponsorConfig"); group: name=3D'penn:community:employee:orgSponsorConfig' displayName=3D'pen= n:community:employee:orgSponsorConfig' uuid=3D'8b329babf720413d81f5004fb372= 9c02' 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 ne= ed to add the contractors. I will add this in the org structure, so p= eople 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 unles= s a patch is applied that Penn developed which is not yet public)
<Dir= ectory "[directory]"> CosignProtected on AuthType Cosign CosignRequireFactor UPENN.EDU AuthzLDAPAuthoritative on AuthLDAPCompareDNOnServer on AuthLDAPBindPassword [password] AuthLDAPBindDN uid=3D[service principal],ou=3Dentities,dc=3Dupenn,dc=3Dedu AuthLDAPLimitAttribute cn # custom attribute via local patch AuthLDAPURL ldaps://url.ldap.private/cn=3Dpenn:community:employee:org:96XX:96XX_andCons= ultants,ou=3Dgroups,dc=3Dupenn,dc=3Dedu?hasMember require ldap-dn cn=3Dpenn:community:employee:96XX:96XX_andConsultants,ou=3D= groups,dc=3Dupenn,dc=3Dedu </Directory>