What type of events are tracked in Azure Sql Database auditing. Can we setup to audit is a specific column in a table is updated?
What type of events are tracked in Azure Sql Database auditing. Can we setup to audit is a specific column in a table is updated?
SQL Auditing supports the following action groups:
BATCH_STARTED_GROUP, BATCH_COMPLETED_GROUP, APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, BACKUP_RESTORE_GROUP, DATABASE_LOGOUT_GROUP, DATABASE_OBJECT_CHANGE_GROUP, DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP, DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, DATABASE_OPERATION_GROUP, AUDIT_CHANGE_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP, DATABASE_PRINCIPAL_IMPERSONATION_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, SCHEMA_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_CHANGE_GROUP, SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, USER_CHANGE_PASSWORD_GROUP
If you are interested on auditing UPDATE statements then you need to use "BATCH_COMPLETED_GROUP". In addition, you need to specify UPDATE as audit action. Below an example of how to audit only UPDATE statements but I don't know a way to capture updating a specific column.
Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup"
-ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT"
-StorageAccountName "storageAccount"
-AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP"
-AuditAction "UPDATE ON database::[AdventureWorksLT] BY [public]"
-RetentionInDays 60