Our company is ISO27001 certified. As such, it is required for us to log database events. We are taking an incremental approach, and first starting with logging events just on one table.
This particular table contains the id of the logged in user.
We would like to create logs of
- manual data changes to this table (the name of the SQL Server user who effected the change, and what he/she did)
- stored procedures that change the data on this table. (the id of the logged in user, the name of the SQL Server user who effected the change, and the statement of the SQL server stored procedure, including the parameters)
- schema change events (the name of the SQL Server user who effected the change, and what he/she did)
We do not need to log events that view data.
What is the best way to do this when using Azure SQL?
The following thread discusses various ways of building an extra table in the database, and logging events using triggers. https://stackoverflow.com/questions/38437/how-to-track-data-changes-in-a-database-table
However I would have liked to take advantage of the database auditing available in Azure SQL, in part because the afore-mentioned approach could easily slow down our database.
I have set up a database audit using the portal - and it generates about 1.5GB of auditing data every day, which is unnecessary for my purposes.
Is there a way of confining the Azure database auditing to relate to just one table?