Include Page | ||||
---|---|---|---|---|
|
This is Penn's experience implementing the Grouper organization hierarchy.
...
Code Block |
---|
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)) org_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 parent 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 = oc2.ORGANIZATION_CODE and oc.CENTER_CODE != oc2.CENTER_CODE ) as center_code_assign from diradmin.dir_org_codes oc where oc.ENABLED = '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 |
This shows the following data (2200 rows)
Code Block ORG_NAME 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 Parent SOMI N 40 School of Medicine
- Here is the view which assigns people to orgs. This view makes sure the person has at least one active job in that org (doesnt have to be the primary job)
...
Code Block |
---|
[appadmin@lorenzo bin]$ ./gsh.sh -registry -check Using GROUPER_HOME: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/bin/.. Using GROUPER_CONF: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/bin/../classes Using JAVA: /opt/appserv/java5/bin/java using MEMORY: 64m-512m Grouper starting up: version: 1.4.2, build date: 2009/05/19 16:13:03, env: PROD grouper.properties read from: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/grouper.properties Grouper current directory is: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/bin log4j.properties read from: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/log4j.properties Grouper is logging to file: /opt/appserv/tomcat_3c/logs/fastGrouper/grouper_error.log, at min level WARN for package: edu.internet2.middleware.grouper, based on log4j.properties grouper.hibernate.properties: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/grouper.hibernate.properties grouper.hibernate.properties: schema@jdbc:oracle:thin:@dbserver.whatever.whatever:1521:sid sources.xml read from: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/sources.xml sources.xml jdbc source id: pennperson: GrouperJdbcConnectionProvider sources.xml groupersource id: g:gsa sources.xml jdbc source id: servPrinc: GrouperJdbcConnectionProvider (note, might need to type in your response multiple times (Java stdin is flaky)) (note, you can whitelistallow orand blacklistdeny db urls and users in the grouper.properties) Are you sure you want to schemaexport all tables (dropThenCreate=F,writeAndRunScript=F) in db user 'schema', db url 'jdbc:oracle:thin:@dbserver.whatever.whatever:1521:sid'? (y|n): y Continuing... Grouper ddl object type 'GrouperOrg' has dbVersion: 0 and java version: 1 Grouper database schema DDL requires updates (should run script manually and carefully, in sections, verify data before drop statements, backup/export important data before starting, follow change log on confluence, dont run exact same script in multiple envs - generate a new one for each env), script file is: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/ddlScripts/grouperDdl_20090519_16_44_25_124.sql Note: this script was not executed due to option passed in To run script via gsh, carefully review it, then run this: gsh -registry -runsqlfile /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/ddlScripts/grouperDdl_20090519_16_44_25_124.sql |
...
Code Block |
---|
CREATE OR REPLACE 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 = oav.ORG_CODE |
Add the config group. Note, there are no org members yet (1=0), so I can inspect the grouperorgs_hierarchical table
Code Block [appadmin@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% GSH_DEBUG=true true gsh 1% grouperSession = GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 9702ff7015a84c019ae58ce6ae950115,'GrouperSystem','application' gsh 2% stem = StemFinder.findByName(grouperSession, "penn:community:employee"); stem: name='penn:community:employee' displayName='penn:community:employee' uuid='3cb63130-03e1-4b60-8f01-1454ee3c9588' gsh 4% group = addGroup("penn:community:employee", "orgConfig", "orgConfig"); group: name='penn:community:employee:orgConfig' displayName='penn:community:employee:orgConfig' uuid='f36a72d38053405d997ee8fc6eb66ff4' gsh 5% groupAddType("penn:community:employee:orgConfig", "grouperLoader"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderDbName", "grouper"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuartzCron", "0 46 6 * * ? "); true gsh 8% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuery", "select group_name, subject_id from org_loader_person_v where 1=0"); true gsh 9% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderScheduleType", "CRON"); true gsh 10% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderType", "SQL_GROUP_LIST"); true
* Run the job
Code Block [appadmin@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% grouperSession = GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 6e1432f7de314aeca2f927f939f1a5be,'GrouperSystem','application' gsh 1% group = GroupFinder.findByName(grouperSession, "penn:community:employee:orgConfig"); group: name='penn:community:employee:orgConfig' displayName='penn:community:employee:orgConfig' uuid='f36a72d38053405d997ee8fc6eb66ff4' gsh 2% loaderRunOneJob(group); loader ran successfully, inserted 0 memberships, deleted 0 memberships, total membership count: 0
* Inspect the grouperorgs_hierarchy table. Note, there were some problems, so we adding the function to strip bad chars and trim the data... also adjust the org_loader_person_v (so the names and everything are ok). Here is what the org_loader_person_v looks like (person data scrubbed)
Code Block GROUP_NAME SUBJECT_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_personorg 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_personorg 12345681
* Add the group query, and fix the member query (take out 1=0)
Code Block gsh 5% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuery", "select group_name, subject_id from org_loader_person_v"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGroupQuery", "select olpmv.GROUP_NAME as group_name, olpmv.GROUP_DISPLAY_NAME as group_display_name, olpmv.READERS, olpmv.VIEWERS, olpmv.ORG_ID from ORG_LOADER_PERSON_META_V olpmv"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGroupsLike", "penn:community:employee:org:%_personorg"); true gsh 8% loaderRunOneJob(group);
- This created 736 org groups with 33k members
Rollup orgs
Now we need a meta view which has information about the rollup group: note the top two levels are filtered out
Code Block CREATE OR REPLACE 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 gh.ORG_HIERARCHICAL_STEM || ':' || gh.ORG_ID || '_rolluporg_systemOfRecord' as group_name, gh.ORG_HIERARCHICAL_STEM || ' - ' || olv.ORG_DISPLAY_NAME || ':' || gh.ORG_ID || ' - ' || olv.ORG_DISPLAY_NAME || ' system of record' as group_display_name, gh.ORG_HIER_ALL_SOR_DESCRIPTION as group_description, 'penn:community:employee:orgSecurity:orgReaders' as readers, 'penn:community:employee:orgSecurity:orgViewers' as viewers, gh.org_id as org_id, gh.ORG_HIERARCHICAL_STEM || ':' || gh.ORG_ID || '_rolluporg' as group_overall_name, olv.PARENT_ID from grouperorgs_hierarchical gh, org_list_v olv where gh.ORG_ID = olv.ORG_NAME and gh.ORG_HIER_ALL_NAME is not null and olv.PAYROLL_FLAG = 'N' and olv.ORG_NAME not in ('TOPU', 'UNIV', 'NOTU') order by 2
* This has data which looks like this:
Code Block GROUP_NAME GROUP_DISPLAY_NAME GROUP_DESCRIPTION READERS VIEWERS ORG_ID GROUP_OVERALL_NAME penn:community:employee:org:TOPU:NOTU:HCAG:HCAG_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG - Health Care and Agencies:HCAG - Health Care and Agencies system of record Members of HCAG and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers HCAG penn:community:employee:org:TOPU:NOTU:HCAG:HCAG_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:21XX:21XX_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:21XX - University of Pennsylvania Health System:21XX - University of Pennsylvania Health System system of record Members of 21XX and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 21XX penn:community:employee:org:TOPU:NOTU:HCAG:21XX:21XX_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX - External Organizations Parent:99XX - External Organizations Parent system of record Members of 99XX and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 99XX penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02 - Agencies for SAS:AG02 - Agencies for SAS system of record Members of AG02 and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers AG02 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:AG04_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04 - Agencies for Provost Interdisciplinary Center:AG04 - Agencies for Provost Interdisciplinary Center system of record Members of AG04 and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers AG04 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:AG04 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:AG06_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06 - Agencies for School of Nursing:AG06 - Agencies for School of Nursing system of record Members of AG06 and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers AG06 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:AG06
Now the rollups, make a view which assigns the rollup. Note this is based on the meta view, so it only includes groups in the meta view
Code Block CREATE OR REPLACE VIEW ORG_LOADER_ROLLUP_V (GROUP_NAME, MEMBER_GROUP_NAME) 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 , gh_member.ORG_HIERARCHICAL_STEM || ':' || gh_member.ORG_ID || '_personorg' as subject_identifier from grouperorgs_hierarchical gh_member, ORG_LOADER_ROLLUP_META_V rollup_parent, org_list_v olv_child where olv_child.PARENT_ID = rollup_parent.org_id and olv_child.ORG_NAME = gh_member.org_id and olv_child.PAYROLL_FLAG = 'Y' )
* The data for this view looks like this
Code Block GROUP_NAME MEMBER_GROUP_NAME penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:9938:9938_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:9979:9979_personorg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:AG04_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:9992:9992_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:AG06_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:9907:9907_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9902:9902_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9911:9911_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9912:9912_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9913:9913_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9914:9914_rolluporg
Use the rollup view, join to group id's
Code Block CREATE OR REPLACE VIEW ORG_LOADER_ROLLUP2_V (GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID, subject_group_name) AS select olrv.GROUP_NAME group_name, ga.GROUP_ID as subject_id, 'g:gsa' as SUBJECT_SOURCE_ID, olrv.MEMBER_GROUP_NAME subject_group_name from org_loader_rollup_v olrv, grouper_attributes ga, grouper_fields gf where gf.NAME = 'name' AND gf.ID = ga.field_id and ga.VALUE = olrv.MEMBER_GROUP_NAME
* The data from this view looks like
Code Block 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 penn:community:employee:org:TOPU:UNIV:UADM:90XX:90XX_rolluporg_systemOfRecord 68e1396ccda643aa8357926de4a0f700 g:gsa penn:community:employee:org:TOPU:UNIV:UADM:90XX:ALUM:ALUM_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg_systemOfRecord 5a13844e363c4e6fbbc95015969b81c1 g:gsa penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG24:AG24_rolluporg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg_systemOfRecord 3ad2e77634c3426c8a2e6e4777f7a350 g:gsa penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG26:AG26_rolluporg penn:community:employee:org:TOPU:TOPU_rolluporg_systemOfRecord 25d090972daa47b690b30eb8a9220de5 g:gsa penn:community:employee:org:TOPU:UNIV:UNIV_rolluporg
* Create the config group for the rollups, and execute the loader job
Code Block 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);
- There are 1461 rollup orgs, 407k memberships. Here is a screen of the rollups orgs
...
- Add the VP to the includes list of the org. Note, this means that for all purposes, additions are considered part of the org, and part of the center (since the org is part of the center)
- Now make a group which has the org group, and the consultants
- Here are the two members
- Here are all members
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)
Code Block <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:TOPU:UNIV:UADM:96XX:96XX_andConsultants,ou=groups,dc=upenn,dc=edu?hasMember require ldap-dn cn=penn:community:employee:org:TOPU:UNIV:UADM:96XX:96XX_andConsultants,ou=groups,dc=upenn,dc=edu </Directory>