Date: Thu, 28 Mar 2024 11:25:43 +0000 (UTC) Message-ID: <688299805.6241.1711625143564@ip-10-10-7-29.ec2.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_6240_768575030.1711625143563" ------=_Part_6240_768575030.1711625143563 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
This is Penn's experience implementing the Grouper organization hierarch= y. Note, we used to put the fully qualified org name in the group nam= e, but now we have the group name as the org extension without the ancestor= path.
This is the size of our org implementation:
Performance:
Here are the steps to creating a loader SQL_GROUP_LIST job:
CREATE = OR REPLACE PACKAGE AUTHZADM.authzadm_pkg AS FUNCTION remove_special_chars (the_input varchar2) RETURN varchar2; END authzadm_pkg; / CREATE OR REPLACE PACKAGE BODY AUTHZADM.authzadm_pkg AS FUNCTION remove_special_chars (the_input varchar2) RETURN varchar2 AS the_string varchar2(4000); BEGIN --take out anything not alphanumeric, space, underscore, or dash the_string :=3D REGEXP_REPLACE(the_input, '[Penn organizational hierar= chy - part 1^a-zA-Z0-9 _\-]', ''); the_string :=3D trim(the_string); return the_string; END; END authzadm_pkg; /
* Implement the view of orgs. Note, in the view you can easily use= unions in the sql to end the hierarchy at a different node. At first= we were going to do this, then we decided against it. However, you w= ill see that we do filter out certain branches and nodes. Also note w= e shorten the names of some top level nodes.
CREATE = OR REPLACE VIEW ORG_LIST_V (ORG_NAME, ORG_DISPLAY_NAME, ORG_DESCRIPTION, PARENT_ID, PAYROLL_FLAG, CENTER_CODE, CENTER_NAME, center_code_assign) AS select trim(organization_code) org_name, decode(organization_code, 'UNIV', 'Penn', 'NOTU', 'Not Acad', 'TOPU', 'Top', authzadm_pkg.remove_special_chars(nvl(oc.description, oc.ORG_SHORT_NAME= ))) org_display_name, authzadm_pkg.remove_special_chars(nvl(oc.description, oc.ORG_SHORT_NAME)) o= rg_description, trim(parent_org_code) parent_id, oc.PAYROLL_FLAG, authzadm_pkg.remove_special_chars(oc.CENTER_CODE) center_code, authzadm_pkg= .remove_special_chars(oc.CENTER_NAME) center_name, /* if the parent is in the same center, dont list it, only list it if the p= arent is in a different center */ (select authzadm_pkg.remove_special_chars(oc.CENTER_CODE) from diradmin.dir= _org_codes oc2 where oc.PARENT_ORG_CODE =3D oc2.ORGANIZATION_CODE and oc.CENTER_CODE !=3D = oc2.CENTER_CODE ) as center_code_assign from diradmin.dir_org_codes oc where oc.ENABLED =3D 'Y' and oc.organization_code not in ( 'DEAD', 'BUD5', 'RADA', 'T', 'CAOP') and oc.PARENT_ORG_CODE not in ( 'DEAD', 'BUD5', 'RADA', 'T', 'CAOP') and oc.description is not null
ORG_NAM= E ORG_DISPLAY_NAME ORG_DESCRIPTION = PARENT_ID PAYROLL_FLAG CENTER_CODE CENTER_NAME 78YY ACP Other Parent ACP Other Parent = 78XX N 78 Audit Compliance and Privacy 9985 AG-Center for School Study Councils AG-Center for School Study = Councils AG32 N 99 External Organizations (Agency= Funds) 4602 AG-Institute on Aging AG-Institute on Aging = IAGE Y 40 School of Medicine IAGE AG-Institute on Aging Parent AG-Institute on Aging Paren= t SOMI N 40 School of Medicine
CREATE = OR REPLACE VIEW ORG_ASSIGN_V (ORG_CODE, PENN_ID) AS Select distinct p.job_org_code org_code, a.v_penn_id penn_id from comadmin.ssn4_affiliation_view a, comadmin.pennpay_appointment_v p Where a.v_penn_id =3D p.penn_id And a.v_source =3D 'PENNPAY' and a.V_ACTIVE= _CODE =3D 'A' and p.JOB_ACTIVE_CODE =3D 'A'
ORG_COD= E PENN_ID 0007 12345678 8105 12345679
CREATE = OR REPLACE FORCE VIEW ORG_LOADER_PERSON_META_V ( GROUP_NAME, GROUP_DISPLAY_NAME, READERS, VIEWERS, ORG_ID ) AS SELECT DISTINCT 'penn:community:empl= oyee:org:' || OLV.ORG_NAME || ':' || OLV.ORG_NAME || '_personorg' AS group_name, 'penn:community:empl= oyee:org:' || OLV.ORG_NAME || ' - ' || olv.ORG_DISPLAY_NAME || ':' || OLV.ORG_NAME || ' - ' || olv.ORG_DISPLAY_NAME AS group_display_nam= e, 'penn:community:employee:orgSecur= ity:orgReaders' AS readers, 'penn:community:employee:orgSecur= ity:orgViewers' AS viewers, OLV.ORG_NAME AS org_id FROM org_list_v olv WHERE olv.PAYROLL_FLAG =3D 'Y' ORDER BY 2;
* This data looks like this
penn:co= mmunity:employee:org:0001:0001_personorg penn:community:employee:org:0001 -= General University:0001 - General University = penn:community:employee:orgSecurity:orgReaders penn:comm= unity:employee:orgSecurity:orgViewers 0001 penn:community:employee:org:0007:0007_personorg penn:community:employee:org= :0007 - General University EB Pool:0007 - General University EB Pool = penn:community:employee:orgSecurity:orgReaders pe= nn:community:employee:orgSecurity:orgViewers 0007 penn:community:employee:org:0030:0030_personorg penn:community:employee:org= :0030 - Learning Alliance for Higher Education LLC:0030 - Learning Alliance= for Higher Education LLC penn:community:employee:orgSecurity:orgReaders pe= nn:community:employee:orgSecurity:orgViewers 0030 penn:community:employee:org:0101:0101_personorg penn:community:employee:org= :0101 - Anthropology:0101 - Anthropology = penn:community:employee:orgSecurity:orgReaders pe= nn:community:employee:orgSecurity:orgViewers 0101 penn:community:employee:org:0102:0102_personorg penn:community:employee:org= :0102 - Asian and Middle Eastern Studies:0102 - Asian and Middle Eastern St= udies penn:community:employee:orgSecurity:orgReaders pe= nn:community:employee:orgSecurity:orgViewers 0102
* Penn has two types of orgs: orgs that hold people, and orgs that dont = (they hold other orgs). So I will create them separately, here is a v= iew of orgs that hold people, that the loader will use. Note: these w= ill not be include/exclude since we only need that on the higher level grou= ps (rollups). Note, each group here should end in _personorg so we ca= n know which groups are managed by this loader process.
/* Form= atted on 3/10/2013 12:47:28 PM (QP5 v5.163.1008.3004) */ CREATE OR REPLACE FORCE VIEW ORG_LOADER_PERSON_V ( GROUP_NAME, SUBJECT_ID ) AS SELECT DISTINCT olpmv.GROUP_NAME, oav.PENN_ID AS subject_id FROM ORG_LOADER_PERSON_META_V olpmv, org_assign_v oav WHERE olpmv.org_id =3D oav.ORG_CODE; COMMENT ON TABLE ORG_LOADER_PERSON_V IS 'view which the loader uses to load= people to payroll orgs';
[appadm= in@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% GSH_DEBUG=3Dtrue true gsh 1% grouperSession =3D GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 9702ff7015a84c019ae58ce6ae= 950115,'GrouperSystem','application' gsh 2% stem =3D StemFinder.findByName(grouperSession, "penn:community:emplo= yee"); stem: name=3D'penn:community:employee' displayName=3D'penn:community:employ= ee' uuid=3D'3cb63130-03e1-4b60-8f01-1454ee3c9588' gsh 4% group =3D addGroup("penn:community:employee", "orgConfig", "orgConfi= g"); group: name=3D'penn:community:employee:orgConfig' displayName=3D'penn:commu= nity:employee:orgConfig' uuid=3D'f36a72d38053405d997ee8fc6eb66ff4' gsh 5% groupAddType("penn:community:employee:orgConfig", "grouperLoader"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderDbNa= me", "grouper"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuar= tzCron", "0 46 6 * * ? "); true gsh 8% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuer= y", "select group_name, subject_id from org_loader_person_v where 1=3D0"); true gsh 9% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderSche= duleType", "CRON"); true gsh 10% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderTyp= e", "SQL_GROUP_LIST"); true
[appadm= in@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% grouperSession =3D GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 6e1432f7de314aeca2f927f939= f1a5be,'GrouperSystem','application' gsh 1% group =3D GroupFinder.findByName(grouperSession, "penn:community:emp= loyee:orgConfig"); group: name=3D'penn:community:employee:orgConfig' displayName=3D'penn:commu= nity:employee:orgConfig' uuid=3D'f36a72d38053405d997ee8fc6eb66ff4' gsh 2% loaderRunOneJob(group); loader ran successfully, inserted 0 memberships, deleted 0 memberships, tot= al membership count: 0
GROUP_N= AME SU= BJECT_ID penn:community:employee:org:TOPU:UNIV:USCH:51XX:DPDN:5188:5188_personorg = 12345678 penn:community:employee:org:TOPU:UNIV:USTU:85XX:CRSC:ASPP:8508:8508_persono= rg 12345679 penn:community:employee:org:TOPU:UNIV:USCH:36XX:APPC:3604:3604_personorg = 12345680 penn:community:employee:org:TOPU:UNIV:USCH:02XX:GRAD:GRAO:0315:0315_persono= rg 12345681
gsh 5% = setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuery", "se= lect group_name, subject_id from org_loader_person_v"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGrou= pQuery", "select olpmv.GROUP_NAME as group_name, olpmv.GROUP_DISPLAY_NAME a= s group_display_name, olpmv.READERS, olpmv.VIEWERS, olpmv.ORG_ID from ORG_L= OADER_PERSON_META_V olpmv"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGrou= psLike", "penn:community:employee:org:%_personorg"); true gsh 8% loaderRunOneJob(group);