Oncology App - Auditing

Dealing with personal and private data is difficult. You just don't need to know about inserts, updates and deletes (see previous blog on CDC), but you also need to know who has viewed (selected) your data. You need to know that only people who need to see your data, actually see your data. You do not want unauthorized read access.

In this case we created a server audit.

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

This was specified to go to a filepath (to a folder that could be locked down) with an unlimited maxsize.

There will be a performance impact on the IO subsystem.

The server audit then needs to be enabled. Then the audit specification was created.

Example: SELECT,INSERT, UPDATE, DELETE ON SCHEMA::[dbo] BY public

and this needs to be enabled.

To report on the audit file you use sys.fn_get_audit_file. You can also then join this with the CDC schema for a complete overview of data reads and writes.