Auditing SQL{3}

This week I will be blogging about the journal entry “Auditing a Batch of SQL Queries” by Rajeev Motwani, Shubha U. Nabar, and Dilys Thomas of Stanford University. This Journal entry talked about ways SQL Queries are audited and how to determine suspicious SQL queries. The Journal makes mention of a command AUDIT, which I had never seen before. They use it an example:

“AUDIT zipcode
FROM Patients p
WHERE p.disease = ‘high blood pressure’ ” (Motwani, 2007).

In this example the command Audit is interested in finding out if the zipcode of any Patient with high blood pressure was revealed. This command is used to help determine whether a query is suspicious such as the example considering keeping the phone numbers of patients completely  private. In this example a query asks for the size of the database, this does not seem suspicious initially, however this reveals the amount of phone numbers the database hold. Thus this can be considered suspicious if “perfect privacy” is wanted.

The Journal entry was a it technical at points especially when speaking about certain theorems and algorithms to find queries that would disclose private information such as:

“A tuple t ∈ T is indispensable to a query Q
if the presence or absence of t makes a difference to the result of Q, i.e. πCQ(σPQ (T )) =
πCQ(σPQ (T − {t}))” (Motwani, 2007).

After rereading some of these theorems a few times I could make sense of them and how they would work in the logic of creating a database. Security of the information in a database is vital and if something should remain perfectly private in the database, then there should be no way to access said information. The Journal does a good job of showing how multiple queries could lead to the disclosure of private information such as in the following example where they are attempting to keep patients medical history private:

“SELECT zipcode

FROM Patients p

WHERE p.disease = ‘diabetes’

FROM Patients p
WHERE p.zipcode = 94305″ (Motwani, 2007).

These two statements independently would not reveal anything, however combined these statements could reveal a patient and a disease the patient has. Examples like this shows how important it is to keep security and the protection of private information in mind when create databases.


Motwani, R.; Nabar, S.U.; Thomas, D.; , “Auditing a Batch of SQL Queries,” Data Engineering Workshop, 2007 IEEE 23rd International Conference on , vol., no., pp.186-191, 17-20 April 2007