Recording access to sensitive data

Today there is considerable interest in access auditing, and a requirement in some jurisdictions for companies to record use of sensitive data.

Pyrrho already indelibly records changes to all data together with the user/role and time of changes, and watches reading of data during transactions to construct simple constraints on a transaction being committed. 

As a matter of fact, few experts agree with this feature, since it means the inclusion of a read operation in a transaction will prevent the transaction being committed a concurrent transaction commits a modification of any of the data that has been read. Pyrrho enforces this approach however, because the user whose transaction is prevented from committing is the same one who (presumably deliberately) included the read operation in the transaction.

The present discussion also considers read operations but in a different way. Here we want to distinguish sensitive data (say at the data type level) and immediately record access to it (by anyone other than the database owner), whether or not the current operation is ever committed.

These features [updated: 27 November] are available in Pyrrho version 6.3. The audit record is merged into the transaction log and a system table gives access to all the details.
From the manual:
Sec 1.5: 

Version 6.3 adds support for “sensitive” data, for which any access is auditable. Columns, domains and types can be declared SENSITIVE[1]. Sensitive values are not assignment-compatible with anything that is not sensitive, and there is a sensitive property inherited by any object that contains a sensitive data type. This means for example that the sum of sensitive data is still sensitive. The transaction log will contain a record of every access to sensitive values (apart from by the database owner), even if the transaction is rolled back. These details are visible in the Sys$Audit system table (see section 8.3.1).

Sec 7.4:

Type =  (StandardType | DefinedType | Domain_id | Type_id | REF’(‘TableReference’)’) [UriType] [SENSITIVE] .

Sec 8.3.1:

8.3.1 Sys$Audit

Field	DataType	Description
Pos	Char	The location of this access record in the transaction log
User	Char	The defining position of the accessing user
Table	Char	The defining position of the sensitive table or view object
Timestamp	Int	The time of the access in ticks

Audit records are only for committed sensitive data. Entries come from physical Audit records, and are added immediately on access (do not wait for transaction commit).

8.3.2 Sys$AuditKey

Field	DataType	Description
Pos	Char	The location of the access record in the transaction log
Seq	Int	The ordinal position of the key (0 based)
Col	Char	The defining position of the key column
Key	Char	A string representation of the key value at this position

Key information for audit records comes from the filters used to access a sensitive object. For example, if a record is inserted in a table, there is no applicable filter, the audit record will apply to the whole table, and there will be no key information here.

Comments by email are welcome.

---

[1] SENSITIVE is a reserved word in SQL that normally applies to cursor sensitivity. The usage in Pyrrho described here is quite different, and the keyword comes at the end of a type clause (see section 7.4).