July 30, 2010 at 3:04 am
Our auditing tends to be in two spheres -
1) Where there is interaction with the customer, so sales orders and product specifications are audited.
2) The finance system, we have very fine grained history of all our transactions.
This just takes the form of history tables plus user names & dates.
Of course it is external interactions which dictate what goes on internally, so I guess that is what is being audited, if something goes wrong internally (we make the wrong product or buy the wrong supplies) then we can find out what the catalyst was for that. I guess there's no point auditing internal procedures which are carried out as a result of making an invalid transaction with the outside world.
In terms of SQL, then, nothing, it's all application based, but then that's why we only allow indirect access to the DB. I could imagine in a larger site with lots of people with direct DB access you'd have to go to a whole different level, but I don't see a justification for it here.
July 30, 2010 at 3:46 am
In our system we audit everything by storing a new state of table row into audit log as xml record along with the info who and when made the change. this way by using views on audit log table we can write query and reconstruct each row to any desired point in time if it is necessary to do so.
At application level we log parameters for every query on database to monitor who and when acceded some data.
July 30, 2010 at 6:18 am
Unfortunately we don't audit as much as I'ld like due to how auditing is implemented in our ERP system.
We audit a few basic fields on certain transactions, but don't audit BOM changes or most other master record changes.
Consequently we rather have to guess who might have made the change based on the date and username of whoever touched the record last.
Does SQL Server have an integrated audit function built in? Something that would let you audit changes to particular fields in tables and grab date, time, userid, and before/after field content?
If not, would it be worth while to anyone other than me to have one?
July 30, 2010 at 6:33 am
The whole topic of auditing can be very complex and honestly, I don't understand or know all the options I even have available to me. Fortunately, I am not currently in an industry that is highly regulated, so I don't have a ton of auditing requirements. In my opinion, this is one of the areas where PASS should be providing best practices and recommendations.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2010 at 6:45 am
We audit select "tables". I have a generic trigger I apply to all tables of interest which simply makes a copy of an existing row before it is changed/deleted and places it in an "audit" copy of the same table. All audit tables have triggers which prevent deletions execpt by a specific user account which was created for that purpose only.
There is nothing particularly fancy about this method, it is mostly brute force but it works and with some creative SQL we can reconstruct most activity on our sensitive data as well as recovering accidentally changed data. Once in place it takes care of itself (I don't have to be concerned with the application side of the house) but I do have to be mindfull of table design changes since they need to be applied to the audit copy of the table also.
I would be interested in seeing how mcerkez88 implemented/maintains their xml audit logs.
James.
July 30, 2010 at 6:49 am
mcerkez88 (7/30/2010)
In our system we audit everything by storing a new state of table row into audit log as xml record along with the info who and when made the change. this way by using views on audit log table we can write query and reconstruct each row to any desired point in time if it is necessary to do so.At application level we log parameters for every query on database to monitor who and when acceded some data.
Would you be willing to post an example of how you implmented your auditing? Curious how you insure all changes are written to the audit log in an appropriate format. Are you just capturing the changes or the entire record that is being changed? If done via trigger or stored procedure I'd appreciate seeing a copy. I understand if you consider it a trade secret or sensitive and don't want to publish it publicly.
Thanks,
James.
July 30, 2010 at 7:43 am
I work at a company whose clients include pharmacies so several of our applications store PHI (protected health information) and must be "treated with special care" according to HIPAA.
I am currently working on an application to audit any PHI access. That is, any time a stored procedure returns PHI to the application, I'll have to enter a log record showing what data was seen, by whom and when.
I'll be using Service Broker to log the accesses, sending encrypted messages, and storing it in a database encrypted with TDE. I'm not sure what the volume will be yet but it seems like a good idea to send the SSB messages as binary to reduce the size.
------------------------------------------------------
Katherine
July 30, 2010 at 7:57 am
Katherine Fraser (7/30/2010)
I work at a company whose clients include pharmacies so several of our applications store PHI (protected health information) and must be "treated with special care" according to HIPAA.I am currently working on an application to audit any PHI access. That is, any time a stored procedure returns PHI to the application, I'll have to enter a log record showing what data was seen, by whom and when.
I'll be using Service Broker to log the accesses, sending encrypted messages, and storing it in a database encrypted with TDE. I'm not sure what the volume will be yet but it seems like a good idea to send the SSB messages as binary to reduce the size.
------------------------------------------------------
Katherine
How is that going to work when a DBA or developer or process is doing bulk historical reporting, or investigating/troubleshooting to find patterns (selecting millions of rows to let a human spot patterns)?
July 30, 2010 at 8:30 am
bwillsie-842793 (7/30/2010)
Does SQL Server have an integrated audit function built in? Something that would let you audit changes to particular fields in tables and grab date, time, userid, and before/after field content?If not, would it be worth while to anyone other than me to have one?
SQL Server 2008 has a built in Auditing feature that is very nice.
July 30, 2010 at 8:33 am
Katherine Fraser (7/30/2010)
I'll be using Service Broker to log the accesses, sending encrypted messages, and storing it in a database encrypted with TDE. I'm not sure what the volume will be yet but it seems like a good idea to send the SSB messages as binary to reduce the size.
That sounds very interesting. I think this would make a great article here if you're up for it.
July 30, 2010 at 11:46 am
Nadrek (7/30/2010)
Katherine Fraser (7/30/2010)
....an application to audit any PHI access. That is, any time a stored procedure returns PHI to the application, I'll have to enter a log record showing what data was seen, by whom and when.I'll be using Service Broker to log the accesses, sending encrypted messages, and storing it in a database encrypted with TDE...
How is that going to work when a DBA or developer or process is doing bulk historical reporting, or investigating/troubleshooting to find patterns (selecting millions of rows to let a human spot patterns)?
It won't. This solution is just intended to log PHI accesses from the application and the application can only retrieve data via stored procedures. So I'll put the audit code inside the procs to capture what's being returned. Our report procs are capped at 65k rows so I'm hoping that will be manageable. And summary reports don't include PHI since it is detail data like patient name, address or medical record number.
You are correct in pointing out that we aren't auditing what direct database users are viewing. I'm not sure what would be a good method for that given, as you noted, the large number of rows that could be returned. For now we acknowledge that people who have direct database access can see all PHI. But that set of users is very limited and their access is audited, albeit at a much less granular level that does not include the individual queries that were run or their results.
------------------------------------------------------
Katherine
July 30, 2010 at 12:29 pm
Our company has a project about Government Health. In this project, Client asked to audit any changes against more than 20 tables. So we created triggers (insert, update,delete) to every table.
Created two tables to save changes. one save who does what aginist which table and which object. another save the detailed changes.
July 30, 2010 at 1:18 pm
-=JLK=- (7/30/2010)
mcerkez88 (7/30/2010)
In our system we audit everything by storing a new state of table row into audit log as xml record along with the info who and when made the change. this way by using views on audit log table we can write query and reconstruct each row to any desired point in time if it is necessary to do so.At application level we log parameters for every query on database to monitor who and when acceded some data.
Would you be willing to post an example of how you implmented your auditing? Curious how you insure all changes are written to the audit log in an appropriate format. Are you just capturing the changes or the entire record that is being changed? If done via trigger or stored procedure I'd appreciate seeing a copy. I understand if you consider it a trade secret or sensitive and don't want to publish it publicly.
Thanks,
James.
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.
August 6, 2010 at 4:40 am
Has anyone dealt with the problem of auditing DBA / priviledged account access - even select access? I know there are some third party products that do a half decent job out there, but how are you implementing them? Who monitors the reports? How do you filter legitimate access vs accessing records they have no reason to be looking at. This is a problem with privacy data (as mentioned in a previous post) or intellectual property, especially as outsourcing becomes more prevalent. (And that is not intended as an outsourcing slam!)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply