Logging just one table in an Azure SQL Database
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?
Yes, you can configure auditing for Azure SQL Database to filter down to a single object, but you will need to use the Azure PowerShell module. You cannot do it through the Portal.
The example below assumes you have already enabled auditing at the database level through the Portal. This script changes the Audit Action Group to "DATABASE_OBJECT_CHANGE_GROUP" and then adds several Audit Actions. The Audit Actions is where you will specify the DML actions for the table you want to audit. You will have to specify each stored procedure separately. It's just a comma delimited list.
Set-AzSqlDatabaseAudit ` -ResourceGroupName "MyResourceGroupName" ` -ServerName "MySqlServerName" ` -DatabaseName "MyDatabaseName" ` -AuditActionGroup "DATABASE_OBJECT_CHANGE_GROUP" ` -AuditAction ` "INSERT, UPDATE, DELETE ON dbo.LoginTable BY public", ` "EXECUTE ON usp_InsertLoginTable BY public", ` "EXECUTE ON usp_DeleteLoginTable BY public"