Auditing Compliance with a Hippocratic Database

We introduce an auditing framework for determining whether a database system is adhering to its data disclosure policies. Users formulate audit expressions to specify the (sensitive) data subject to disclosure review. An audit component accepts audit expressions and returns all queries (deemed “suspicious”) that accessed the speciﬁed data during their execution. The overhead of our approach on query processing is small, involving primarily the logging of each query string along with other minor annotations. Database triggers are used to capture updates in a backlog database. At the time of audit, a static analysis phase selects a subset of logged queries for further analysis. These queries are combined and transformed into an SQL audit query, which when run against the backlog database, identiﬁes the suspicious queries efﬁciently and precisely. We describe the algorithms and data structures used in a DB2-based implementation of this framework. Experimental results reinforce our de-sign choices and show the practicality of the approach.


Introduction
The requirement for responsibly managing privacy sensitive data is being mandated internationally through legislations and guidelines such as the United States Fair Information Practices Act, the European Union Privacy Directive, the Canadian Standard Association's Model Code for the Protection of Personal Information, the Australian Privacy Amendment Act, the Japanese Personal Information Protection Law, and others.A vision for a Hippocratic database [2] proposes ten privacy principles for managing Permission to copy without fee all or part of this material is granted provided that the copies are not made or distributed for direct commercial advantage, the VLDB copyright notice and the title of the publication and its date appear, and notice is given that copying is by permission of the Very Large Data Base Endowment.To copy otherwise, or to republish, requires a fee and/or special permission from the Endowment.
Proceedings of the 30th VLDB Conference, Toronto, Canada, 2004 private data responsibly.A vital principle among these is compliance, which requires the database to verify that it adheres to its declared data disclosure policy.
Consider Alice who gets a blood test done at Healthco, a company whose privacy policy stipulates that it does not release patient data to external parties without the patient's consent.After some time, Alice starts receiving advertisements for an over-the-counter diabetes test.She suspects that Healthco might have released the information that she is at risk of developing diabetes.The United States Health Insurance Portability and Accountability Act (HIPAA) empowers Alice to demand from Healthco the name of every entity to whom Healthco has disclosed her information.As another example, consider Bob who consented that Healthco can provide his medical data to its affiliates for the purposes of research, provided his personally identifiable information was excluded.Later on, Bob could ask Healthco to show that they indeed did exclude his name, social security number, and address when they provided his medical record to the Cardio Institute.The demand for demonstrating compliance need not only arise from an externally initiated complaint -a company may institute periodic internal audits to proactively guard against potential exposures.
One approach to verifying that a database adheres to its disclosure policies might be to support data disclosure auditing by physically logging the results of each query.Problems with this approach include the following: it imposes a substantial overhead on normal query processing, particularly for queries that produce many results, and the actual disclosure auditing it supports is limited, since data disclosed by a query is not necessarily reflected by its output.
As an example of the limitations on disclosure auditing, consider P3P [5], which allows individuals to specify whether an enterprise can use their data in an aggregation.
Verifying that database accesses have been compliant with such user preferences is not possible given only a log of aggregated results.To address this issue, one might instead consider logging the tuples "read" by a query during its execution instead of its output.However, determining which tuples accessed during query processing were actually disclosed is non-trivial.In addition, such a change dramatically increases logging overhead.

Our Contribution
We propose a system that can be used to audit whether the database system executed a query in the past that accessed the specified data.The ideal system should have the following properties: Non-disruptive: The system should put minimal burden on normal query processing.Fast and precise: The system should be able to quickly and precisely identify all the queries that accessed the specified data.Fine-grained: It should be possible to audit even a single field of a specific record.Convenient: The language for specifying data of interest should be intuitive and user friendly.The proposed audit system satisfies the above desiderata.Figure 1 shows the overall architecture of our system.During normal operation, the text of every query processed by the database system is logged along with annotations such as the time when the query was executed, the user submitting the query, and the query's purpose.The system uses database triggers to capture and record all updates to base tables in backlog tables for recovering the state of the database at any past point in time.Read queries, which are usually predominant, do not write any tuple to the backlog database.
To perform an audit, the auditor formulates an audit expression that declaratively specifies the data of interest.Audit expressions are designed to be identical to the SQL queries, allowing audits to be performed at the level of an individual cell of a table.The audit expression is processed by the audit query generator, which first performs a static analysis of the expression to select a subset of logged queries that could potentially disclose the specified information.It then combines and transforms the selected queries into an audit query by augmenting them with additional predicates derived from the audit expression.This audit query, expressed in standard SQL, when run against the backlog database yields the precise set of logged queries that accessed the designated data.Indices on the backlog tables make the execution of the audit query fast.

Assumptions
We make two fundamental assumptions: There are subtle ways in which the combination of the results of a series of queries may reveal certain information.For example, the statistical database literature [1] discusses how individual information can be deduced by running several aggregate queries and the database security literature [3] shows how covert channels can be used to leak information.We limit ourselves to the problem of determining if the specified data was disclosed by a single query when that query is considered in isolation.We also assume that the queries do not use outside knowledge to deduce information without detection.The SQL queries we consider comprise a single select clause.A large class of queries (including those containing existential subqueries) can be converted into this form [12]. Specifically, we consider queries containing selection, projection (including distinct), relational join, and aggregation (including having) operations.

Paper Layout
The rest of the paper is organized as follows.Section 2 provides the syntax of an audit expression.We then propose the concept of an indispensable tuple, which in turn is used to identify suspicious queries with respect to an audit expression.Section 3 describes the system structures needed to support the proposed auditing capability.Specifically, we discuss the use of triggers to implement recovery of past database states.We also provide temporal extensions used to support the execution of an audit query, and give details of the query log.Section 4 states the algorithm for generating the audit query from an audit expression.Section 5 presents performance results, Section 6 discusses related work, and Section 7 concludes with a summary and directions for future work.

Definitions
We have a database D, which is a collection of base tables.We denote the scheme of table T as T C 0 ¡ C 1 ¡ £¢ £¢ ¤¢ ¥¡ C m¦ and use t ¢ C to refer to the value of the field C in tuple t.We will use the following schema in our examples: Customer (cid, name, address, phone, zip, contact) Treatment (pcid, date, rcid, did, disease, duration) Doctor (did, name) The primary keys have been underlined.A customer can be a patient, someone accepting financial responsibility for a patient's treatment, or an emergency contact.The Treatment table uses pcid to identify the patient receiving the treatment and uses rcid to identify the customer assuming financial responsibility for the treatment (who could be the same person as the patient).The date is the start date of the treatment and duration reflects the length of the treatment.
Other column names are self-explanatory.To simplify exposition, we will assume that the database has referential integrity and that no field value is null.

Audit Expressions
We propose to use expressions that are very close to SQL queries to enable an auditor to conveniently specify the queries of interest, termed suspicious queries.
Specifically, the proposed syntax of an audit expression is identical to that of a select-project-join (SPJ) query without any distinct in the select list, except that audit replaces the key word select and the elements of the audit list are restricted to be column names: Let U be the cross product of all the base tables in the database.The audit expression marks a set of cells in the table U.The marked cells belong to the columns in the audit list for the tuples that satisfy the predicate in the where clause.We are interested in finding those queries that access all the marked cells in any of the tuples in U.These are the suspicious queries with respect to the audit expression.

Example 1
We want to audit if the disease information of anyone living in the ZIP code 95120 was disclosed.Here is the audit expression: This audit expression marks the cells corresponding to the disease column of those tuples in the Customer Treatment table that have c.cid = t.pcid and c.zip = 95120.Any query that accesses the disease column of any of these tuples will be considered suspicious.

Informal Definitions
We introduce the notion of the indispensability of a tuple and then use it to define suspicious queries.We see that Q is a candidate query with respect to A as it accesses the disease column that A is auditing.Consider the Customer Treatment table.Clearly, tuples that match the join condition and have diabetes in the disease column are indispensable for Q.Similarly, tuples that match the join condition and have 95120 as the zip code are indispensable for A. Therefore Q will be deemed suspicious with respect to A if there was some customer who lived in the ZIP code 95120 and was also treated for diabetes.

Informal Definition 1 (Indispensable Tuple
Example 3 Consider the query Q from Example 2 and the following audit expression A: audit address from Customer c, Treatment t where c.cid = t.pcid and t.disease = 'cancer' Q will not be deemed suspicious with respect to A because no tuple in Customer Treatment can simultaneously satisfy the predicates of Q and A. But how about Alice who has both cancer and diabetes?Although Q discloses Alice's address, the fact that Alice has cancer is not relevant to the query: Q only asks for people who have diabetes.In other words, anyone looking at the output of the query will not learn that Alice has cancer.Hence it is reasonable to not consider the query to be suspicious.Note that all the tuples of Customer Treatment marked by A have cancer in the disease column and Q does not access any one of them.

Formal Definitions
Let the query Q and audit expression A be of the form: where T , R , S are virtual tables of the database D, that is, cross products of base tables: The operator π is the multi-set projection operator that preserves duplicates in the output (as opposed to the relational projection operator π which eliminates duplicates).Note that T is common to Q and A.
We denote by C Q the column names that appear anywhere in a query Q, and by C OQ the column names appearing in the select list of Q.Similarly, C OA denotes the column names present in the audit list of an audit expression A. P Q denotes the predicate of the query and P A is the predicate of the audit expression.We refer to the tuples of any virtual table as virtual tuples.
We now formalize the definition of indispensability, for all classes of queries of interest.Specifically, we discuss (a) SPJ queries, (b) queries with aggregation without having, and (c) queries with aggregation and having.

Indispensability -SPJ queries
Consider first a SPJ query that does not contain a distinct in its select list.This case is the most important case on which the rest of the cases will be based.For such queries, the form of the query of Eq. ( 1) is specialized to: We can now formalize the definition of an indispensable tuple for an SPJ query: Proof From Definition 1, we have Since the projections π maintain the duplicates, we have Queries with distinct in the select clause produce a duplicate-free table.Such queries have the form Q ¢ π C OQ σ P Q T R ¦ ¦ ¢ Let Q3 be the SPJ query obtained from the original query Q after removing distinct from the query text.Then, we have the following definition: The motivation for this definition will become apparent after the upcoming discussion of aggregation queries.Queries with distinct can be viewed as a special case of aggregation, the aggregation function being the first tuple in a group.
We can state succinctly: Observation 1 Duplicate elimination does not change the set of indispensable tuples for an SPJ query.

Indispensability -Aggregation without having
The definition of indispensability of a tuple for an aggregation query requires extra care.Consider a query that computes average salary per department.If Alice happens to have exactly the average salary of her department and her tuple is omitted, the query result will not be affected.However, it will be wrong to treat Alice's tuple as dispensable because the privacy systems such as P3P allow individuals to opt out of the use of their values in the computation of an aggregation.
The form of the query of Eq. ( 1) for an aggregation query without a having clause is specialized to: where gby are the grouping columns and agg represent aggregations like avg(duration), count(disease).
Consider the query Q3 that is a version of Q, but without aggregations.That is, Q3 has exactly the same from and where clauses, and a select clause with the same columns as Q, but without the aggregation functions.Note that the columns used in agg (e.g.duration, disease) are included in the select list of Q3 .

Definition 3 (Indispensability -Aggregation) A (virtual) tuple v is indispensable for Q if and only if it is indispensable for the aggregate-free version Q3 .
Example 4 Consider the following query that outputs average duration of diabetes treatment by doctor: We find that every Treatment tuple having diabetes in the disease field is indispensable.Thus the fact that the duration values of these tuples were used in computing the output is not lost.
The following is immediate:

Indispensability -Aggregation with having
We will use the query in following example to help with the explanations.The general form of an aggregation query Q that includes a having clause can be written as: Compared to Eq. ( 5), we now have an extra having predicate P H (avg(duration) 100 in Example 5).Any group that does not satisfy this predicate is not included in the result of Q, which implies that any tuple belonging to a group that gets filtered out by P H is dispensable.
Let Q3 be the having-free version of Q, obtained by simply removing the having clause from Q.

Definition 4 (Indispensability -Aggregation with having)
A (virtual) tuple v is indispensable for Q if and only if it is indispensable for Q3 and it belongs to a group that satisfies the having predicate P H .
We will again recast indispensability in terms of an SPJ query.Define a group table G as: For our example query, G will have two columns: name and avg(duration).It will have as many tuples as there are doctors who treat diabetes.Every tuple will have the average duration of diabetes treatment for the corresponding doctor.
Next form the following table: where P G is the natural join condition on the group-by columns, gby.We have augmented the result tuples of the having-free version of Q with the corresponding group values.The query Q can now be computed from σ P H QG ¦ .

Suspicious Queries
We first define a maximal virtual tuple for queries Q1 and Q2.

Definition 5 (Maximal Virtual Tuple) A tuple v is a maximal virtual tuple for queries Q1 and Q2, if it belongs to the cross product of common tables in their from clauses.
We can now formalize the definitions of candidate and suspicious queries.

Definition 6 (Candidate Query) A query Q is a candidate query with respect to the audit expression A if and only if
Definition 7 (Suspicious Query) A candidate query Q is suspicious with respect to audit expression A if they share an indispensable maximal virtual tuple v, that is: is the cross product of the common tables in Q and A.

System Structures
We now discuss the system structures needed to handle audits in the presence of updates to the database.

Full Audit Expression
The audit expression is prepended with an additional during clause that specifies the time period of interest:

Query Log
As shown in Figure 1, the audit system maintains a log of past queries executed over the database.The query log is used during the static analysis to limit the set of logged queries that are transformed into an audit query.
Our prototype implementation has a thin middleware that lies between the application and the database engine.This middleware has been implemented as an extension to the JDBC driver.The middleware intercepts queries and writes the query string and associated annotations to the log.We assume the isolation level of serializable [8] and log only queries of committed transactions.
The annotations include the timestamp of when the query finished, the ID of the user issuing the query, and the purpose and the recipient information extracted from the context of the application [10,11] in which the query was embedded.The query log is maintained as a table.
Note that some database systems (e.g., DB2) provide the facility for logging incoming queries.This capability can be extended to log additional information required for auditing.

Temporal Extensions
We determine if a candidate query Q accessed the data specified in an audit expression by selectively playing back history.We thus need to recreate the state of the database as it existed at the time Q was executed.A backlog database [9] is eminently suited for this purpose.
We describe two organizations for the backlog database: time stamped and interval stamped.In both the organizations, a backlog table T b is created for every table T in the database.T b records all updates to T .We will assume that every table T has a primary key column P; the system can create an internally generated key column otherwise.

Time stamped Organization
This organization is based on the ideas presented in [9].Aside from all columns in T , a tuple in T b has two additional columns: TS that stores the time when a tuple is inserted into T b , and OP that takes one of the values from 'insert', 'delete', 'update'! .For every table, three triggers are created to capture updates.An insert trigger responds to inserts in table T by inserting a tuple with identical values into T b and setting its OP column to 'insert'.An update trigger responds to updates to T by inserting a tuple into T b having the after values of the tuple in T and setting its OP column to 'update'.A delete trigger responds to deletes in T by inserting into T b the value of the tuple before the delete operation and setting its OP column to 'delete'.In all the three cases, the value of the TS column for the new tuple is set to the time of the operation.
To recover the state of T at time τ, we need to generate the "snapshot" of T at time τ.This is achieved by defining a view T τ over the backlog table T b : The scheme for T τ is identical to T .T τ contains at most one tuple from T b for every distinct primary key value P.Among a group of tuples in T b having an identical primary key value, the selected tuple t is the one that was created at or before time τ, is not a deleted tuple, and there is no other tuple r having the same primary key value that was created at or before time τ but whose creation time is later than that of t.

Interval stamped Organization
In this organization, the end time (TE) of a tuple is explicitly stored in addition to the start time (TS).Thus, the combination of TS and TE for a tuple gives the time period during which the tuple was alive.A null value of TE is treated as current time.The operation field (OP) is no longer necessary.
When a new tuple t is inserted into T , the insert trigger also adds t to T b , setting its TE column to null.When a tuple t ¡ T is updated, the update trigger searches for the tuple b ¡ T b such that b ¢ P ¢ t ¢ P £ b ¢ TE ¢ null and sets b ¢ TE to the current time.Additionally, the trigger inserts a copy of t into T b with updated values and its TE column set to null.When a tuple t is deleted from T , the delete trigger searches for b ¡ T b such that b ¢ P ¢ t ¢ P £ b ¢ TE ¢ null and sets b ¢ TE to the current time.

Indexing
We propose two strategies for indexing a backlog table T b : 1. Eager: Index is kept fresh and updated every time T b is updated.
2. Lazy: Index is created afresh at the time of audit.Otherwise, T b is kept unindexed.
The advantage of the eager strategy is that there is no latency at the time of audit due to the time needed to build the index.However, an update during normal query processing is burdened with the additional overhead of updating the index.The trade-off is reversed in the lazy strategy.We can also choose which columns are indexed.We can index the primary key.We can also create a composite index consisting of the primary key concatenated with the timestamp.We explore the performance trade-offs in managing backlog tables in Section 5.

Algorithms
The audit query is generated in two steps: 1. Static Analysis: Select candidate queries (i.e., potentially suspicious queries) from the query log. 2. Audit Query Generation: Augment every candidate query with information from the audit expression and combine them into an audit query that unions their output.

Static Analysis
For a given audit expression A, some queries will be judged as non-candidates, and excluded immediately.We use four static tests, explained next.The query log is indexed to make these tests fast.
The first is by comparing the attribute names: with audit columns C OA , we simply check whether C Q ¡ C OA .The second test checks whether the timestamp of query Q is out of range with respect to the audit interval in the during clause of A. The third test checks whether the purposerecipient pair of Q matches any of the purpose-recipient pairs specified in the otherthan clause of A. Finally, we can eliminate some queries by checking for contradictions between the predicates P Q and P A , such as P Q ¢ age 40 ¦ and P A ¢ age 20 ¦ .This class of tests is an instance of the constraint satisfaction problem, for which many solution techniques are available [6].

Audit Query Generation
At the end of static analysis, we have a set of candidate with respect to the audit expression A. We augment every Q i with information in A, producing another query AQ i defined against the view of the backlog database at time τ i , where τ i is the timestamp of Q i as recorded in the query log.If we were to execute these AQ i queries, those with non-empty results will comprise the exact set of suspicious queries.However, to increase opportunities for optimization, all AQ i are combined into one audit query AQ whose output is the union of the output of the individual AQ i .This audit query is the one that is executed against the backlog database.
To simplify exposition, we will assume henceforth that Q has only one query Q and discuss how it is transformed

Simple Selections
Consider first the simple case of a candidate query Q involving a selection over a single base table T and the audit expression A over the same table.This case is a special case of the upcoming SPJ queries.However, we present it for pedagogical reasons.

is suspicious with respect to A if and only if σ
Proof From the upcoming Theorem 2, by substituting T for T , and ignoring the non-existing R and S.

2
Thus, given that query Q has passed the static analysis, we need to check whether the combined selection σ P A σ P Q T ¦ ¦ is empty or not, which is what Figure 2 implements using the QGM representation.We illustrate the audit query generation algorithm using the following example.
Example 6 Candidate query Q: Retrieve all customers in ZIP code 95120.

select * from
Customer where zip = '95120' Audit expression A: Find queries that have accessed Alice's name and address.
1 QGM [12] is a graphical representation that captures the semantics of queries and provides convenient data structures for transforming a query into equivalent forms.QGM is composed of entities portrayed as boxes and relationships among entities portrayed as lines between boxes.Entities can be operators such as table, select, group, union, etc. Lines between operators represent quantifiers that feed an operator by ranging over the output of the other operator.Figure 3(a) shows the state of the QGM graph after Line 3. A new QGM structure for the audit query AQ has been created and both the candidate query Q and the audit expression A have been added to AQ. Figure 3(b) shows the state of QGM after Line 6. Line 4 has changed the audit expression's quantifier (range variable) C 2 from ranging over the Customer table to ranging over the result of the query Q.As part of this transformation, each column referenced by C 2 is changed to reference a column of Q's output.If a column referenced by C 2 is not in the output of Q, it is propagated up from the Customer table to be included in the Q's select list.Line 5 replaces the audit list with Q's id: Q1.Finally, Line 6 replaces the Customer table with a view of the Customer table at time τ when Q completed.

SPJ Queries
Consider now the case when the candidate query as well as the audit expression contain joins in the WHERE clauses.
1) create an empty QGM for the audit query AQ 2) add Q to AQ 3) add A to AQ 4) rewrite A to additionally range over the result of Q with quantifier x 5) for each quantifier r in A which is over a table T also in Q 6) substitute x in place of r in A 7) replace A's audit list with id Q¢ 8) replace every table T i referenced in AQ with T τ i .
Figure 4: Audit query generation when both the candidate query and the audit expression contain joins The audit list may contain columns from multiple tables and the join condition in the candidate query may be different from the one in the audit expression.
is suspicious with respect to an audit expression A ¢ π C OA σ P A T S ¦ ¦ if and only if Proof According to our Definition 7, we have Figure 4 gives the algorithm.Note that an audit expression A may have multiple quantifiers, only some subset of which may range over a table that also appears in query Q.These are the only ones for which A is made to range over the result of the query (Lines 5-6).For others, A continues to range over the original tables.We illustrate the algorithm using the following example.

Aggregation
To determine if an aggregate query without a having clause is suspicious, aggregate functions are simply removed along with the group by clause.Columns previously referenced by aggregate functions are added to the select list of the query.The resulting SPJ query is then handled using the algorithm given in Figure 4.
If the aggregate query, however, additionally contains a having clause, the predicate therein might have eliminated the data specified by the audit expression from the query result.Simply removing the having clause can thus lead to false positives.This limitation is overcome by the algorithm given in Figure 6, which is based on the upcoming theorem.
Recall that the general form of such a query is given by Eq. ( 6): Q ¢ σ P H gby γ agg σ P Q T R ¦ ¦ ¢ By Eq. ( 7), the group table G ¢ gby γ agg σ P Q T R ¦ ¦ .As always, the audit expression is Proof From Observation 3, the query Q has the same indispensable tuples as the SPJ query Q3 below: Then, from Theorem 2 we have that Q3 is suspicious if and only if Eq. ( 10) holds.

2
An aggregate query with a having clause can be viewed as consisting of three parts: Q s , Q g , and Q h .The first part, Q s , ignores grouping and aggregation and finds the tuples qualifying the WHERE clause.Grouping and aggregations are then applied to this result in Q g .Finally, any predicates on groups are applied using a selection operator over the result of grouping and aggregation in Q h .A new select box is created on Line 8 in Figure 6.This operator joins the tuples emanating from Q h with those from A to ensure that these A tuples were not all filtered out by the having predicates in Q h .We illustrate the algorithm with Example 8. Audit expression A: Find queries that have accessed the disease and treatment duration of patients who have diabetes and live in ZIP code 95120.The QGM for the candidate query Q in Example 8 integrated with the audit expression is shown in Figure 7(a).Figure 7(b) shows the audit query.The select box B ensures that the groups formed by the grouping operator that survived the having predicate match the audit expression's data.

Performance
This section presents the results of performance experiments from a DB2 implementation of our auditing solution.Specifically, we study the overhead imposed on normal query processing, and the cost of conducting audits.
Experiments were performed on an IBM Intellistation M Pro 6868 having an 800 MHz Pentium III processor, 512 MB of memory, and a 16.9 GB disk drive, and running Windows 2000 Version 5.00.2195 service pack 4. The same machine was used to host data as well as backlog tables and to run audits.The DBMS used was DB2 UDB Version 7 with default settings.We would have liked to use real-life query logs and data tables, but no such dataset was available.We therefore performed our experiments on the Supplier table of the TPC-H database [15], using synthetic workload.The results below give the average warm performance numbers.
We report results for time stamped as well as interval stamped organizations of the backlog tables.We consider three cases: no index, simple index on the supplier key SKEY, and composite index on SKEY and start time TS.We explore both eager and lazy strategies for updating indices.
We will write Supplier b to refer to the Supplier backlog table.In the performance graphs, TS (IS) denotes the time stamped (interval stamped) organization.

Burden on Normal Query Processing
We performed the following experiment to study the overhead of maintaining backlog tables.The Supplier table contained 100,000 tuples and Supplier b started with a copy of every Supplier tuple.An SQL update statement updated every Supplier tuple, resulting in the creation of a new version of every supplier in Supplier b .Forty nine such update statements were executed, each adding 100,000 tuples to Supplier b that finally ended up having fifty versions of every supplier and a total of 5 million tuples.Indices of Supplier b were updated eagerly.
The update operation on the Supplier table took 5.2 minutes to complete when performed unburdened with the maintenance of Supplier b .This time essentially consists of sequentially reading the Supplier tuples and writing them back after updating one of the values.Figure 8 shows the ery updated data tuple.Therefore, when there is no index on Supplier b , the overhead experienced by successive updates remains fairly constant.When there is an index on SKEY, the overhead is a bit larger due to additional index updates, and this overhead increases a little for the later updates because the size of the index grows.The composite index on SKEY and TS obviously has a somewhat larger overhead than the simple index.
For equivalent operation with the interval stamped organization, the update trigger first locates the most recent version of the tuple, updates its end time, and then adds a new current tuple.Unfortunately, the cost of locating the most recent version becomes prohibitively large when there is no index; hence it is not shown in the figure.Even when there is an index on SKEY, all the versions of a tuple need to be brought into memory to select the most recent of them.If different versions of a supplier do not remain clustered on the same page (which we found to be the case even when we had a clustered index on SKEY), the number of page faults increases with the number of versions, resulting in a rapid degradation of performance.Having the additional index on TS does not help in cutting down the number of versions that are examined before the most recent one is found.On the other hand, the overhead increases somewhat due to additional index updates and a larger index.
It is very fast to sequentially update all the tuples of a table in DB2.Thus, updating all the tuples of the Supplier table with backlog maintenance using the time stamped organization is about 10 times slower than without maintenance.We next performed another experiment in which only one Supplier tuple was updated.Supplier b had 25 versions of every tuple in this experiment.The cost of an update to a single tuple with backlog maintenance was now on average 3 times the cost of the same update without maintenance when using the simple index, and 3.7 times when using the composite index.
We note that in most of the installations, there are far more read queries than update operations.Updates are often batched and performed while the system is under a light load.The read queries in our audit system do not incur any overhead beyond logging the query string (which is anyway

Eager vs. Lazy Indexing
It is clear from the discussion in the previous section that the lazy indexing is not a viable option for the interval stamped organization.However, the overhead of eagerly updating the indices can be avoided in the time stamped organization by building them as needed at the time of audit.Figure 9 shows the time needed to build indices from scratch with an initial set of 100,000 suppliers in Supplier b , while increasing the number of versions of each supplier.The results are shown both for a simple index on SKEY and for a composite index on SKEY and TS.
We see that the index construction times are such that it would be acceptable to adopt the lazy strategy and strictly create indices at audit time.

Performance of Audit
We study the audit performance by measuring the execution time of simple audit queries.The audit expression is of the form: during t 1 to t 2 audit name from Supplier where skey = k We set both t 1 and t 2 to the time when the initial versions of the Supplier tuples were created.The value of k is randomly set to one of the values of SKEY present in the Supplier table.We assume that static analysis has yielded one candidate query: select * from Supplier.
We consider both time stamped and interval stamped organizations, with simple as well as composite indices.Both the time stamped and interval stamped organizations benefit a great deal from the composite index on SKEY and TS as the number of versions becomes large.When there is an index only on SKEY, the query plan first selects all versions of a tuple with the matching supplier key and then selects the correct version amongst the matching tuples.These versions might reside on different disk pages and cause page faults as Supplier b becomes large.The composite index avoids this problem.
We also see that with few versions of a tuple, the interval stamped organization has a slight performance advantage, but loses this advantage as the number of versions increases.The interval stamped organization requires an extra timestamp attribute to record the end time of validity of a tuple.The larger tuple size results in more page faults as the number of versions increases, and thus the impact of the larger size outweighs the benefit of the simpler interval stamped computation over the time stamped organization that requires a join.

Take Aways
The composite index on the primary key and start time pays large dividend over an index only on the primary key at the time of audit, although it puts a slightly larger burden on updates if the indices are updated eagerly.
The interval stamped organization has a slight advantage over the time stamped organization at the time of audit if the number of versions is small.However, the lazy strategy for updating indices cannot be used with the interval stamped organization and eager updating becomes quite expensive as the number of versions increases.Overall, the use of time stamped organization along with the lazy strategy for updating indices is recommended.However, the eager strategy is also not too burdensome for the time stamped organization.
The system supports efficient auditing without substantially burdening normal query processing tasks.

Related Work
Closely related to compliance is the privacy principle of limited disclosure, which means that the database should not communicate private information outside the database for reasons other than those for which there is consent from the data subject [2,10].Clearly, the two are complimentary.The principle of limited disclosure comes into play at the time a query is executed against the database, whereas demonstrating compliance is post facto and is concerned with showing that usage of the database indeed observed limited disclosure in every query execution.
Oracle [11] offers a "fine-grained auditing" function where the administrator can specify that read queries are to be logged if they access specified tables.This function logs various user context data along with the query issued, the time it was issued, and other system parameters including the "system change number".Oracle also supports "flashback queries" whereby the state of the database can be reverted to the state implied by a given system change number.A logged query can then be rerun as if the database was in that state to determine what data was revealed when the query was originally run.There does not appear to be any auditing facility whereby an audit predicate can be processed to discover which queries disclosed data specified by the audit expression.Instead, Oracle seems to offer the temporal database (flashback queries) and query logging (finegrained auditing) components largely independent of each other.
The problem of matching a query against an audit expression bears resemblance to the problem of predicate locking [7] that tests if the predicates associated with two lock requests are mutually satisfiable.Besides being expensive, this test can lead to false positives when applied to the auditing problem.Related work also includes the literature on query processing over views that contains the notion of augmenting a user query with predicates derived from the view definition [13].Also related is the work on optimizing a group of queries (e.g.[4,14]) that can be profitably used by our system to accelerate the execution of audit queries.

Summary
We identified the problem of verifying whether a database system is complying with its data disclosure policies through auditing.Given the accelerated pace at which legislations are being introduced to govern data management practices, this problem represents a significant opportunity for database research.We formalized the problem through the fundamental concepts of indispensability and suspiciousness.Additional contributions include a carefully designed and implemented system that meets the design goals identified in the introduction: Convenient: The audit expression language used by our system reuses the familiar SQL syntax, providing a familiar, declarative and expressive means for specifying the data whose disclosure is subject to review.Fine-grained: The audit expression language allows the auditor to specify even a single field of a record as subject for review.Fast and precise audits: Our system combines the audit expression with logged queries into an SQL audit query which examines only the specific data necessary to determine suspiciousness.Guided by our implementation and experimentation with various backlogging and indexing strategies, we proposed system structures to support efficient audit query execution.Non-disruptive: Our system imposes only a small burden on the execution of most queries.Rather than logging query results or the tuples accessed by a query, it logs the query strings.While update operations require some additional backlog database maintenance, the predominant read queries are processed without any further encumbrance.
We considered a model of data disclosure in which what a querier learns is restricted to the information gained from the current query assuming the querier does not possess any outside knowledge.It would be interesting to see how our framework could be extended to support more adversarial disclosure scenarios.Other remaining work includes how schema evolution can be gracefully accommodated in the audit system.Finally, we feel it would be beneficial to the community to develop a set of comprehensive benchmarks for measuring and testing the effectiveness and performance of any database auditing proposal.

Figure 1 :
Figure 1: The system architecture 1.1 Our Contribution audit disease from Customer c, Treatment t where c.cid = t.pcid and c.zip = '95120' select name, avg(duration) from Doctor d, Treatment t where d.did = t.did and t.disease = 'diabetes' group by name Indispensability of a tuple t in the the above query is determined by considering the indispensability of t in the following SPJ query: select name, duration from Doctor d, Treatment t where d.did = t.did and t.disease = 'diabetes' during start-time to end-time audit audit-list ¥ ¦¥ ¦¥ Only if a query has accessed the data of concern during the specified time period is the query deemed suspicious.Privacy policies specify who is allowed to receive what information and for what purpose [2, 5].An audit expression can use the otherthan clause to specify the purposerecipient pairs to whom the data disclosure does not constitute non-compliance: otherthan purpose-recipient pairs during start-time to end-time audit audit-list ¥ ¦¥ ¦¥ table at time (b) After Line 6 in Figure2

Example 7
Figure 5(a)  shows the initial QGM (after Line 3) and Figure5(b) shows the final QGM (after Line 7).In the final QGM, the audit expression ranges over the result of the query and then joins the results with the Customer table since Customer only appears in the audit expression.

Figure 6 :
Figure 6: Audit query generation for an aggregate query containing having Theorem 3 A candidate query Q with aggregation and having is suspicious with respect to an audit expression A if and only if

Example 8
Candidate query Q: Compute the average treatment duration grouped by disease and the doctor performing the treatment for treatments which were between 01/01/2001 and 31/12/2003 having a minimum duration 100.select D.name, T.disease, avg(T.duration)from Doctor D, Treatment T where T.date between '01/01/2001' and '31/12/2003' and D.did = T.did group by D.name, T.disease having min (T.duration) 100 audit T.disease, T.duration from Customer C, Treatment T where C.cid = T.pcid and C.zip = '95120' and T.disease = 'diabetes'

Figure 9 :
Figure 9: Cost of building indices over the backlog table.

Figure 10 :
Figure 10: Execution time of an audit query done in many installations).
Figures 10 shows the results.

its omission makes a difference. Informal Definition 2 (Candidate Query -cand
Q is a simple selection query over a single table T , executed at time τ.// A is an audit expression over the same table T . 1) create an empty QGM for the audit query AQ 2) add Q to AQ 3) add A to AQ 4) rewrite A to range over the result of Q instead of T 5) replace A's audit list with id Q¢ 6) replace T with the view T τ // 6)gregations ranging over Q s , and // Q h is a selection over Q g representing having.1)createanempty QGM for the audit query AQ 2) add Q to AQ 3) add A to AQ 4) rewrite A to additionally range over the result of Q s with quantifier x 5) for each quantifier r in A which is over a table T also in Q s6)substitute x in place of r in A 7) replace the audit list of A with the grouping columns of Q g 8) create a new empty select box B and add it to AQ 9) add Q h , A as inputs to B 10)join inputs of B on grouping columns from Q h and A 11)replace Π B¢ with id Q¢ 12)replace every table T i referenced in AQ with its backlog counterpart T τ i at time τ.