Drive Portal User Account Creation From Your ERP System

1. Sheryl welcomed everyone and introduced Connie. Connie provided an overview of the AQIP process, specifically the Admissions AQIP Project. We have already looked at Traditional Freshman, internally in Admissions and then externally throughout the University. We decided to move forward with the Degree Completion to ensure that we are effective in promoting the new programs. Next, we will look at Transfer students. In addition to identifying opportunities for improvements, think about how to measure those improvements. Sheryl welcomed everyone and introduced Connie. Connie provided an overview of the AQIP process, specifically the Admissions AQIP Project. We have already looked at Traditional Freshman, internally in Admissions and then externally throughout the University. We decided to move forward with the Degree Completion to ensure that we are effective in promoting the new programs. Next, we will look at Transfer students. In addition to identifying opportunities for improvements, think about how to measure those improvements.

2. Quick Overview School Info Located in Joliet, IL National presence 1200 on-campus students 2400 degree completion and graduate students �Techie Details� Production with uPortal August 2006 Running uPortal 2.5.2, CAS, Oracle DB Banner 7.x ERP / Oracle DB 3 FT Staff, 2 Student Workers Novell E-Directory Track results through direct mail BRC codeTrack results through direct mail BRC code

3. About This Presentation We�ll talk about: Account creation challenges in our environment. The approach we used to automate account creation and password manipulation. Describe the technologies involved. Reflect on what we�d do differently. This is not as much of a linear how-to presentation, but rather geared to share our experiences and generate ideas for your own institution. Feel free to ask questions throughout. Lets make this interactive!

4. The Problem� Account creation was a very manual process Provision network/portal access, email, and other services Problem when staff went on vacation! Initial batch load, but then changes done individually as students registered late or dropped courses. Couldn�t keep up with adjunct faculty changes Users always had to call IT to reset their passwords. No tie between our directory and a persons attributes i.e., bio info, employment records, student records With the portal people expected real time access to their information. People were not notified when their accounts were created.

5. Solution Overview - Design Anybody being provisioned with IT services MUST be in our ERP system. The ERP system tells us what role(s) a person plays at USF. Student, Faculty, Staff, Alum, Prospects The unique ID for the person in the ERP system is stored in LDAP workForceId attribute. Directory and ERP are now �connected� ERP system queries the directory and determines who needs accounts created or roles.

6. Solution Overview - Implementation Had to work with various departments to get the data in the system, correctly, and in a timely manner. This was our biggest challenge. Had to coordinate admissions, registrar, our colleges/deans for faculty, and HR. Had to get the colleges and HR to communicate effectively with regards to adjunct faculty (and last minute changes). Data is not in the system, user doesn�t get an account. Call respective dept. to get it right.

7. Solution Overview - Process Nightly Job writes a file of directory changes to an NFS mount. account creations/deletions and role changes. ERP system assigns a random 6 digit initial password for new accounts. Track that a letter needs to go out (GURMAIL table) Novell Server has an AT job to process the file daily. Uses the JRButils package to manipulate directory ERP system checks to see which accounts were created, and emails the users their account info (based on unsent GURMAIL records) Accounts are created within 24 hours of data entered into ERP system.

8. Solution Overview (PW Reset) When user logs into portal the first time, they MUST setup a security question to use for password resets. Data stored in the ERP system. Uses existing Banner table, GOBTPAC User must change their password upon first login. Portal LDAP user has the privilege to reset passwords. Check birth date and security question associated with the person (in ERP). Uses JNDI to connect to the directory and reset password.

9. Solution Overview (PW Expiration) When user logs into portal, we check to see if their password has expired. Allows directory to maintain the rules for expiration, length, and password strength. passwordMinimumLength & passwordExpirationInterval attributes User must change password to continue logging in. Use JNDI again to update the directory with new password.

10. Batch Account Creation Process Picture

11. Technologies Involved Unix Shell Scripts, Cron Oracle PL/SQL and SQL DBMS_LDAP Novell Batch Scripting/AT JRButils User Templates Java JNDI Javamail (java stored proc in oracle) JSPs & Servlets LDAP

12. USF_LDAP_UTILConnect the ERP and the Directory. This is the Oracle PL/SQL package that allows us to pull directory data into SQL, and essentially �join� the ERP to E-Directory. Uses the Oracle DBMS_LDAP package Other DB�s will need another approach, or build entirely in JNDI Major Methods pidmExistsInLdap getLdapAttributeForPidm getLdapUserIdForPidm getLdapPidmForUserId generateLdapUserId isUserMemberOfGroup isPidmMemberOfGroup isFilterStringMemberOfGroup ldapBind ldapDisconnect Source code attached to slide�

13. USF_LDAP_UTILExample Usage Query the faculty table in Banner ERP system, and get their first name, last name, and directory user ID. SELECT per.last_name, per.first_name, usf_ldap_util.getLdapUserIdForPidm(sibinst_pidm) FROM usfv_general_person per, sibinst WHERE per.pidm_key = sibinst_pidm ORDER BY UPPER(per.last_name), UPPER(per.first_name) Now we can easily see who has accounts, and who doesn�t using nothing more complex than SQL!

14. LdapUtil.java - Overview Utility class we use from our portal to connect to the directory and perform password maintenance, and retrieve/update attributes. public Object getUserIdProperty (String userDn, String propertyName) public void updateAttributes(String dn, String propertyName, String propertyValue) public void changePassword(String dn, String newPassword) public boolean isPasswordExpired(String cn) We can use this class from within the portal or within client applications to manipulate the directory. i.e., Servlets call this class for password resets, etc. Source code attached to slide�

15. LdapUtil.java � Some Examples Connect to the directory LdapUtil ldapUtil = new LdapUtil("ldap://127.0.0.1:389", "cn=ldapuserid,ou=adm,ou=main,o=sf", "passwordhere", "ou=main,o=sf"); ldapUtil.connectDirectory(); Get an attribute from the directory for a CN pidm = (String) ldapUtil.getPersonAttributeForCn("widget1", "workforceID"); When does the password expire? pwExpireDateString = (String) ldapUtil.getPersonAttributeForCn("widget1", "passwordExpirationTime"); Is the password expired? boolean expired = ldapUtil.isPasswordExpired("widget1"); Get the DN for a CN dn = (String) ldapUtil.getPersonAttributeForCn("widget1", "dn"); Update an attribute ldapUtil.updateAttributes(dn, "workforceID", "35689");

16. LdapUtil.java � Additional Info If you use the code, you may have to modify it depending on how your directory works. Password expiration times, resets, etc. High usage environments may benefit from a pool of connections to the directory, maintained by an app server. You may want to extend it to query multiple directory servers, or failover to a second server. We arbitrarily picked the workforceId attribute to store the UID of the person in our ERP. Anything would work.

17. Benefits Realized Students, faculty, staff, etc. can all reset their password through the portal if they forget it. 1813 Passwords Reset August 1st 2007� June 2nd 2008 Accounts Created August 1st 2007� June 2nd 2008 1,048 Students, 1,838 applicants, 27 Faculty Data is in our ERP system in a more timely manner! Reporting is more accurate.

18. Miscellaneous Issues Accounts are locked after 10 invalid login attempts. Must call IT to unlock. People forget the answers to their security questions. Sometimes birth dates entered incorrectly. Since we require it to reset password, this requires a call to IT.

19. Find what�s right for your Institution - One size doesn�t fit all. Places have: multiple sources of truth (HRMS, SIS, external vendor apps) Wide array of technologies in place, legacy systems. Multiple directories, physical sites, etc Try to centralize the source of truth, or at least come up with a priority hierarchy, and use it/them. The real work is in consolidating systems and getting the data right. Writing the code was the easy part�

20. Future Items Deletion of accounts is not automated yet. Issues with network storage cleanup, email retention, etc. Kicking around idea of alumni email for life� Removal of roles is not automated yet. Admin/Staff accounts not automatically created. Due to additional rights needed (printers, files, telephone, office keys) FT Faculty are automatically created, and then manually given rights to printers/files. Account creation/role update �status� widget for help desk. i.e., Where is my account?

21. Hindsight We made it too complex at the beginning! Trying to have multiple directories, replicate between them. Novell sync to openLDAP, desktop accounts in novell, portal accounts in open LDAP. Thoughts of going to Oracle Internet Directory� = more expense Too many components! Use what you have! Don�t gut the enterprise� Trust issues between our network and ERP systems groups. Will the ERP system give good enough data? It has too! Get the ERP right and build trust in it. Get the right skill set for the job!