SQL Auditing
Team i-Protect
December 10, 2009
Introduction
We introduce an auditing algorithm for determining whether a database system is adhering to its data disclosure policies [3]. Consider Bob (homeless, diabetic guy) goes through the rehabilitation process (g:1 ). He consented that his information can be used by CAISI for doing population level research. After some time, he started receiving advertisements for over the counter diabetes test. Now, the question arises whether this is a coincidence or his information is leaked by someone at CAISI. He goes back to CAISI and questions their privacy protection policy. How CAISI can satisfy Bob? Currently, CAISI is using two rules while doing population level research.
1. Data used for research are de-identied.
2. If a query returns less than 5 rows, it is not executed.
It is obvious, that these two conditions are not sucient enough. For example, a query : select facility from client c, disease d where c.id = d.id and c.age > 90 and d.disease = `diabetic' executed on the database (g:2 ) will return more than 5 rows and does not access any private information. The result (g:2 ) has only one row with shelter A, now it is trivial to identify a person above the age of 90 at shelter A and we now know that he is suering from diabetes.
SQL Auditing
This work is based on the previous research conducted in this eld [1, 2, 3, 4, 5]. 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 specied data during their execution [6].
Denition
Indispensable tuple: A tuple is indispensable in the computation of a queryQ,
Figure 1: Story of homeless, diabetic Bob.
Candidate Query: A query Q is a candidate query w.r.t an audit expression A, if and only if
C(Q)⊇C(A)
where, C is the column. The correctness of this denition comes from the
fol-lowing argument. Suppose, that a queryQand audit expressionAdo not share
a column. Then, by removing all the columns belonging toAfrom the database
table will not eect the result of queryQ. Therefore, for a queryQto be termed
as candidate it has to share some column with audit expressionA.
Suspicious Query: A candidate query Q is suspicious w.r.t audit expression Aif they share an indispensableI maximal virtual tuplev.
S(Q, A)⇐⇒ ∃v∈τ s.t. I(v, Q)∧I(v, A)
where, τ = T1×T2 ×...×Tn is the cross product of common tables in Q
andA. The correctness of this denition is clear from the following argument.
Suppose, that the candidate queryQand audit expressionAdoes not share any
indispensable tuple. Then there can be only two possibilities. 1) They do not
share any tuple, in that case even if we remove all the tuples belonging to A
from database, result ofQwill not change. 2) They share dispensable tuples,
in this case also, upon removing all the tuples belonging to A from database,
result ofQwill not change. Therefore, for a candidate queryQto be termed as
suspicious it has to share indispensable tuple withA.
Algorithm
1. Create an audit expression of the form: audit column from table where condition.
2. Find candidate queries with respect to audit expression.
3. From the set of candidate queries nd suspicious queries with respect to audit expression.
Discussion
SQL auditing algorithm helps in identifying suspicious queries in a given query log. Then by running these suspicious queries one can easily nd whether the database is adhering to its data disclosure policies or not. Continuing with the example of Bob presented in the introduction, we can create an audit expression and test whether the any of the queries present in the query log are suspicious or not (g:3 ).
Figure 3: Results obtained after running SQL auditing algorithm. Query1and 3are found to be candidate queries. Query1 is then found to be suspicious.
Usage
The application is written in java. It is only a prototype without any user in-terface. File auditing/querylog consists of all the SQL queries. auditing/disease and auditing/client are the les (csv) with database table information. The audit expression is given as input argument. On command line execute the fol-lowing command with audit expression.
java -jar auditing.jar "audit expression" For example,
java -jar auditing.jar "audit client.facility from client where client.age>90"
References
[1] N. Adam and J. Wortman. Security-control methods for statistical databases. ACM Computing Surveys, 21(4):515556, Dec. 1989.
[2] R. Agrawal, J. Kiernan, R. Srikant, and Y. Xu. Hippocratic databases. In 28th Int'l Conference on Very Large Databases, Hong Kong, China, August 2002.
[4] R. Motwani, S. U. Nabar, and D. Thomas. Auditing sql queries. In ICDE Workshop on Privacy Data Management, 2007.
[5] G. Miklau and D. Suciu. A formal analysis of information disclosure in data exchange. In SIGMOD, 2004.
[6] R. Ramakrishnan and J. Gehrke. Database ManagementSystems. McGraw-Hill, 2000.
