Data auditing has become a business challenge for many DBAs and security administrators with the recent compliance dates required by numerous industries from the United States Government. Some of these regulations include:
- Healthcare – HIPAA
- Pharmaceuticals and FDA – 21 CFR Part 11
- Financial – GLBA
- Services – SAS 70
- Publicly Traded Companies – SEC
- Telephone Carriers – CPNI
- Financial Accountability – SOX
As a DBA, this legislation provides you with an opportunity to be the hero for your organization to help prove compliance. As such, you have a number of options to achieve compliance via comprehensive processes, talented people and beneficial technology. These options include the following:
- Triggers can be created for each table and executed any time an INSERT, UPDATE or DELETE transaction is committed in conjunction with audit tables to capture the needed data with functions like GETDATE(), SUSER_SNAME() as well as accessing the values in the INSERTED and DELETED tables internal to the trigger code.
- In your existing stored procedures it is possible to mimic the trigger functionality as outlined above and for SELECT statements execute INSERT statements to capture the necessary values into an audit table.
- SQL Profiler is able to capture all of the SQL Server activity both from a transactional and data perspective in a raw format.
- Fn_get_sql is a function introduced in SQL Server 2000 that can be called for each SPID and record the activity performed for each process.
- DBCC INPUTBUFFER is another statement that can be issued on a per SPID basis to capture the user statements.
- C2 Auditing is another option with SQL Server to capture the processing information and store externally for review.
- ApexSQL's Audit is a tool to manage triggers for database tables in a particular database.
- Lumigent Log Explorer, Log PI and ApexSQL Log Navigator leverage the contents of the database transaction log in a similar manner to determine the data changes at a transactional level.
- Lumigent Entegra is an enterprise auditing solution that records all database activity (SELECT, INSERT, UPDATE, DELETE, DDL, DML, Security, etc.) without the overhead of triggers and can not be subverted with the ability to provide real time alerting as well as centralized storage for compliance reporting via a simple Web-based interface.
This response is continued...
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.