extreme blue © 2004 ibm corporation eunomia hdb compliance auditing system architecture

Extreme Blue

© 2004 IBM Corporation

EunomiaHDB Compliance Auditing

System Architecture

Extreme Blue

© 2004 IBM CorporationEunomia Compliance Auditing | System Architecture

Eunomia system overview

Two components:

Component 1: the logging tool will record the extra information necessary to determine “who accessed which data?”

Component 2: the audit tool will retrieve this information from the logs to answer questions about data access.

Extreme Blue


Eunomia system overview

Enterprise Application

Eunomia Interface

DB2

Log Retrieval API

Audit Tool

Requests for Personal Information

Chief Privacy Officer

Extreme Blue


Piece 1: The Logging Tool

Extreme Blue


Overview of logging from 50,000 feet

Enterprise

Application

Eunomia Interface

DB2

Enterprise

Application

DB2

Before: After:

JDBC

JDBC

DB2 Driver API

Extreme Blue



We want to:

minimize overhead of storing extra logging information

defer as much computation expense as possible

Extreme Blue



Solution:

query logs – record all queries for data

transaction log – record all changes to data in the database in shadow tables

at audit time, calculate from the shadow tables which information was accessed by which queries

Extreme Blue


Overview of query logging

Enterprise

Application

Query Handler

DataQuery Logs

Query

JDBC Interface

Write Log

Extreme Blue


Overview of backlogging

Personal Information

Tables

Shadow Tables

Triggers on Update, Insert, and Delete

Shadow tables maintain a temporal log of all information stored in the actual data tables.

Suppose you want to know what the database looked like at a certain point in time.

First select the set of records that have an earlier timestamp than your desired date

From that set, then select the set of records that have the latest timestamp.

Extreme Blue


PACT configuration wizard

an automated tool for configuring a database to support logging

a database administrator can specify a database connection which the wizard will analyze

the wizard will generate and execute the SQL necessary to augment the existing database tables

Extreme Blue


Eunomia configuration wizard

prompt admin for database settings

analyze databasetables

create shadow tables

populate shadow tables with current table information

create query logtable

Extreme Blue


Overview of logging from 1000 feet

PACT Driver Eunomia Configuration Tool

Logs Data Tables Shadow Tables

Audit Tools

Applications DBA

CPO

Triggers

Extreme Blue


Overview of logging from 100 feet

JDBC Interface

Logs Data Shadow Tables

Eunomia Interface

Applications

JSP/GUI Interface

Log Setup

Pact Configuration Wizard

BacklogSQLGenerator

JDBC Interface

Triggers

query record

query andquery results

setup query logs

read schema

setup backlogs

DoctorDBA

Extreme Blue


Piece 2: The Audit Tool

Extreme Blue


Overview of auditing from 50,000 feet

Logs Shadow Tables

Log Retrieval API

Audit Tool

Extreme Blue


Overview of auditing from 50,000 feet

Auditing tools only care about questions like “What information was accessed by doctor X on date Y, in response to query Z.” The auditing tools would love to have the log data in the following format:

Query Date Requester Fields Accessed Data Returnedselect * from patients 2/1/2004 Dr. Haas record 7->name Joseph Lawsselect * from patients 2/1/2004 Dr. Haas record 7->age 20select * from patients 2/1/2004 Dr. Haas record 7->SSN 123-45-6789

Unfortunately, processing all of the query log and backlog data to put it in this format is computationally expensive. Instead, it is necessary to process the data on the fly using a SQL query rewrite engine.

This layer of complexity is hidden within the “Log Retrieval API,” which will translate the data from the log format, to the format the audit tools want to use it in.

Note – table is just a quick illustration of how the auditing tools would like to get at the data. Don’t nit-pick it.

Extreme Blue


Overview of auditing from 1000 feet

SQL Rewrite Engine

JDBC and SQL

Request Processor Result Processor

Log Retrieval Layer

Log Retrieval API

Tools For Viewing Logs Tools for Comparing Logs

Against Privacy Policies

Audit Application

Logs Shadow Tables

Extreme Blue


Queries and Query Sets

An auditor may want to retrieve a set of queries, and then process and analyze the queries in a number of ways

We will create “Query” objects and Query Sets, which will represent the query information retrieved from the database.

This will eliminate repeat computation when someone wants to process a set of queries in multiple ways.

Extreme Blue


Queries and Query Sets

Database

Log Retrieval API

Audit Tool

JDBC: SQL and ResultSet Objects

Query and QuerySet Objects

extreme blue © 2004 ibm corporation eunomia hdb compliance auditing system architecture

Documents