Grouper Loader - What is it?

A Loader is a task, or a job, that runs on a schedule that pulls users from a data source into Grouper memberships. There are several types of Loader that can be used to pull information from various sources. The types of loader (which correspond to their data source) are as follows: JEXL Script, SQL, LDAP, and Recent Membership. To see where Loaders fit into the architectural landscape please view the following diagram: architectural diagram.

Grouper Diagnostics.




Things to keep in mind with Loaders: 

Ad hoc groups with the Grouper Loader


Loader manages simple group or a list of groups in one job

One-time setup in your Grouper database

To make a dynamic (loadable) group, first you need the correct metadata in Grouper. The easiest way is to set the grouper-loader.properties key loader.autoadd.typesAttributes to true.  If you don't want to do that, then here is the setup in GSH:

subj=SubjectFinder.findById("GrouperSystem")
sess=GrouperSession.start(subj)
type=GroupType.createType(sess, "grouperLoader")
type.addAttribute(sess, "grouperLoaderType", false)
type.addAttribute(sess, "grouperLoaderDbName", false)
type.addAttribute(sess, "grouperLoaderScheduleType", false)
type.addAttribute(sess, "grouperLoaderQuery", false)
type.addAttribute(sess, "grouperLoaderQuartzCron", false)
type.addAttribute(sess, "grouperLoaderIntervalSeconds", false)
type.addAttribute(sess, "grouperLoaderPriority", false)
type.addAttribute(sess, "grouperLoaderAndGroups", false)
type.addAttribute(sess, "grouperLoaderGroupTypes", false)
type.addAttribute(sess, "grouperLoaderGroupsLike", false)
type.addAttribute(sess, "grouperLoaderGroupQuery", false)
type.addAttribute(sess, "grouperLoaderDisplayNameSyncType", false)
type.addAttribute(sess, "grouperLoaderDisplayNameSyncBaseFolderName", false)
type.addAttribute(sess, "grouperLoaderDisplayNameSyncLevels", false)

 Note that a loadable group has the type "grouperLoader", and there are some attributes that you can set about the group:

# specify the db connection with user, pass, url, and driver class
# the string after "db." is the name of the connection, and it should not have
# spaces or other special chars in it
db.warehouse.user = mylogin
db.warehouse.pass = secret
db.warehouse.url = jdbc:mysql://localhost:3306/grouper
db.warehouse.driver = com.mysql.jdbc.Driver

This defaults to CRON if there is a value for grouperLoaderQuartzCron (see below), else it defaults to START_TO_START_INTERVAL. Note that a job will not start if a previous run has not finished.

#if using a sql table, and specifying the name like string, then should the group (in addition to memberships)
# be removed if not used anywhere else?
loader.sqlTable.likeString.removeGroupIfNotUsed = true

Configure a loadable group (obviously any number of dynamic loadable groups can exist at once)

With GSH, it would look like this:

group=getGroups("aStem:aGroup2")
groupAddType("aStem:aGroup2", "grouperLoader")
setGroupAttr("aStem:aGroup2", "grouperLoaderDbName", "grouper")
setGroupAttr("aStem:aGroup2", "grouperLoaderType", "SQL_SIMPLE")
setGroupAttr("aStem:aGroup2", "grouperLoaderScheduleType", "START_TO_START_INTERVAL")
setGroupAttr("aStem:aGroup2", "grouperLoaderQuery", "select SUBJECT_ID, SUBJECT_SOURCE_ID from agroup2_v")
setGroupAttr("aStem:aGroup2", "grouperLoaderIntervalSeconds", "30")

You can also use the UI, here are screenshots (obviously these need some work).

Run grouper daemon

The first time you run, it will probably fail, and give you DDL in the logs to run in your database (to add a couple of tables).  Run the scripts and you should be all set.

Run with:

 GROUPER_HOME/bin/gsh.sh -loader

This will kick off as a command line program that you will want to run as a service.  This process will be always running, and the scheduler will schedule the jobs.  You should monitor the process with a monitoring tool like nagios or whatever you use at your institution so that you know when it is not up.

You can also run a one-timer via gsh.  This is useful to run once at the beginning, and not have to wait for the schedule.  Or to troubleshoot e.g.

loaderGroup = GroupFinder.findByName(GrouperSession.startRootSession(), "school:orgs:orgGroup");
loaderRunOneJob(loaderGroup);
loaderRunOneJob("MAINTENANCE_cleanLogs");


Note that as of Grouper 2.3, if you create a new loader job, you can have it scheduled without restarting the daemon by using an option in the New UI.  If you're an admin of the group, under "More actions", there's an option named "Schedule loader process".  This option can also be used if you change the loader's schedule.

Groups as members

If you are using the loader to load hierarchies where groups are members, then you need to have a query which has the column SUBJECT_IDENTIFIER with the group system name, or SUBJECT_ID with the group uuid.  Useful for org charts.  See this example

Logging of jobs in DB

Each job (and subjob if the job manages multiple things) will have an entry in the grouper_loader_log table.  This will show the following information.  This can be used to tune performance problems, see which jobs have unresolvable subjects, verify that jobs are running, etc.

COLUMN_NAME    				DATA_TYPE

ID    							VARCHAR2
JOB_NAME                                            VARCHAR2
STATUS                                               VARCHAR2
STARTED_TIME                                     TIMESTAMP(6)
ENDED_TIME    					   TIMESTAMP(6)
MILLIS    					       NUMBER
MILLIS_GET_DATA    				NUMBER
MILLIS_LOAD_DATA    			       NUMBER
JOB_TYPE    					     VARCHAR2
JOB_SCHEDULE_TYPE    			      VARCHAR2
JOB_DESCRIPTION    				VARCHAR2
JOB_MESSAGE    					  VARCHAR2
HOST    						VARCHAR2
GROUP_UUID    					  VARCHAR2
JOB_SCHEDULE_QUARTZ_CRON    		VARCHAR2
JOB_SCHEDULE_INTERVAL_SECONDS        NUMBER
JOB_SCHEDULE_PRIORITY    		   NUMBER
LAST_UPDATED    				 TIMESTAMP(6)
UNRESOLVABLE_SUBJECT_COUNT    	      NUMBER
INSERT_COUNT    				 NUMBER
UPDATE_COUNT    				NUMBER
DELETE_COUNT    				 NUMBER
TOTAL_COUNT    					 NUMBER
PARENT_JOB_NAME    				VARCHAR2
PARENT_JOB_ID    				  VARCHAR2

You can also look at log4j debug log messages, and info log messages (less frequent).  to see these, set log level in log4j.properties

## Log debug info on loader to see progress etc
log4j.logger.edu.internet2.middleware.grouper.app.loader = INFO
-or-
log4j.logger.edu.internet2.middleware.grouper.app.loader = DEBUG


Misc

Example test proving that only certain members can edit loader attributes

grouper.properties:

security.types.grouperLoader.wheelOnly = false

security.types.grouperLoader.allowOnlyGroup = etc:someAdminGroup

wheel configured:

groups.wheel.use                      = true

# Set to the name of the group you want to treat as the wheel group.
# The members of this group will be treated as root-like users.
groups.wheel.group                    = etc:sysadmingroup

create the security group in gsh

grouperSession = GrouperSession.startRootSession();

someSysAdminGroup = new GroupSave(grouperSession).assignName("etc:someAdminGroup").assignGroupNameToEdit("etc:someAdminGroup").save();

make sure subject not wheel

gsh 9% hasMember("etc:sysadmingroup", "test.subject.0");
false

add a group for that user to be an admin of (GSH)

someGroup = new GroupSave(grouperSession).assignName("a:b").assignGroupNameToEdit("a:b").assignCreateParentStemsIfNotExist(true).save();

grantPriv("a:b", "test.subject.0", AccessPrivilege.ADMIN);

auto add attributes in grouper-loader.properties

# auto-add grouper loader types and attributes when grouper starts up if they are not there
loader.autoadd.typesAttributes = true

start the loader to init the attributes (command line)

gsh -loader

login to UI as test.subject.0, Try to add grouperLoader type to group, and get this error:

Error: This operation is not allowed: Not allowed to edit type: grouperLoader, adding type since the user Subject id: test.subject.0, sourceId: jdbc is not in group: etc:someAdminGroup.

In the UI see that the type is not assigned.  Check the DB if you don't believe:

SELECT * FROM grouper_groups_types_v WHERE group_name = 'a:b'

Verify the SQL, when I look in grouperSql.log, I see these SQLs:

update grouper_groups set hibernate_version_number=1, parent_stem='088956f34e064116b68b97198ea422f7', creator_id='233cdc87a0654c03b37e59ea0bc7b52c', create_time=1273197358184, modifier_id='9221f2ae35d44d23b7bdb469e9e96278', modify_time=1273198909003, name='a:b', display_name='a:b', extension='b', display_extension='b', description=null, context_id='29523c8d2dac4ddd8294c40fadfd0f7f', alternate_name=null, type_of_group='group' where id='6e70a1c3a2d246669244051e44439374' and hibernate_version_number=0
2010/05/06 22:21:49:003, 0ms, statement: ByObjectStatic.java.saveOrUpdate() line 327, Hib3AuditEntryDAO.java.saveOrUpdate() line 26, AuditEntry.java.saveOrUpdate() line 307, Group.java.callback() line 3900, Group.java.store() line 3826, SaveGroupAction.java.grouperExecute() line 237, GrouperCapableAction.java.callback() line 217, Hib3TransactionDAO.java.callback() line 51, Hib3TransactionDAO.java.transactionCallback() line 41, GrouperCapableAction.java.grouperTransactionExecute() line 214, GrouperCapableAction.java.execute() line 279, LoginCheckFilter.java.callback() line 173, GrouperSession.java.callbackGrouperSession() line 644, LoginCheckFilter.java.doFilter() line 168, ErrorFilter.java.doFilter() line 132, GrouperUiFilter.java.doFilter() line 398
   insert into grouper_audit_entry (hibernate_version_number, act_as_member_id, audit_type_id, context_id, created_on, description, env_name, grouper_engine, grouper_version, int01, int02, int03, int04, int05, last_updated, logged_in_member_id, server_host, string01, string02, string03, string04, string05, string06, string07, string08, duration_microseconds, query_count, user_ip_address, server_user_name, id) values (0, null, 'd087478a3a334c41a104a9a0b47e2b3e', '29523c8d2dac4ddd8294c40fadfd0f7f', 1273198909003, 'Updated group: a:b, Fields changed: none', '', 'grouperUI', '1.5.3', null, null, null, null, null, 1273198909003, '9221f2ae35d44d23b7bdb469e9e96278', 'mchyzer-PC', '6e70a1c3a2d246669244051e44439374', 'a:b', '088956f34e064116b68b97198ea422f7', 'a:b', '', null, null, null, 4444, 1, '0:0:0:0:0:0:0:1', 'mchyzer', '691681cbbb7243caa3e4852ced79b3bc')
2010/05/06 22:21:49:003, 0ms, statement: ByObject.java.save() line 197, Hib3GroupDAO.java.callback() line 119, Hib3GroupDAO.java.addType() line 108, Group.java.callback() line 916, Group.java.addType() line 890, Group.java.addType() line 860, SaveGroupAction.java.doTypes() line 349, SaveGroupAction.java.grouperExecute() line 240, GrouperCapableAction.java.callback() line 217, Hib3TransactionDAO.java.callback() line 51, Hib3TransactionDAO.java.transactionCallback() line 41, GrouperCapableAction.java.grouperTransactionExecute() line 214, GrouperCapableAction.java.execute() line 279, LoginCheckFilter.java.callback() line 173, GrouperSession.java.callbackGrouperSession() line 644, LoginCheckFilter.java.doFilter() line 168, ErrorFilter.java.doFilter() line 132, GrouperUiFilter.java.doFilter() line 398
   insert into grouper_groups_types (hibernate_version_number, group_uuid, type_uuid, context_id, id) values (0, '6e70a1c3a2d246669244051e44439374', '57433a6dbdf14008a371ef18cd5c9c8d', 'b296ae3dd6b448d28c9ba2e643903087', '402881822870817d01287091964b0002')
GrouperLoaderArchitectureDiagram.tiff2010/05/06 22:22:08:489, 0ms, statement: ByHqlStatic.java.uniqueResult() line 297, Hib3GroupDAO.java.findByName() line 907, GroupFinder.java.findByName() line 225, GroupFinder.java.findByName() line 198, GroupTypeSecurityHook.java.vetoIfNecessary() line 194, GroupTypeSecurityHook.java.groupTypeTupleHelper() line 300, GroupTypeSecurityHook.java.groupTypeTuplePostInsert() line 289, GrouperUtil.java.invokeMethod() line 3422, GrouperHooksUtils.java.executeHook() line 476, GrouperHooksUtils.java.callHooksIfRegistered() line 276, GrouperHooksUtils.java.callHooksIfRegistered() line 215, GrouperHooksUtils.java.callHooksIfRegistered() line 141, GroupTypeTuple.java.onPostSave() line 265, Hib3GroupDAO.java.callback() line 119, Hib3GroupDAO.java.addType() line 108, Group.java.callback() line 916, Group.java.addType() line 890, Group.java.addType() line 860, SaveGroupAction.java.doTypes() line 349, SaveGroupAction.java.grouperExecute() line 240, GrouperCapableAction.java.callback() line 217, Hib3TransactionDAO.java.callback() line 51, Hib3TransactionDAO.java.transactionCallback() line 41, GrouperCapableAction.java.grouperTransactionExecute() line 214, GrouperCapableAction.java.execute() line 279, LoginCheckFilter.java.callback() line 173, GrouperSession.java.callbackGrouperSession() line 644, LoginCheckFilter.java.doFilter() line 168, ErrorFilter.java.doFilter() line 132, GrouperUiFilter.java.doFilter() line 398

The next commit or rollback in the file is a rollback (after it checks to see if the user is the member of the allowed group or wheel or GrouperSysadmin)

Unschedule a database quartz job

Note, you can look in the table grouper_qz_triggers and grouper_qz_job_details for more info

select * from grouper_loader_log where lower(job_name) like '%abc%' order by started_time desc;
select * from GROUPER_QZ_TRIGGERS where lower(trigger_name) like '%abc%';
select * from GROUPER_QZ_CRON_TRIGGERS where lower(trigger_name) like '%abc%';
select * from GROUPER_QZ_FIRED_TRIGGERS where lower(trigger_name) like '%abc%';
select * from grouper_qz_job_details gqjd where lower(GQJD.JOB_NAME ) like '%abc%';

If you want to unschedule BLOCKED jobs (the code part is one line of GSH)

GrouperSession.startRootSession();
for (String triggerName : HibernateSession.bySqlStatic().listSelect(String.class, "select trigger_name from GROUPER_QZ_TRIGGERS where trigger_state = 'BLOCKED'", null)) {GrouperLoader.schedulerFactory().getScheduler().unscheduleJob(org.quartz.TriggerKey.triggerKey(triggerName));}


If you get errors about a database quartz job, you can unschedule with (or whatever the trigger name is):

select trigger_name from GROUPER_QZ_TRIGGERS where lower(trigger_name) like '%abc%';
gsh 2% GrouperLoader.schedulerFactory().getScheduler().unscheduleJob(org.quartz.TriggerKey.triggerKey("triggerMessaging_MESSAGING_listener_messagingListener"))


Performance Settings

These are the default settings:

# if should use threads in the loader for add/remove member
# {valueType: "boolean", required: true}
loader.use.membershipThreads=true

# number of threads to use for each group job (not shared among jobs)
# {valueType: "integer", required: true}
loader.membershipThreadPoolSize=10

# if should use threads in the loader for each group in a group of groups
# {valueType: "boolean", required: true}
loader.use.groupThreads=true

# number of threads to use for each list of groups job (not shared among jobs)
# {valueType: "integer", required: true}
loader.groupThreadPoolSize=20


So for a simple job (SQL_SIMPLE, LDAP_SIMPLE), you can have up to 10 threads.  However for a single groupList job (SQL_GROUP_LIST, LDAP_GROUP_LIST, LDAP_GROUPS_FROM_ATTRIBUTES), you can have up to 200 threads since each of the 20 group threads can have 10 membership threads.

Loader permissions

This table reflects loader permissions in Grouper v2.5.53+

ResourceMisc Loader screenReadonly Loader functionsRead/write Loader function
DescriptionScreen with all loader jobs listed with config and statusSome functions on each loader jobSee and edit the loader configuration for a loader job
Functions
  • Job names
  • Schedule
  • Stats
  • View settings
  • View logs
  • Diagnostics
  • Enable/disable job
  • Edit loader settings
  • Run loader
  • Schedule job
Grouper sysadminYesYesYes
etc:loaderEditorsYes

Yes

* if has VIEW on group

Yes

* if has VIEW on group

etc:loaderViewersYes

Yes

* if has VIEW on group

No
Group adminsNo

Yes


No
Everyone elseNoNoNo

Some other settings

SettingDescription
uiV2.group.allowGroupAdminsToRefreshLoaderJobsIf someone is in the configured group, and they are admin on a group, then they can run that loader job




Add loader job from GSH

import java.util.Map;

import edu.internet2.middleware.grouper.Group;
import edu.internet2.middleware.grouper.GroupSave;
import edu.internet2.middleware.grouper.GrouperSession;
import edu.internet2.middleware.grouper.app.loader.GrouperLoader;
import edu.internet2.middleware.grouper.attr.assign.AttributeAssign;
import edu.internet2.middleware.grouper.attr.assign.AttributeAssignSave;
import edu.internet2.middleware.grouper.attr.assign.AttributeAssignType;
import edu.internet2.middleware.grouper.cfg.GrouperConfig;
import edu.internet2.middleware.grouper.util.GrouperUtil;

String groupName = "test:testGroup";

Map<String, String> settingsNameValue = GrouperUtil.toMap(
    "dbName", "grouper",
    "type", "SQL_SIMPLE",
    "scheduleType", "CRON",
    "quartzCron", "0 0 6 * * ?",
    "query", "select 1 from whatever",
    "failsafeUse", "T",
    "failsafeSendEmail", "T",
    "minGroupSize", "50",
    "maxGroupPercentRemove", "20",
    "minGroupNumberOfMembers", "100"
    );

GrouperSession grouperSession = GrouperSession.startRootSession();

Group group = new GroupSave().assignName(groupName).assignCreateParentStemsIfNotExist(true).save();

String etcStemName = GrouperConfig.retrieveConfig().propertyValueString("grouper.rootStemForBuiltinObjects", "etc");

AttributeAssign markerAssign = new AttributeAssignSave(grouperSession).assignOwnerGroup(group).
    assignNameOfAttributeDefName(etcStemName + ":legacy:attribute:legacyGroupType_grouperLoader").assignAttributeAssignType(AttributeAssignType.group).save();

for (String settingName : settingsNameValue.keySet()) {
  String settingValue = settingsNameValue.get(settingName);
  
 new AttributeAssignSave(grouperSession).assignOwnerAttributeAssign(markerAssign).
      assignNameOfAttributeDefName(etcStemName + ":legacy:attribute:legacyAttribute_grouperLoader" + GrouperUtil.capitalize(settingName)).
      assignAttributeAssignType(AttributeAssignType.group_asgn).addValue(settingValue).save();
}

GrouperLoader.scheduleJobs();


Possible to do's

See Also

Grouper Loader Class List Example from University of Pennsylvania