Post Mortem
Dissecting A Successful Campus Integration Project
Theresa Auricchio
The Project:
Two colleges in the City University of New York (CUNY) system needed to implement a student retention system. The system, a client/server application, would sit beside an IBM mainframe.
Challenges:
- The colleges had no existing security architecture, meaning that the network was compromised on a regular basis.
- The colleges also had no operating system standard, so the solution had to work cross-platform. One college used Oracle 9i as its database system, while the other used SQL Server™ 2000.
- The IT department had no trained personnel available to serve as a project liaison.
- The consultant had to guarantee the integrity of the software and data without the authority or funding to implement additional solutions outside of their own.
The Plan:
The consultant would implement the student retention system in three phases. First, Lehman College would get its system in place, followed by Bronx Community College (BCC) in the second phase. Finally, the two colleges would be linked. The consultant turned to Microsoft technologies as a common baseline for each site, plugging them into the existing environment while allowing CUNY to avoid costly and disruptive changes to their infrastructure.
The Consultant:
ATSI, Adaptive Technology Solutions Inc., is a software development and consulting firm specializing in collaborative applications. The company focuses on improving operations and communications by streamlining business processes. They specialize in administrative processes and centralized records storage in education, finance, telecommunications, and government.
Background:
The CUNY system is academically rich, but resources are extremely limited. The core student information system, which encompasses finance, course schedule, and student registration, resides on an IBM mainframe. What you see on the mainframe is what you get; new routines and reports are not feasible since Lehman College employs a single, desperately overworked mainframe programmer.
As the college and the diversity of its population grew over the years, educators found that individuals were getting lost in the system. There was no way to formally identify students needing assistance. The dropout rate was suspected to be higher in certain student populations, but this was difficult to measure.
A Title V grant opened up possibilities for Lehman College educators and administrators. New data beyond registration, financial aid, and grades could now be harnessed, so administrators went to work deciding what they would need to move ahead.
Since it would prove to be a problem incorporating all this data into the existing IBM mainframe, the ATSI project team decided that the new system would have to stand side-by-side with the IBM mainframe as a client-server application. Problems with the university's network security ended up ruling out the use of Web applications. The only workstation requirement on the client is that the operating system should be Windows® 2000 or Windows XP.
How They Pulled It Off
The final goal at Lehman College is a student retention app called the Student Retention and Development System (SRDS) consisting of survey data, referrals data, long-range academic plans, advising tools, faculty data entry screens, and reporting.
To take advantage of existing data, ATSI created a routine to populate the SRDS Oracle9i database with collected data that was stored on the mainframe. A translation table was created to give user-friendly meaning to those pesky mainframe number codes (56784 would now read "freshman," 980 would mean "English 101," to give a couple examples).
On top of this translation layer, the ATSI project team developed a graphical user interface in Microsoft® Access 2000 with Visual Basic for Applications (VBA) for scripting core and ActiveX® control data objects as the data model.
Rather than attempt to re-key thousands of student records, ATSI leveraged the existing data for new purposes. Now the data is being exported for tasks such as ad hoc reporting, tracking, career counseling, faculty visits, surveys, and student intervention/dropout prevention programs.
So far, so good. After successful deployment of the Lehman College SRDS, ATSI created a customized version for Bronx Community College. The same interface was used, but here the back-end database was SQL Server 2000. SQL Server was chosen because it met or exceeded the functionality the project needed, and it had a lower total cost of ownership (BCC did not have an Oracle 9i administrator available).
The primary challenge at BCC arose when ATSI used SQL Server with Microsoft Access workgroup security. Within workgroup networking, shared resources and user information are unique to a specific computer and are unavailable to other computers. The administrator of a specific computer must maintain users, shares, and permissions at the local level, as opposed to using a centralized location where users and resources can be administered (like a Windows NT® Server domain). While workgroup security has evolved over the years into a stable security account management tool, ATSI found reoccurring flaws in the security architecture where user accounts were habitually overwritten due to toggling between Access and SQL applications.
Security Issues
There were several security challenges encountered during the system implementation. First, the university network was wide open and subject to constant hacking, making confidential student records vulnerable. Hacking at the university is pervasive. Student hackers have been able to access confidential files and in some instances alter the data. In other instances, hackers have covertly installed programs that recorded keystrokes as a means to gain access to user accounts and even inserted code into VBA files that corrupted data. ATSI learned about this house-of-cards network when they noticed that their security files kept disappearing. When a university VP was affected by this hacking, a firewall was finally installed.
Unfortunately, the new firewall was installed directly onto the network without testing, or even notifying the application developers, so network settings including the single Access Security Workgroup file shared by both colleges were set back to their defaults. Additionally, Microsoft VBA Security Update MS03-037 was installed, thwarting some hacks but also disabling some expected functionality.
Since the colleges were using the same Access Security Workgroup for the entire university, user accounts were overwritten when toggling back and forth to other Access applications. While this can be controlled if login information is administered at the SQL Server level, in practice it wasn't because the IT department had no one serving as an applications administrator. Therefore, logins were managed by local admins as a Client Zone requirement. (Oracle accounts, on the other hand, are centrally managed by a database administrator in this site.)
At both Lehman and BCC, the general practice has been to rely upon Windows Authentication and the Access system workgroup default file for security in the login process for all SQL Server and Access apps. Not only are system security workgroups unmanaged, but this mixture of systems combined to cause a bigger problem: users who accessed different systems found that their account information (user name and password) was overwritten, making the system inaccessible to them.
This problem was discovered when troubleshooting one of the affected workstations. A review of the system properties revealed that the path of the security file had been altered to coincide with the system default file. This was fixed by modifying the application code to point to a different security file location, and then placing an SRDS application icon, pointing to the correct security file, on each user's desktop. Since Lehman and BCC did not employ any network management software, push technology, or imaging, ATSI ended up applying the fix on each workstation one at a time. Now, when the network infrastructure changes or implodes, the application will still work.
The original system was launched without any definition of user roles and permissions. When the project group launched the application, they had no idea how much it would open the lines of communication—almost too much, as it turned out.
The user team soon realized that they needed to define roles and permissions as an important part of the workflow. As an example, an economics professor should be able to update information about a student in her class, but not be able to see all the student's records.
The application was locked down after a review with the user team. The review resulted in advisors being linked only to their assigned students and faculty receiving access only to student records pertaining to their particular classes.
To secure the entire object model, developers set access at the account level, permissions on the object side, roles and privileges in Oracle, and workgroups in Access. Each entry made in the Access security file required matching accounts in Oracle and Access before the application would launch. A user account table was created to centrally store user information and active/inactive status. The system access process is coded so that if one entry in the table is off, the user is terminated without access to the application. This may have been the biggest step of all; if hackers break into the system at any point, they are now denied access to the application itself and can no longer alter its code.
Outcome
This project had a very happy conclusion as users have productively used this system for nine months and new functionality is being developed on top of the more secure framework that was put in place. Legacy system data has been turned into information that the SRDS user community can build upon for their own student tracking and retention workflow and business processes. The application now functions well despite the dysfunctional network on which it resides. In an ideal environment, Access security workgroups would not be used for login security. Centrally located and administered applications would eliminate the kind of security file conflicts that were encountered by ATSI and the SRDS project team.
Because of improvements in workgroup security features in Access, the security requirements that used to require hard coding can now be performed by administrators. The ease of use is similar to that which SQL Server and Oracle offer. There are still some places where Access security should continue to improve. For instance, it is not easy to toggle between security files without hard coding. Perhaps an improvement will come in the form of a database property that would allow you to marry the application to the security file and then take it back to the system default without having to do it at individual workstations.
Theresa Auricchio is President/Principle Consultant of AVI Consulting Inc. in New York state. She consults and speaks on e-business topics, and was recently appointed to the board of mm1Media. Reach Theresa at auricchio@aviconsulting.com.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.