Simple DB table of username-group assocation

Owned by James Wennmacher
Oct 14, 2014
4 min read

If you have simple needs for a database table associating a username with one or more group names and

  • the group names do not change frequently
  • you don't have need of group hierarchies (or can use local groups to provide the group hierarchies)
  • You don't need to answer the question 'Who are the members of group X' in uPortal (you can of course get that by querying the database table) because PAGS doesn't support answering that question
  • You don't need hierarchies of groups (or could include groups into uPortal's local groups to accomplish your needs)

you can use the approach of

  • Modify personDirectoryContext.xml to query a database table to create a custom person attribute with a set of one or more group names
  • Define a PAGS group based on the above attribute matching the predefined group name values.

For example, assume you have a table

create table MY_GROUPS (
   username varchar(255),
   groupName varchar(255)
);
insert into MY_GROUPS values
   ('faculty', 'myTestGroup'),
   ('student','myTestGroup'),
   ('student','myOtherGroup');

Step 1: personDirectoryContext.xml

You can create a new database-backed attribute source such as

uportal-war/src/main/resources/properties/context/personDirectoryContext.xml
 ...
<bean id="mergedPersonAttributeDao"
   <property name="personAttributeDaos">
       <list>
...
           <!-- ADDITIONAL ATTRIBUTE SOURCES GET ADDED HERE
           Don't add more sources to the CascadingPersonAttributeDao.personAttributeDaos
           list above unless you're adding "special" DAOs tasked with augmenting/transforming
           the attributes generated by uP-local DAOs. (CascadingPersonAttributeDao takes
           the results from the first-DAO, transforms them into a query, and passes that
           query to each subsequent DAO. I.e. subsequent DAOs in the cascade list will
           *not* be given a chance to process the original query. But they will if you add
           them directly to the MergingPersonAttributeDaoImpl.personAttributeDaos list here.)
           -->
           <ref bean="myJdbcGroupSource"/>
       </list>
   </property>
...
</bean>

<!--
 | Looks in the MY_GROUP table for user-groupname associations and adds to myGroups attribute.  A user can be
 | associated to multiple groups. Results are cached by the outer caching DAO.  Will need to modify ehcache.xml
 | to add a cache name of 'org.jasig.services.persondir.USER_INFO.myJdbcGroupSource'.
 +-->
<bean id="myJdbcGroupSource"
      class="org.jasig.services.persondir.support.CachingPersonAttributeDaoImpl">
    <property name="usernameAttributeProvider" ref="usernameAttributeProvider" />
    <property name="cacheNullResults" value="true" />
    <property name="userInfoCache">
        <bean class="org.jasig.portal.utils.cache.MapCacheFactoryBean">
            <property name="cacheFactory" ref="cacheFactory" />
            <property name="cacheName" value="org.jasig.services.persondir.USER_INFO.myJdbcGroupSource" />
        </bean>
    </property>
    <property name="cacheKeyGenerator" ref="userAttributeCacheKeyGenerator" />
    <property name="cachedPersonAttributesDao" >
        <bean class="org.jasig.services.persondir.support.jdbc.NamedParameterJdbcPersonAttributeDao">
            <property name="dataSource" ref="PersonDB" />
            <property name="sql">
                <value>
                    SELECT groupName AS myDBGroup
                    from MY_GROUPS
                    where UPPER(username) = UPPER(:username)
                </value>
            </property>
            <property name="usernameAttributeProvider" ref="usernameAttributeProvider" />
            <property name="userAttributeNames">
                <set>
                    <value>myDBGroup</value>  <!-- Attribute name added to user -->
                </set>
            </property>
        </bean>
    </property>
</bean>

Step 2: Add PAGS Groups

Add XML-backed PAGS or DB entity-backed PAGS groups to make use of the attribute defined above.  See Person Attribute Group Store (PAGS) Overview.  The DB entity-backed PAGS group is more dynamic and therefore recommended because you can add a new group name to the database and import a new PAGS group without redeploying uPortal as is required for the XML-based PAGS configuration.

For a entity-based PAGS configuration for example, add group for each group name; e.g.

entity-based PAGS group: uportal-war/src/main/data/default_entities/pags/myTestGroup.pags-group.xml
<pags-group script="classpath://org/jasig/portal/io/import-pags-group_v4-1.crn">
  <name>My Test Group</name>
  <description>myTestGroup defined in MY_GROUPS table</description>
  <selection-test>
    <test-group>
      <test>
        <attribute-name>myDBGroup</attribute-name>
        <tester-class>org.jasig.portal.groups.pags.testers.RegexTester</tester-class>
        <test-value>myTestGroup</test-value>
      </test>
    </test-group>
  </selection-test>
</pags-group>
 
<!-- Also add My Test Group to PAGS_Root.pags-group.xml -->

Also see Configuring Person Attribute Group Store (PAGS)