May 16, 2023 at 4:01 pm
Hello,
I need to setup the database audit against a single database to capture the details such as: create/drop/alter tables, views. sps, done by any users...
Can someone suggest to setup a db level audits? The environment may have SSIS packages so it should not cause any issues
Thanks
Thanks.
May 17, 2023 at 8:13 am
First question about audits, is does the audit need to be legally binding for any compliance issues?
If yes - then SQL Audit is not the tool to use, you need to use a 3rd party tamper proof auditing software for SQL like Apex SQL Audit, Idera SQL Compliance Manager etc.
Reasoning for this is that SQL Audit can be disabled with anyone who has enough permissions, so your SSIS packages when running under the Agent context is a sysadmin, if people know the audit is there, they could easily put in a piece of T-SQL to disable the audit at the start of the package, and then enable the audit again at the end, and heck you've missed what that package has altered, or whatever a user has done in the period the package has run.
Or the file that the audit rights to again could easily be compromised with enough permissions etc.
SQL Audit isn't there as a regulatory compliance tool, for that you need external software.
SQL Audit is NOT tamper proof enough for legally binding compliance requirements.
If the answer is no, it's not needed for regulatory compliance or doesn't need to be legally binding and your happy that people with enough permissions can start disabling, enabling the audit as you they see fit to hide their tracks, then you will want to create a
DATABASE AUDIT SPECIFICATION on the DATABASE_OBJECT_CHANGE_GROUP action group, which will track any CREATE/ALTER/DROP commands.
For extra security I would add in the AUDIT_CHANGE_GROUP action group too, to see if people are disabling/enabling/changing the audit in any way also, so you can go and have some choice words with them,
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply