Sql-server – Auditing/Logging Help

auditsql server

I'm working on a request to start logging some additional data for PCI compliance. I need to log who, when, and what changed on a set of tables. I set up SQL auditing which would work fine except for the fact that the values of what is being changed is coming through as @1, @2 etc instead of the actual data. What are my other options for logging the needed information? CDC does not track who made the change.

This is currently running on SQL Server 2012 but a possible upgrade to 2014 is possible if that would provide a better solution.

Thanks,
Tim

Best Answer

I would probably go with triggers. You will be warned about performance implications and all that, but truthfully there isn't a "free" way to audit, and any option you try will have some impact on your workload. You will need to be aware that if someone updates the whole table, you will be logging that too, so be prepared for your auditing data growth to potentially outpace your actual data. Might want a separate database on isolated storage for that.

Auditing, CDC and Change Tracking would be great if they were combined, but they're not. As you've discovered, auditing shows parameter placeholders, so it tells you who changed it but not what they changed, and the other two tell you what has changed, but not who changed it or when.

I've asked for the ability to include username and date/time in CDC data, but it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

I also asked for date/time information to be included in Change Tracking, but similarly it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/319049/change-tracking-provide-datetime-column

I wasn't aware of the auditing limitation. If I had known about it, I would have asked for it, too. And it would have been declined like the others.

Related Solutions

Enabling/disabling/changing Oracle auditing without a shutdown

AUDIT_TRAIL is an initialisation parameters and needs a database restart. Anyway by default AUDIT_TRAIL is set to DB. This could be enough.

With this AUDIT_TRAIL you have just to issue:

AUDIT SELECT TABLE

to start the audit on any table for any select. As you stated this activity has an overhead from the database point of view. you have to pay attention to you SYSAUX tablespace growth where by default resides the sys.aud$ table. To move sys.aud$ you have to do:

SQL> BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
 audit_trail_location_value => 'AUDIT_TBS');
END;
/

FGA is much more than AUDIT and lets you specify the conditions necessary for an audit record to be generated. Furthermore FGA policies are programatically bound to the object (table, view).

Apart from this you should consider a different solution like an third party tool for DAM (Direct Access Monitoring). Oracle has its own solution today called Oracle Firewall. Otherwise you can check for other products like IBM Guardium, DBProtect, etc.

Mysql – Triggers on Slave database (in the context of replication) for auditing purpose in MySQL

@parag Yes you can create triggers on the slave which won't be replicated. The only issue I see is if you're Master fails and you promote your slave to master you will need to keep track of what changes need to be applied to the new slave and what changes need to be applied to the newly promoted master. If I'm not mistaken you can create the triggers on the master so that they can be replicated and then disable them and just enable them on your slave. That way all your environments will have the triggers but only enabled on one slave.

Or, so you can avoid managing details like that, which can become overwhelming, you can make the slave, the master of another slave which will do the auditing and only have the triggers on that subsystem.

Thanks John

Best Answer

Related Solutions

Enabling/disabling/changing Oracle auditing without a shutdown

Mysql – Triggers on Slave database (in the context of replication) for auditing purpose in MySQL

Related Question