At Penn we want to enforce two step authentication but we do not want to overwhelm our support staff.

Construct a loader to gradually load up a group, 200 people every hour.  Allow blackout periods.

This table sets up the timeperiods

CREATE TABLE AUTHZADM.two_step_timeperiod_enabled
  from_date  DATE                               NOT NULL,
  to_date    DATE                               NOT NULL

Identify the group to get the members from.  Note, these should not be people who are already enrolled in two step.


Subtract people in the target group, or it will reload the same members each time


Get a query to get 200 members from the group

CREATE OR REPLACE VIEW AUTHZADM.two_step_Require_students_v
select subject_id, 
when exists (select 1 from two_Step_timeperiod_enabled where sysdate between from_date and to_date )
then 'T'
else 'F'
end as load_now from grouper_memberships_lw_v gmlv 
where group_name = 'penn:isc:ait:apps:twoFactor:groups:requiredUsersStudent:twoStepStudentsNotEnrolledOrRequired'
and list_name = 'members' and rownum <= 200;

Make a group to hold the eventual members


Loader job will add any members already in the group, and add 200 members every hour, if the time period is correct

select subject_id, 'pennperson' as subject_source_id
from grouper_memberships_lw_v gmlv where group_name = 'penn:isc:ait:apps:twoFactor:groups:requiredUsersStudent:twoStepRequiredStudentsGradual'
and list_name = 'members' and subject_source = 'pennperson'
select subject_id, 'pennperson' as subject_source_id
from two_step_Require_students_v where load_now = 'T'

Test by filling up the table

Then look at the loader logs

  • No labels