Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Auditing Expand / Collapse
Author
Message
Posted Friday, July 30, 2010 8:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:55 AM
Points: 33,072, Visits: 15,183
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #961430
Posted Friday, July 30, 2010 11:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:22 PM
Points: 10, Visits: 144
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
Post #961556
Posted Friday, July 30, 2010 12:29 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 568, Visits: 813
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.

Post #961578
Posted Friday, July 30, 2010 1:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 12:46 AM
Points: 6, Visits: 49
-=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)

mcerkez 2010-07-27 17:50:54.643 303759 modify VoucherDenomination 4 <?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.
Post #961597
Posted Friday, August 6, 2010 4:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:53 AM
Points: 12, Visits: 277
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!)
Post #964990
Posted Friday, August 6, 2010 10:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:55 AM
Points: 33,072, Visits: 15,183
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #965301
Posted Friday, August 6, 2010 11:16 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 7:02 AM
Points: 28, Visits: 304
[/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)

mcerkez 2010-07-27 17:50:54.643 303759 modify VoucherDenomination 4 <?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
Post #965345
Posted Friday, August 6, 2010 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 12:46 AM
Points: 6, Visits: 49
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)

mcerkez 2010-07-27 17:50:54.643 303759 modify VoucherDenomination 4 <?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.

Post #965401
Posted Friday, August 6, 2010 2:04 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 1:16 PM
Points: 367, Visits: 303
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.


  Post Attachments 
AuditExample.txt (16 views, 8.06 KB)
Post #965447
Posted Monday, August 9, 2010 7:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 861, Visits: 2,357
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.
Post #965959
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse