Appendix H. HAvBED Database and Web Services Documentation
Purpose of This Document
The purpose of this document is to provide necessary information required to properly document, deploy, and host an application created within the Denver Health Information Services Department. It is limited to hardware and database specifications.
Contents
1. Introduction
1.1. Objectives
1.2. Scope
2. Application
2.1. Application
Narrative/Overview
2.3. Schematic
Diagram of the Application
2.4. Application
Profile
2.4.1. DNS Requirements
2.4.2. SSL Requirements
2.4.3. Firewall Ports
Requirements
2.4.7. Hardware
3. Operations
Guide
3.1. Application
Components
3.6. Error Codes & Messages
3.8. Logging
4. System
Administration Guide
4.2. Database Administration
4.2.1. Database Details
1.
Introduction
1.1.
Objectives
This document describes database architecture and hardware configuration for the HAvBED Web application.
Return to Appendix H Contents
1.2.
Scope
This document is focused on the HAvBED database details.
Return to Appendix H Contents
2.
Application
2.1. Application
Narrative/Overview
The HAvBED system is a national hospital bed-tracking tool which is an internet-based system available for secure Web site viewing by federal, regional and local offices of emergency management. This is a AHRQ grant-funded research effort to develop a national hospital bed-tracking system in the event of a medical emergency such as a bioterrorist attack.
This system is comprised of a database, Web services and a Web site. The database stores the secure information that is pushed via XML from disparate hospital systems located around the U.S.
The Web services will ensure a secure system-to-system transfer of bed availability information into the HAvBED database. The Web site will allow a secure information display as well as a secure log-in for bed availability data entry from other participating hospitals which do not have a computer system interface with HAvBED.
This is a proof of concept project, and all hardware specifications have been designed to meet this limited functionality.
High Level Application Architecture
- Data is passed via SSL to a Web service on the application server, where it is validated and inserted into the SQL Server 2000 database.
- The application server is located in a secure DMZ (demilitarized zone), only allowing HTTPS traffic on port 443.
- The SQL Server database is located in a second DMZ, and accessible only via port 1433 from the application server IP.
- Challenge and response authentication is managed via the Web application for both XML stream data feeds and manual Web data entry.
- Data can then be reported via a Web page report or as points on a map, utilizing ArcIMS.
- Users must log on with individualized usernames and passwords.
- ArcIMS (ESRI) must be installed on the application server.
- All code for the HAvBED project's Web site and Web service were written using Microsoft's .NET framework, ASP.NET and C#.
Return to Appendix H Contents
Select to access Schematic Diagram.
Return to Appendix H Contents
2.4.
Application Profile
Return to Appendix H Contents
2.4.2. SSL
Requirements
Return to Appendix H Contents
2.4.3
Firewall Ports Requirements
- HTTPS port 443 for access from Internet user to Web servers.
- SQL port 1433 for access from Web server to SQL Server.
- ArcSDE port 5151 for access from ArcIMS to ArcSDE.
- Using ArcSDE on a Secure Server requires that Port 137 be opened. Due to security concerns Port 137 was not open and subsequently data residing in ArcSDE was placed on the Application Server.
Return to Appendix H Contents
2.4.4.
Hardware
- For Web Server, require 1 GB RAM and 2 Processors with 40 GB hard disk space.
- For SQL Server, require 1 GB RAM and 2 Processors with 40 GB hard disk space.
Return to Appendix H Contents
3.
Operations Guide
3.1.
Application Components
Web Services
The HAvBED project utilizes a Web service in order to gather data from partner's automated systems via the internet. The project group collaborated to design an XML Schema which would effectively communicate all of the information required for HAvBED. The goal of this schema was to allow for any amount of data to be transmitted, from a single bed count to an entire update for multiple facilities. The decision to accept updates to HAvBED via an XML Web service was driven largely by the platform independence of Web services. Partners used both .NET, and Java clients to transmit updates to the Web service. In fact, many other languages could have also been used if desired.
The HAvBED Web service API was rather simple. The publicly exposed methods were:
- HelloWorld—Returns "Hello World." This method was provided so that partners could validate the communication between their client application and the Web service. No authentication is performed in this method.
- SubmitFacilityUpdate—This method updates the HAvBED database. This method validates the submitted user credentials against the HAvBED database. The string parameter is parsed as an XML document and validated against the schema. If authenticated and the document is valid the document is parsed to update values in the database and an acknowledgement is returned.
- ValidateHospitalUpdate—This method validates the string parameter against the HAvBED schema. Partners were able to use this method to ensure that the document they are submitting to SubmitFacilityUpdate is in a valid format. This method uses the same validation logic as the SubmitFacilityUpdate method. No authentication is performed by this method.
Web Application
HAvBED's primary interface is through an ASP.NET Web application which is publicly facing on a server using SSL encryption. Users are granted access to this application by an administrator of the system. Due to the public facing system, it was necessary to provide a basic authentication mechanism (username and password). SSL was incorporated to prevent unencrypted passwords from being sent across the internet. SSL also protects sensitive data about facilities from being intercepted by malicious users on the internet.
The Web application defines 4 types of roles for users:
- Administrator—has complete access to the system. This role can create new facilities, add or modify users, and update information about any facility.
- Data Entry—can view/edit facilities for the scale associated with the login.
- Data Display—can view facilities for the scale associated with the login.
- Web Service User—the only role which is allowed to update the system via the Web service.
This role has no permission to view the Web site.
Authorization to view facilities is dictated by "Scales." A scale is the geographic area which a user is allowed to view. The scale can range from national level to a specific city, to an individual hospital. A filter control is used to allow a user to navigate through the data. This control will not allow the user to navigate to any scale for which they are not authorized. Additional controls have been built into the ASP.NET data entry pages to only render editable fields if the current user has privileges to update a facility.
Security
HAvBED user credentials are stored in the database. The user name is stored in plain text. The password is encrypted an SHA1 encryption algorithm. This hashed password is stored in the database instead of the plain text password. To authenticate a user, the password entered when a user logs in is hashed using the same algorithm and compared to the value in the database. If the values match, the correct password was presented. This hashing mechanism is used to prevent a hacker from discovering the passwords of users in the event that the database is compromised.
Return to Appendix H Contents
3.6. Error Codes & Messages
Web Site Errors
If an error occurs in the HAvBED Web application the user will be redirected to an error page. This page provides a generic error message indicating that a problem occurred and the error has been logged. This high-level message is used for security purposes to prevent a possible hacker gaining insight into the structure of the application. In addition, many users are confused by a detailed error message which provides little value to anyone other than a developer.
Web Service Errors
Errors that occur when updating the HAvBED system via the Web service receive notification via SoapExceptions returned to the client. All documents submitted are validated against a published XML schema. For documents that fail validation all validation messages are returned to the client. For application exceptions the error message returned is the Message property of the Exception which was thrown.
Return to Appendix H Contents
3.8. Logging
User Login Attempts
The system logs all attempted logins from both the Web site and the Web service. The success status of each login, the time, and source (Web site/Web service) are logged. This data has been utilized to report on activity levels in the system and to monitor for possible attempts to hack into the site.
Web Service Updates
The Web service keeps a log of all submitted xml updates for requests that from a user that has been validated successfully. The logged files have been useful in debugging errors in submitted documents, both for invalid schemas and errors in the data which need to be reconciled. In a high capacity environment consideration must be made for archiving files and monitoring storage capacity.
Error Logging
All database updates and ASP.NET code behinds have been wrapped by Microsoft's Enterprise Library for logging errors in the system. In production mode errors will be logged to include the location of the error, error message, timestamp, along with additional data. When an error occurs the user is redirected to a generic error message indicating that a problem was encountered and the error logged.
Return to Appendix H Contents
4.
System Administration Guide
4.2. Database Administration
4.2.1. Database Details
Databases:
- Database Name = havbed
- Object owners = dbo
- Sort order/Collation = Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data.
Tables:
- tbl_bed_availability
- insert only table containing all new bed count data.
- tbl_bed_category
- lookup table containing bed categories (e.g. current, surge, American Hospital Association [AHA] survey, etc).
- tbl_bed_category_type
- join table for tbl_bed_category and tbl_bed_type.
- tbl_bed_type
- lookup table containing bed types (e.g. Adult ICU, Burn, Pediatric ICU, etc.).
- tbl_city
- City lookup table initially populated with cities associated with the 2003 AHA data.
- tbl_county
- County lookup table initially populated with counties associated with the 2003 AHA data. AHA data contained a FIPS lookup for county/State (http://www.itl.nist.gov/fipspubs/).
- tbl_facility_availability
- insert only table containing divert and availability data (Boolean values).
- tbl_facility_type
- description of facitlity (service, e.g. Emergency Department Status, Decontamination Facility Availability, etc).
- tbl_facility_type_availability_decode
- description of facility type status (e.g. On divert, open, etc).
- tbl_hospital
- Hospital demographics, contact information, geographic location, etc.
- tbl_lu_State_county_FIPS
- lookup table containing FIPS county/State data (http://www.itl.nist.gov/fipspubs/).
- tbl_region
- tbl_region_State
- join table for States to regions to region types
- tbl_region_type
- description of region types (e.g. FEMA/ NDMS/ PHS Federal Regions, CDC Federal Regions, etc).
- tbl_role
- Security table describing user roles.
- tbl_scale
- table used with ArcIMS to determine which mapping layer to display.
- tbl_State
- tbl_user
- security table containing user credentials.
Select to access Database Diagram.
Return to Appendix H Contents
Return to Contents
Proceed to Next Section
540 Gaither Road Rockville, MD 20850