Our department that manages doors sends feeds to ccure data via CSV and email.
We need to get that data into Grouper.
We wrote a simple "other job" that will check the email box every day, get the latest CSV, put the data into a SQL table, delete the email(s). Then a loader job can load that into groups for door access policies.
Make a table to hold door access (oracle)
CREATE TABLE DOOR_ACCESS ( ID VARCHAR2(40 CHAR) NOT NULL, PENN_ID VARCHAR2(20 CHAR) NOT NULL, CLEARANCE_NAME VARCHAR2(500 CHAR) NOT NULL, CLEARANCE_NAME_FORMATTED VARCHAR2(500 CHAR) NOT NULL, CLEARANCE_TYPE VARCHAR2(40 CHAR) NOT NULL ); CREATE UNIQUE INDEX DOOR_ACCESS_PK ON DOOR_ACCESS (ID); CREATE UNIQUE INDEX PERSON_CLEARANCE_ORIG_IDX ON DOOR_ACCESS (CLEARANCE_NAME, PENN_ID); ALTER TABLE DOOR_ACCESS ADD ( CONSTRAINT DOOR_ACCESS_PK PRIMARY KEY (ID) USING INDEX DOOR_ACCESS_PK);
Compile the program below into a jar, and add to classpath of grouper. Configure in grouper-loader.properties
###################################### ## CCURE ###################################### penn.ccure.email.server = something.office365.com penn.ccure.email.user = user@deptpenn.edu penn.ccure.email.password = /my/password.file # list of comma separated strings to allow email from penn.ccure.email.allowMailFromList = mchyzer, thereal@sender.upenn.edu otherJob.ccure.class = edu.upenn.isc.pennGrouper.ccure.ProcessMailFromCcure otherJob.ccure.quartzCron = 0 30 8 * * ?
Make some loader jobs, one list of groups which gets all access. One that just sees who has IT dept access. Then we can composite minus to active employee, and you are left with a group which is a report of who should probably be removed.
Make a bean which maps to that table using the Grouper Client object relationship mapping
/**
* @author mchyzer
* $Id$
*/
package edu.upenn.isc.pennGrouper.ccure;
import java.util.List;
import edu.internet2.middleware.grouperClient.jdbc.GcDbAccess;
import edu.internet2.middleware.grouperClient.jdbc.GcPersist;
import edu.internet2.middleware.grouperClient.jdbc.GcPersistableClass;
import edu.internet2.middleware.grouperClient.jdbc.GcPersistableField;
/**
*
*/
@GcPersistableClass(defaultFieldPersist=GcPersist.doPersist, tableName="DOOR_ACCESS")
public class DoorAccess {
/**
*
* @param args
*/
public static void main(String[] args) {
List<DoorAccess> doorAccessTemps = new GcDbAccess().selectList(DoorAccess.class);
}
/**
*
*/
public DoorAccess() {
}
/**
* uuid
*/
@GcPersistableField(primaryKey=true, primaryKeyManuallyAssigned=true)
private String id;
/**
* pennId
*/
private String pennId;
/**
* clearanceName
*/
private String clearanceName;
/**
* clearanceNameFormatted
*/
private String clearanceNameFormatted;
/**
* clearanceType
*/
private String clearanceType;
/**
* uuid
* @return the id
*/
public String getId() {
return this.id;
}
/**
* uuid
* @param id1 the id to set
*/
public void setId(String id1) {
this.id = id1;
}
/**
* pennId
* @return the pennId
*/
public String getPennId() {
return this.pennId;
}
/**
* pennId
* @param pennId1 the pennId to set
*/
public void setPennId(String pennId1) {
this.pennId = pennId1;
}
/**
* clearanceName
* @return the clearanceName
*/
public String getClearanceName() {
return this.clearanceName;
}
/**
* clearanceName
* @param clearanceName1 the clearanceName to set
*/
public void setClearanceName(String clearanceName1) {
this.clearanceName = clearanceName1;
}
/**
* clearanceNameFormatted
* @return the clearanceNameFormatted
*/
public String getClearanceNameFormatted() {
return this.clearanceNameFormatted;
}
/**
* clearanceNameFormatted
* @param clearanceNameFormatted1 the clearanceNameFormatted to set
*/
public void setClearanceNameFormatted(String clearanceNameFormatted1) {
this.clearanceNameFormatted = clearanceNameFormatted1;
}
/**
* clearanceType
* @return the clearanceType
*/
public String getClearanceType() {
return this.clearanceType;
}
/**
* clearanceType
* @param clearanceType1 the clearanceType to set
*/
public void setClearanceType(String clearanceType1) {
this.clearanceType = clearanceType1;
}
}
Java program to check email and load into table
/**
* @author mchyzer
* $Id$
*/
package edu.upenn.isc.pennGrouper.ccure;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.io.StringWriter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import javax.mail.Address;
import javax.mail.BodyPart;
import javax.mail.Flags;
import javax.mail.Folder;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.NoSuchProviderException;
import javax.mail.Session;
import javax.mail.Store;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMultipart;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import edu.internet2.middleware.grouper.app.loader.GrouperLoaderConfig;
import edu.internet2.middleware.grouper.app.loader.OtherJobBase;
import edu.internet2.middleware.grouper.app.loader.db.Hib3GrouperLoaderLog;
import edu.internet2.middleware.grouper.cfg.GrouperConfig;
import edu.internet2.middleware.grouper.internal.util.GrouperUuid;
import edu.internet2.middleware.grouper.util.GrouperUtil;
import edu.internet2.middleware.grouperClient.collections.MultiKey;
import edu.internet2.middleware.grouperClient.jdbc.GcDbAccess;
import edu.internet2.middleware.grouperClient.util.GrouperClientUtils;
/**
*
*/
public class ProcessMailFromCcure extends OtherJobBase {
/**
*
* @param args
*/
public static void main(String[] args) {
processEmail(null);
}
/**
* @param hib3GrouperLoaderLog
*
*/
public static void processEmail(Hib3GrouperLoaderLog hib3GrouperLoaderLog) {
@SuppressWarnings("unused")
boolean passedInGrouperLoaderLog = hib3GrouperLoaderLog != null;
if (hib3GrouperLoaderLog == null) {
hib3GrouperLoaderLog = new Hib3GrouperLoaderLog();
}
Map<String, Object> debugMap = new LinkedHashMap<String, Object>();
long startTimeNanos = System.nanoTime();
debugMap.put("method", "processEmail");
Properties props = new Properties();
String host = GrouperLoaderConfig.retrieveConfig().propertyValueStringRequired("penn.ccure.email.server");
String username = GrouperLoaderConfig.retrieveConfig().propertyValueStringRequired("penn.ccure.email.user");
String password = GrouperLoaderConfig.retrieveConfig().propertyValueStringRequired("penn.ccure.email.password");
String provider = "imaps";
Store store = null;
Folder inbox = null;
Folder deleted = null;
boolean processedEmail = false;
try {
//Connect to the server
Session session = Session.getDefaultInstance(props, null);
store = session.getStore(provider);
store.connect(host, username, password);
//open the inbox folder
inbox = store.getFolder("INBOX");
deleted = store.getFolder("Deleted Items");
inbox.open(Folder.READ_WRITE);
deleted.open(Folder.READ_WRITE);
// get a list of javamail messages as an array of messages
Message[] messages = inbox.getMessages();
debugMap.put("messagesCount", GrouperUtil.length(messages));
Set<String> allowFromSet = null;
{
String allowFromListString = GrouperLoaderConfig.retrieveConfig().propertyValueString("penn.ccure.email.allowMailFromList");
if (!StringUtils.isBlank(allowFromListString)) {
allowFromSet = GrouperUtil.splitTrimToSet(allowFromListString.toLowerCase(), ",");
}
}
String latestContents = null;
Date latestDate = null;
for (Message message : GrouperUtil.nonNull(messages, Message.class)) {
boolean validEmail = true;
if (GrouperUtil.length(allowFromSet) > 0) {
String from = GrouperUtil.defaultString(retrieveFrom(message));
validEmail = false;
for (String allowFrom : allowFromSet) {
if (from.toLowerCase().contains(allowFrom)) {
validEmail = true;
break;
}
}
}
if (validEmail) {
String contentType = message.getContentType();
if (!contentType.contains("multipart")) {
validEmail = false;
}
Multipart multiPart = null;
if (validEmail) {
multiPart = (Multipart) message.getContent();
if (multiPart != null) {
if (multiPart.getCount() != 2) {
validEmail = false;
}
}
}
debugMap.put("validEmail", validEmail);
if (validEmail) {
// MimeBodyPart part0 = (MimeBodyPart) multiPart.getBodyPart(0);
// MimeMultipart content0 = (MimeMultipart)part0.getContent();
// int contentCount0 = content0.getCount();
// IMAPBodyPart bodyPart0 = (IMAPBodyPart)content0.getBodyPart(0);
// IMAPBodyPart bodyPart1 = (IMAPBodyPart)content0.getBodyPart(1);
MimeBodyPart part1 = (MimeBodyPart) multiPart.getBodyPart(1);
// String disposition = part1.getDisposition();
// String fileName = part1.getFileName();
MimeMultipart content1 = (MimeMultipart)part1.getContent();
BodyPart bodyParta0 = content1.getBodyPart(0);
String disposition = bodyParta0.getDisposition();
debugMap.put("disposition", disposition);
// fileName = bodyParta0.getFileName();
// debugMap.put("fileName", fileName);
// if (Part.ATTACHMENT.equalsIgnoreCase(disposition)) {
// if (GrouperUtil.equals(part.getFileName(), "Clearance Report.csv")) {
InputStream inputStream = null;
StringWriter stringWriter = new StringWriter();
try {
inputStream = bodyParta0.getInputStream();
if (inputStream == null) {
continue;
}
String encoding = GrouperConfig.retrieveConfig().propertyValueString("grouper.default.fileEncoding", "UTF-8");
GrouperUtil.copy(inputStream, stringWriter, encoding);
} catch (IOException ioe) {
throw new RuntimeException("Error reading email attachment", ioe);
} finally {
GrouperUtil.closeQuietly(inputStream);
GrouperUtil.closeQuietly(stringWriter);
}
String csvContents = stringWriter.toString();
//lets harmonize newlines
csvContents = StringUtils.replace(csvContents, "\r", "\n");
csvContents = StringUtils.replace(csvContents, "\n\n", "\n");
// strip of ReportTitle
csvContents = csvContents.substring(csvContents.indexOf('\n')+1);
// ISC Clearance Report: 9/10/2018 7:50:16 AM
String title = csvContents.substring(0, csvContents.indexOf('\n'));
if (title.startsWith("ISC Clearance Report: ")) {
String dateString = title.substring("ISC Clearance Report: ".length());
SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a");
try {
Date date = format.parse(dateString);
if (latestDate == null || date.after(latestDate)) {
latestDate = date;
// add two since two newlines
latestContents = csvContents.substring(csvContents.indexOf('\n')+2);
}
} catch (ParseException pe) {
debugMap.put("invalid date", "'" + title + "'");
}
}
}
}
}
List<DoorAccess> doorAccessFromDb = new GcDbAccess().selectList(DoorAccess.class);
debugMap.put("dbRecords", GrouperUtil.length(doorAccessFromDb));
hib3GrouperLoaderLog.setTotalCount(GrouperUtil.length(doorAccessFromDb));
if (latestDate != null) {
debugMap.put("latestDate", latestDate);
// get records from database
Map<MultiKey, DoorAccess> pennIdClearanceToDoorAccessMap = new HashMap<MultiKey, DoorAccess>();
for (DoorAccess doorAccess : doorAccessFromDb) {
MultiKey multiKey = new MultiKey(doorAccess.getPennId(), doorAccess.getClearanceName());
pennIdClearanceToDoorAccessMap.put(multiKey, doorAccess);
}
Map<MultiKey, DoorAccess> pennIdClearanceToDoorAccessMapToDelete = new HashMap<MultiKey, DoorAccess>(pennIdClearanceToDoorAccessMap);
Map<MultiKey, DoorAccess> pennIdClearanceToDoorAccessMapToInsert = new HashMap<MultiKey, DoorAccess>();
Reader reader = null;
try {
reader = new StringReader(latestContents);
CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
.withHeader("DGUnion_ID","DGUnion_Type", "DGUnion_Name","Card_Num","Last_Name","First_Name","Clear_ID","Clearance_Name")
.withIgnoreHeaderCase().withFirstRecordAsHeader()
.withTrim());
int records = 0;
for (CSVRecord csvRecord : csvParser) {
// Accessing values by the names assigned to each column
String pennId = csvRecord.get("Card_Num");
String clearanceName = csvRecord.get("Clearance_Name");
MultiKey multiKey = new MultiKey(pennId, clearanceName);
pennIdClearanceToDoorAccessMapToDelete.remove(multiKey);
if (!pennIdClearanceToDoorAccessMap.containsKey(multiKey) && !pennIdClearanceToDoorAccessMapToInsert.containsKey(multiKey)) {
DoorAccess doorAccess = new DoorAccess();
doorAccess.setId(GrouperUuid.getUuid());
doorAccess.setClearanceName(clearanceName);
doorAccess.setClearanceType("isc");
doorAccess.setClearanceNameFormatted(convertToStandardName(clearanceName));
doorAccess.setPennId(pennId);
pennIdClearanceToDoorAccessMapToInsert.put(multiKey, doorAccess);
}
records++;
}
debugMap.put("records", records);
} finally {
GrouperUtil.closeQuietly(reader);
}
debugMap.put("inserts", GrouperUtil.length(pennIdClearanceToDoorAccessMapToInsert));
hib3GrouperLoaderLog.setInsertCount(GrouperUtil.length(pennIdClearanceToDoorAccessMapToInsert));
{
ArrayList<DoorAccess> doorAccessToInsert = new ArrayList<DoorAccess>(pennIdClearanceToDoorAccessMapToInsert.values());
for (DoorAccess doorAccess : doorAccessToInsert) {
new GcDbAccess().storeToDatabase(doorAccess);
}
if (GrouperUtil.length(doorAccessToInsert) > 0) {
new GcDbAccess().storeBatchToDatabase(doorAccessToInsert);
}
}
debugMap.put("deletes", GrouperUtil.length(pennIdClearanceToDoorAccessMapToDelete));
hib3GrouperLoaderLog.setDeleteCount(GrouperUtil.length(pennIdClearanceToDoorAccessMapToDelete));
{
ArrayList<DoorAccess> doorAccessToDelete = new ArrayList<DoorAccess>(pennIdClearanceToDoorAccessMapToDelete.values());
for (DoorAccess doorAccess : doorAccessToDelete) {
new GcDbAccess().deleteFromDatabase(doorAccess);
}
}
processedEmail = true;
}
inbox.copyMessages(messages, deleted);
// delete all the messages
for (Message message : GrouperUtil.nonNull(messages, Message.class)) {
message.setFlag(Flags.Flag.DELETED, true);
}
} catch (IOException ie) {
throw new RuntimeException(ie);
} catch (NoSuchProviderException nspe) {
throw new RuntimeException(nspe);
} catch (MessagingException me) {
throw new RuntimeException(me);
} finally {
debugMap.put("processedEmail", processedEmail);
//close the inbox folder and do
//remove the messages from the server
try {
inbox.close(true);
} catch (Exception e) {
}
try {
deleted.close(true);
} catch (Exception e) {
}
try {
store.close();
} catch (Exception e) {
}
CcureLog.ccureLog(debugMap, startTimeNanos);
hib3GrouperLoaderLog.setJobDescription(GrouperClientUtils.mapToString(debugMap));
}
}
/**
*
* @param input
* @return the converted string, alphanumeric, with underscores
*/
private static String convertToStandardName(String input) {
StringBuilder result = new StringBuilder();
for (int i=0;i<input.length();i++) {
char theChar = input.charAt(i);
if (Character.isAlphabetic(theChar) || Character.isDigit(theChar)) {
result.append(theChar);
} else {
result.append("_");
}
}
return result.toString();
}
/**
* get from from message
* @param javaMailMessage
* @return the string or null
* @throws MessagingException
*/
private static String retrieveFrom(Message javaMailMessage)
throws MessagingException {
String from = "";
Address a[] = javaMailMessage.getFrom();
if (a == null)
return null;
for (int i = 0; i < a.length; i++) {
Address address = a[i];
from = from + address.toString();
}
return from;
}
/** logger */
private static final Log LOG = GrouperUtil.getLog(ProcessMailFromCcure.class);
/**
* @see edu.internet2.middleware.grouper.app.loader.OtherJobBase#run(edu.internet2.middleware.grouper.app.loader.OtherJobBase.OtherJobInput)
*/
@Override
public OtherJobOutput run(OtherJobInput otherJobInput) {
Hib3GrouperLoaderLog hib3GrouperLoaderLog = otherJobInput.getHib3GrouperLoaderLog();
processEmail(hib3GrouperLoaderLog);
return null;
}
}