August 6, 2010 at 10:16 am
Auditing sysadmins is hard. Especially since the sysadmin has to set things up!
SQL 2008 has good auditing capabilities, and what you need to do is write audit records to a file where the DBAs can't access. Only some security group. The SQL Service account should only have write access.
August 6, 2010 at 11:16 am
[/quote]
Sure no problem. I will mask the data in records but i believe it should give you solid example of system in place.
This the example of one row in audit log table: (result of query bellow)
mcerkez2010-07-27 17:50:54.643303759modifyVoucherDenomination4<?xml version="1.0" encoding="utf-16" standalone="yes"?><log object="VoucherDenomination" action="modify"><field name="id" value="6" /><field name="barcode" value="00000000000000" /><field name="recharge_period" value="000" /><field name="voucher_name" value="XXX" /><field name="voucher_value" value="00000" /><field name="product_code" value="1234" /><field name="serviceproviderservice" value="XXX" /><field name="serviceprovider" value="XXX" /><field name="default_min_active_quantity" value="10000" /><field name="default_min_activation_quantity" value="000" /><field name="default_min_inactive_quantity" value="0000" /><field name="mtime" value="27.7.2010 17:50:54" /><field name="ctime" value="20.2.2008 17:20:51" /><field name="deleted" value="False" /></log>
And this is the quary that selects all columns from audit log table.
SELECT top 1 username
,[ctime]
,[id]
,[action_type]
,[record_type]
,[errorLevel]
,[description]
,[extraInfo]
FROM [dbo].[AuditLog]
This configuration allows us to create view onto audit log table depending on our needs. Also ordering by ctime (cration time) column allows us to maintain chain of modification on particular database object.
If you have any other question please don't hesitate to ask.[/quote]
curious how the record gets into the table. Is it a trigger?
-- Optimist with experience and still learning
August 6, 2010 at 12:35 pm
Ed Salva (8/6/2010)
Sure no problem. I will mask the data in records but i believe it should give you solid example of system in place.
This the example of one row in audit log table: (result of query bellow)
mcerkez2010-07-27 17:50:54.643303759modifyVoucherDenomination4<?xml version="1.0" encoding="utf-16" standalone="yes"?><log object="VoucherDenomination" action="modify"><field name="id" value="6" /><field name="barcode" value="00000000000000" /><field name="recharge_period" value="000" /><field name="voucher_name" value="XXX" /><field name="voucher_value" value="00000" /><field name="product_code" value="1234" /><field name="serviceproviderservice" value="XXX" /><field name="serviceprovider" value="XXX" /><field name="default_min_active_quantity" value="10000" /><field name="default_min_activation_quantity" value="000" /><field name="default_min_inactive_quantity" value="0000" /><field name="mtime" value="27.7.2010 17:50:54" /><field name="ctime" value="20.2.2008 17:20:51" /><field name="deleted" value="False" /></log>
And this is the quary that selects all columns from audit log table.
SELECT top 1 username
,[ctime]
,[id]
,[action_type]
,[record_type]
,[errorLevel]
,[description]
,[extraInfo]
FROM [dbo].[AuditLog]
This configuration allows us to create view onto audit log table depending on our needs. Also ordering by ctime (cration time) column allows us to maintain chain of modification on particular database object.
If you have any other question please don't hesitate to ask.[/quote]
curious how the record gets into the table. Is it a trigger?[/quote]
No actually we insert data into table directly form application. There is no way for a user to get to database except through the application. But i presume it is possible to create trigger for such a purpose if needed.
August 6, 2010 at 2:04 pm
After cooresponding with mcerkez88 I took his idea and implemented it using triggers. Attached is a text file that contains the DDL to create the "Audit" table, a trigger to prevent editing the audit table by most users/process, along with the definition of a simple table from my database and the trigger attached to it that logs all changes to the table in the audit table.
Prior to this I had to have a seperate audit table for each table I was auditing and keeping all the changes in sync between the "audited" and "audit" tables was a bit of a pain, but it did work. Based on the new design I need only a single audit table with views into the xml to retrieve the information I'm interested in. Performance seems to be excellent, though I have not stressed the system as we only have about 50 users at any one time on the system where I'm using this, so I make no warranties in that respect 🙂
I hope someone finds the attached file usefull/interesting and I'm interested in all constructive comments/suggestions. I don't consider myself an expert in any of this and am always willing to learn a new technique or be corrected if I've done something stupid.
One comment on maintaining my triggers, I use Powerdesigner from Sybase to maintain my DB Model and with it's built in scripting it does an excellent job of keeping my triggers up-to-date if I change a table name or make some other modification that would result in breaking the trigger.
August 9, 2010 at 7:51 am
Steve Jones - Editor (8/6/2010)
Auditing sysadmins is hard. Especially since the sysadmin has to set things up!SQL 2008 has good auditing capabilities, and what you need to do is write audit records to a file where the DBAs can't access. Only some security group. The SQL Service account should only have write access.
I'd also state that writing audit records to some kind of WORM media (journaled, like MO platters, or not, like printers and DVD+R), and then having another department audit the journal on said media, is an important step.
If one insists on writing said auditing to SQL Server tables, I'd have to say that writing transaction log backups of the logging database to WORM media every few seconds would be a reasonable, necessary, and still insufficient step. Another way to do this might be to set up the transaction log itself to write to WORM media; one would have to accept horrendously slow performance, however, and constantly change the physical platter/tape/whatever the log writes to, which could be... interesting.
Log files should be written directly and immediately to WORM media, absolutely minimizing any delay in which they can be changed prior to being archived. For those SQL Server trace flag experts out there, is there a trace flag that does this, and that can shut down SQL Server when auditing fails?
In particular, auditing changes to the auditing is critical.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply