Grouper Users - Open Discussion List

["Guenther, Dean R." <>]

I’ve been having trouble setting up subjects to be mapped from my PersonRegistry, which resides on the same Oracle DB as Grouper. When I setup a new loader task to create an employee group called “hourly” it runs and finds all 7461 people who are to belong in the group. But then it gives an error that each person’s subject is not found. I’ve checked the archives and Grouper Deployement Guide and most references I found about this error refer to a problem with the sources.xml or subject.properties. But even after getting those setup the way I think they should be, it still doesn’t find a subject for these people.

 

Here is what I’m seeing in the grouperUi for building this group “hourly”

 

######## CONFIGURATION ########

SUCCESS: grouperLoaderType is: SQL_SIMPLE

SUCCESS: This is a SQL job

SUCCESS: grouperLoaderType SQL_SIMPLE is a SQL type

SUCCESS: SQL database name is 'grouper' which uses the Grouper database connection: jdbc:oracle:thin:@localhost.wsu.edu:1521:MIM

NOTE: SQL query is set to 'select EXTERNALSYSTEMID as  subject_id from grouperpersonregistry where employeerole like '%Hourly%' '

SUCCESS: 'and groups' is not set

SUCCESS: Schedule type correctly set to: CRON

SUCCESS: Cron '0 42 15 * * ?' is set to: 'At 3:42 PM'

SUCCESS: Schedule interval is not set and schedule type is CRON

SUCCESS: SQL group query is not set!

SUCCESS: 'groups like' SQL config is not set

SUCCESS: Group types are not set

SUCCESS: Scheduling priority is a valid integer: 5 

######## CHECKING QUERIES ########

SUCCESS: Found DB profile for: 'grouper'

SUCCESS: Ran query, got 7461 results in 323ms

SUCCESS: Found SUBJECT_ID col

WARNING: Did not find col: SUBJECT_SOURCE_ID, this column improves performance

ERROR: Could not find subject by id ‘9809109029’!

edu.internet2.middleware.subject.SubjectNotFoundException: subject not found: 9809109029

 

Here is what I see from gsh:

 

groovy:000> subj=SubjectFinder.findByIdOrIdentifier("9809109029",false)

===> Subject id: 9809109029, sourceId: GrouperPersonRegistry, name: Something, Zack Matthew

groovy:000> subj.getAttributes()

===> [loginid:[zack.something]]

 

 

This is what I have in the grouper-loader.properties:

 

db.grouperpersonregistry.url = "jdbc:oracle:thin:@localhost.wsu.edu:1521:MIM

default.subject.source.id = grouperpersonregistry

 

This is what I have in the sources.xml (trimming some config lines to make it shorter)

 

    <source adapterClass="edu.internet2.middleware.grouper.subj.GrouperJdbcSourceAdapter2">

    <id>GrouperPersonRegistry</id>

    <name>Grouper Person Registry: JDBC Source Adapter</name>

     <type>person</type>

     <init-param>

        <param-name>jdbcConnectionProvider</param-name>

       <param-value>edu.internet2.middleware.grouper.subj.GrouperJdbcConnectionProvider</param-value>

     </init-param>

…

      <init-param>

       <param-name>dbTableOrView</param-name>

       <param-value>GrouperPersonRegistry</param-value>

     </init-param>

      <init-param>

       <param-name>subjectIdCol</param-name> <!-- immutable ID which can never be changed -->

       <param-value>EXTERNALSYSTEMID</param-value>

     </init-param>

… skipping nameCol, descriptionCol, lowerSearchCol,     

     <init-param>

       <param-name>subjectIdentifierCol0</param-name>

       <param-value>LOGINID</param-value>

     </init-param>

     <init-param>

       <param-name>subjectAttributeCol0</param-name>

       <param-value>LOGINID</param-value>

     </init-param>

     <init-param>

       <param-name>subjectAttributeName0</param-name>

       <param-value>LOGINID</param-value>

     </init-param>

     <init-param>

       <param-name>sortAttribute0</param-name>

       <param-value>LOGINID</param-value>

     </init-param>

     <init-param>

       <param-name>searchAttribute0</param-name>

       <param-value>LOGINID</param-value>

     </init-param>

   </source>

 

 

This is from subject.properties (trimmed version):

 

subjectApi.source.someConfigId.id = GrouperPersonRegistry

subjectApi.source.jdbc.id = GrouperPersonRegistry

subjectApi.source.jdbc.name = Grouper Person Registry

subjectApi.source.jdbc.types = person

subjectApi.source.jdbc.adapterClass = edu.internet2.middleware.grouper.subj.GrouperJdbcSourceAdapter2

 subjectApi.source.jdbc.param.jdbcConnectionProvider.value = edu.internet2.middleware.grouper.subj.GrouperJdbcConnectionProvider

 subjectApi.source.jdbc.param.emailAttributeName.value = USERPRINCIPALNAME

… 

subjectApi.source.jdbc.param.dbDriver.value = oracle.jdbc.driver.OracleDriver

subjectApi.source.jdbc.param.dbUrl.value = jdbc:oracle:thin:@localhost.wsu.edu:1521:MIM

subjectApi.source.jdbc.param.SubjectID_AttributeType.value = EXTERNALSYSTEMID

subjectApi.source.jdbc.param.Name_AttributeType.value = DISPLAYNAME

subjectApi.source.jdbc.param.Description_AttributeType.value = DESCRIPTION

subjectApi.source.jdbc.param.subjectVirtualAttribute_0_searchAttribute0.value = ${subject.displayname},${subjectUtils.defaultIfBlank(subject.getAttributeValue('LASTNAME'), "")},${subjectUtils.defaultIfBlank(subject.getAttributeValue('LOGINID'), "")},${subjectUtils.defaultIfBlank(subject.description, "")},${subjectUtils.defaultIfBlank(subject.getAttributeValue('USERPRINCIPALNAME'), "")}

subjectApi.source.jdbc.param.sortAttribute0.value = LASTNAME

subjectApi.source.jdbc.param.sortAttribute1.value = LOGINID

subjectApi.source.jdbc.param.searchAttribute0.value = searchAttribute0 

subjectApi.source.jdbc.param.useInClauseForIdAndIdentifier.value = true

subjectApi.source.jdbc.param.identifierAttributes.value = LOGINID

subjectApi.source.jdbc.param.subjectIdentifierAttribute0.value = LOGINID

subjectApi.source.jdbc.internalAttributes = searchAttribute0

 

 

So my question is, what am I missing in my subject.properties so that the loader can find the subjects for each person it finds?

thanks – dean

 

 

 

 

Dean Guenther                          
Washington State University    Phone:    509 335-0433
Pullman, WA. 99164-1222        fax:      509 335-0540
Identity and Access Management Manager