Winter 2003: Facilities and Spatial Data Integration (FASDI)

Features

Facilities and Spatial Data Integration (FASDI)

Pamela Drake, Budget and Finance — Computing Operations and Information Systems
Patricia Mead, Budget and Finance — Space Management and Capital Programs
Rob Johnson, Budget and Finance — Computing Operations and Information Systems

This article is the second of three to cover aspects of creating a secure, web-based data integration application. The first summarized the project scope and its processes. This article addresses data integration and authorization database design supporting FASDI's security and standards. The third will present the COIS ColdFusion Web Application Authorization Framework.

Introduction

FASDI is a web-based management information system integrating geographic and tabular campuswide data to support decision making and institutional reporting. FASDI integrates any data that can be associated with a building or physical area. Please visit the FASDI website (http://fasdi.vcbf.berkeley.edu/) to view phase one of the project.

Data integration

Data integration was essential to the project. Early on, the FASDI Committee set objectives including:

Standardize data definition, format, and values where possible.
Document and cross-reference necessary exceptions and differences.
Agree on standards for future use.

We approached the project by identifying the data elements necessary to integration including keys, descriptors, and codes necessary for consistency. We worked from higher to lower level elements.

Data collection and database population

The project has a large number of diverse data providers with multiple systems and varied data requiring a flexible approach to data collection and database population. We encouraged initial submission of data with few restrictions, making suggestions for standardizing submission later as we gained expertise with database population. We developed a standard process to populate the database, documenting the process and actual transformation steps for each data source. We imported data using SQL Server DTS packages, reformatted data using SQL queries and finally created database views. Some simple changes will make future data collection easier:

Naming submitted files according to our naming standards.
Including only one row of meaningful headers as database column names.
Placing all notes or comments on a separate worksheet, not interspersed with data.

Database design

FASDI web page design required slicing and dicing data in various ways. Multiple data sources from different data providers appear on the same web page and a web page may contain multiple security levels. Our solution was to access the data needed for each page using multiple SQL Server views and stored procedures.

Naming conventions for database views identify the source of data, web page where data is used, security level, data provider, and descriptive information. Database view names are in intercap convention and consist of optional, variable-length nodes structured as vDddWwwSssDddQqq where:

v = "view"
Ddd = data source or database acronym
Www = web page where used
Sss = security level
Ddd = department providing data
Qqq = qualifying node(s) describing specific system / table / type of data

Standards

COIS established certain standards specifically designed to safeguard web security by adding additional layers of control over data available to web developers:

All web database access is by database views or stored procedures.
Database views are created by selecting named columns (not "SELECT *"), ensuring that columns added to tables do not automatically become available to developers.
The UserId and DateLastModified are tracked on each table for audit purposes.
The security level (high / medium / low) is specified in each view name for clarity.

Authorize database

COIS designed an Authorize database to supplement campus standard CalNet authentication and LDAP authorization as described in the previous article (see FASDI business security rules in the November-December 2002 BC&C [/bcc/Nov_Dec2002/feat.fasdi.html]). The Authorize database design is flexible and used by diverse applications which may relate to multiple Organizations with different methods of security specified at the Application level. Individual users with contact information may be entered and associated with Organizations and ApplicationRoles. In its most granular configuration, Authorize grants permissions defined as "AuthorizationObjects" to individual users or roles within an application.

The main Authorize tables and keys are:

Tables	Keys	Key definitions
Organization	OrgId	Identifies Organization (e.g., Payroll Processing Unit from LDAP, roll-up values)
People	UserId	Identifies individual users (e.g., CalNet UiD)
Application	ApplicationId	Identifies an application
ApplicationRole	ApplicationId + RoleId	Identifies an ApplicationRole which is granted permission of varying types to one or many AuthorizationObjects
AuthorizationObjects	ApplicationId + AuthObjectId	Identifies an object, permission for which may be granted to one or more ApplicationRoles

ApplicationRoles are defined as needed and are granted permission by owning one or more AuthorizationObjects (which may be owned by one or more ApplicationRoles). The attributes of AuthorizationObjects define whether the permission granted is Read or Update and include various AuthorizationObjectTypes and ExternalDataSource information.

ColdFusion Application Framework and the Authorize database

COIS designed a ColdFusion Web Application Authorization Framework (CCAAF) to use the Authorize database and create a security "keychain" of AuthorizationObjects contained within web session variables. These objects describe database roles granted permission to access data falling into various security categories required by the FASDI project. CCAAF used with the Authorize database facilitates rapid development of security methodologies for diverse web applications and will be discussed in the next article.