5 ms·
You would keep a copy of every query result set? The storage requirements alone would be insane. Seems perfectly reasonable to load that day’s snapshot/rewind t
by closeparen 8y ago
You would keep a copy of every query result set? The storage requirements alone would be insane. Seems perfectly reasonable to load that day’s snapshot/rewind the WAL and rerun the query.
- deleted 8y ago[deleted]
- toomuchtodo 8y ago> You would keep a copy of every query result set? I am familiar with systems that do, and do not believe it to be an unreasonable ask depending on GRC [1] requirements. Storage is cheap, compression effective. [1] https://en.wikipedia.org/wiki/Governance,_risk_management,_and_compliance https://en.wikipedia.org/wiki/Governance,_risk_management,_a...
- hderms 8y agoHave you ever honestly worked on a system that logged the results of every query run?
- mberger 8y agoYes. In E-Health you have to be able say who and what was queried for. There's also https://en.wikipedia.org/wiki/Reliable_messaging https://en.wikipedia.org/wiki/Reliable_messaging We store every message and every response so you can tell what query was run and when.
- dogma1138 8y agoYou are confusing two concepts under HIPAA you are explicitly disallowed to log any EHR information. Record systems must be able to represent the exact EHR presented to a query e.g. to check if there was a human error like some one missreading the record. This is achieved by keeping record versions and a record history this isn’t achieved by having system logs and audit trails of queries returned. Essentially your EHR system would work like git you’ll be able to query the same commit as the original query but it doesn’t mean that your database audit trail would record any parts of the electronic health record that is simply not allowed.
- mberger 8y agoWe're Canadian so don't fall under HIPAA. We record in a database the full request and response for each transaction submitted. We index with the UUID for the message. With that request and response I can tell you exactly what each query returned because we only return the data that goes in the message. I can't tell you what the database contained at that point but I know what is in the message.
- saltcured 8y agoThe approach you are describing seems to extend the same access protection and audit requirements to the audit logs themselves. Otherwise, you have created a covert channel to access the private content by examining these logs rather than the original database. The other approach, of being able to replay a historical query described in a log while disallowing the private content in the logs allows the audit logs to be stored in a way that can be biased for better storage durability, without quite the same recursive audit nightmare. The logs are much smaller and can be easily replicated and archived, without quite the same level of risk from exposure of log content. Not to say those other logs are not also worth protecting, but there are different ways to balance the risks and costs...
- toomuchtodo 8y agoYes. Financial services.
- closeparen 8y agoThe observable functionality (what “requirements” ought to be specifying) is the same, no?
- dogma1138 8y agoYou do not store query returns in your audit log it’s not only a nightmare from performance and storage standpoint but it’s also disallowed by the same GRC requirements you are touting. The last thing you want is PII or other regulated date sitting in splunk in fact each large organization that handles such data will have systems to ensure that this does not happen as the regulatory requirement is simple - do not log sensitive data there are tons of both client side and server side plugins and tools for common logging frameworks and log aggregators that search and delete sensitive data or sanitize it before it being logged completely and usually you run a combination of both. On the storage/performance side since you need to keep access logs sometimes for years if you store query results you’ll need storage 1000’s and 1000’s of times the size of your DB to store those logs this isn’t feasible. What you log is often the query how many result did it return the user and or app that run the query.
- dsfyu404ed 8y agoI work at a similar sized company. It depends. If you ran a query on any of the schemas that have access to security and compliance sensitive data back in 2013 I can probably tell you what it returned (sans any PII, obviously we don't log stuff that would have compliance implications if we logged it) but I'd have to go digging in backups to find it.