Explanation:
The identifier is actually for the key that encrypts the value (1 unique key per value).
1. When the value is encrypted for the first time, it gets an ID.
2. When its decrypted, the application requests the key for that ID from the key-server (key-server records that the data was accessed)*
3. When updating, the same data key is used so the ID is persistent*
* Technically the key server doesn't return a key, it generates partial key material that can be used to derive the data key in the app. It can do this at up to 10,000 keys per second.
* You can also tag each value with the table/column name and the row-id to link everything together
The 3 values form the "descriptor" of a value: `table/column/id`.
So the audit log contains an ID for a key which could decrypt a value - does the audit log also contain the encrypted value itself? If not, how do you go from the audit log back to the original value in 1?
No, not by default. You could but as you said, that would be a A LOT of data.
It depends on your setup. If you're using Supabase, one way is to send the logs to Clickhouse and use the Clickhouse partner integration to query the audit logs and join it to the actual data.
Keeping only the ids in the audit log means you need to stitch the data together later. This means you don't accidentally leak data via your audit trail!
> SQL query auditing is great for knowing what queries were run but it doesn't tell you what data was actually returned
With what you’re saying - sounds like cipher doesnt tell you what data was actually returned either - it will only tell you if the user could have received a decrypted version of the data right?
Query logging would tell you what queries were executed.
E.g the following would tell you that 10 user records were returned with name and email but not which users.
SELECT name, email FROM users LIMIT 10;
CipherStash can tell you the ID of every row that was returned, like:
If you need to know the actual values instead of just the IDs (say when doing an investigation), you can do a query to join the audit data with the users table.
Assuming audit logs are in a table called audit with a column called record_id and a timestamp:
-- full forensic analysis
SELECT name,email FROM audit
LEFT JOIN users ON users.id = audit.record_id
WHERE timestamp BETWEEN $1 AND $2;
And because the data is encrypted, that query will ALSO be audited. Not even admins escape the auditing :p
All of this is important for "materiality" investigations. If you suspect some data has been accessed inappropriately, knowing how much and exactly which records is very useful - especially if deciding if customer/regulator notification is required.
The answer to your question is yes.
Explanation: The identifier is actually for the key that encrypts the value (1 unique key per value).
1. When the value is encrypted for the first time, it gets an ID. 2. When its decrypted, the application requests the key for that ID from the key-server (key-server records that the data was accessed)* 3. When updating, the same data key is used so the ID is persistent*
* Technically the key server doesn't return a key, it generates partial key material that can be used to derive the data key in the app. It can do this at up to 10,000 keys per second. * You can also tag each value with the table/column name and the row-id to link everything together
The 3 values form the "descriptor" of a value: `table/column/id`.