October 14, 2010 at 11:48 am
Hi folks!
Question regarding auditing... I am a developer for a sql server 2005 enviornment and also for Oracle. In Oracle, all I had to do for auditing was set audit all statements for each table that I wanted monitored... in SQL, I'm at a loss.
We have a customized "trace" report that we run daily that captures database activity like the username, windows username, db, how many login attempts and where the connection originated from (and we're not even sure about the reliabilty of this). But, I want something that says Jane Doe ran a select statement against Table X on X date. Is there any way in sql server to do this? I checked all the system tables and I didn't see anything that remotely looked helpful except the sys.dm_exec_sessions table but that just seemed like session/connection info.
I know I can do insert update and delete DDL triggers but I really don't care about that stuff... lol. The only way data gets updated in our database is if we have created a data load that does it. Our system is not transactional... its basically a reporting database.
The problem is we have PII (sensitive information) in our data and we really care about who accesses it without permission. Mainly, and its very sad, we're concerned about the production support team. For example, our daily trace report showed an unauthorized access to a database with very sensitive information by the production support team one day. The task he was doing should not have been touching this database at all. However, we have no other records on what he did except that it recorded a successful connection to this database.
So, I really wanna know if he ran a query or did any kind of select from the tables in the database he accessed.
Do any of you know how to accomplish this? Is there any kind of custom procedure I can write or even some simple third party tools. I know SQL profiler can track TSQL actions but we really don't want sql profiler running in the background constantly. We already have some network performance issues.
Many thanks!
October 14, 2010 at 11:57 am
You can run a DML trace that will capture all queries run against the database. Take a look at "sp_trace_create" in Books Online (or on MSDN) for details on how to set it up. It's pretty easy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2010 at 12:30 pm
On further thought, if they aren't supposed to access the database at all, why do their accounts allow it? You can set up SQL Server to deny the right to read data to certain accounts in certain databases.
If they sometimes need access, but usually don't, then create an account for temporary access, and change the password on it after every use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2010 at 12:40 pm
Well, they are production support...they are supposed to have access to everything. However, just because they have access to do anything and everything doesn't mean I don't want to know what they are doing. 🙂 While they have the right to perform maintenance and things they should not be querying tables and stuff.
But we have no control over the access rights to the production team. Being the developers, we work for a completely different company than the production people... so its very complicated. 🙂 I work for the gov't.... so... that should answer that question hehe. 🙂
October 14, 2010 at 12:51 pm
so with the sp_create_trace ... after reading a little... its basically a programmatic way to tell sql server to start a profiler trace? And I can stop it and start it in code?
Would I then be able to take the results and put it in a table?
October 14, 2010 at 2:37 pm
amy26 (10/14/2010)
so with the sp_create_trace ... after reading a little... its basically a programmatic way to tell sql server to start a profiler trace? And I can stop it and start it in code?Would I then be able to take the results and put it in a table?
Yes, you can set the trace up to run in the background, this is a lot more efficient than running in a window either on the server or from a remote maching. Set the file to have a max size and roll over/create a new folder when the size limit is reached. You can then either:
Read the trace fiel directly out of Managment Studi, or open it from Profiler, or save it as a table (manually or with scripts).
Be weary about how much data you track as this file can get really big really fast on a busy server. I'd filter by table or by logon or some other reasonable filter to keep the rows to a minimum.
You can also do things like create a job to ensure the trace is automatically started if the server is restarted, a job to auto load the data into a table,... through too jobs to report on suspect activity.
NOTE! This doesn't stop someone from stopping the trace, doing their dity work, then starting the trace again.
When you say this is the Production Support team, what exactly do you mean? Are they DBA's or System Admins? If they are only system admins and shouldn't be able to see the data, you can try deleting the BUILTIN\Administrator account. I'd rather use good security and confidentiality agreements to manage this than a trace (if possible). You should be able to trust your staff.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
October 14, 2010 at 2:50 pm
Well, its complicated... they are supposed to be DBA's (and have that title) but we can't just say things like go add a new user with these permissions we have to write down the instructions like: Expand this folder, click here, right click, add new user, go here, do this....
Technically my group is not supposed to do anything to the production server that doesn't go through a release and the only people that are allowed to touch production is this production support group. But we find we have to do a lot of things ourselves because they are just a bunch of dimwits. And they aren't "our" staff. I think I mentioned before that us (the developers) work for a totally different company than the production people. And our group and their group are contractors.
Oh but thank you for the info with the profiler stuff... I don't think we should have a problem with it getting crazy out of control... I only want to track TSQL activities for a limited amount of stuff.
Thanks so much. 🙂
October 14, 2010 at 2:54 pm
When you say this is the Production Support team, what exactly do you mean? Are they DBA's or System Admins? If they are only system admins and shouldn't be able to see the data, you can try deleting the BUILTIN\Administrator account. I'd rather use good security and confidentiality agreements to manage this than a trace (if possible). You should be able to trust your staff.
Second this. Tighten the access privilage is the best way, otherwise trust your staff.
October 14, 2010 at 2:58 pm
Wildcat (10/14/2010)
When you say this is the Production Support team, what exactly do you mean? Are they DBA's or System Admins? If they are only system admins and shouldn't be able to see the data, you can try deleting the BUILTIN\Administrator account. I'd rather use good security and confidentiality agreements to manage this than a trace (if possible). You should be able to trust your staff.
Second this. Tighten the access privilage is the best way, otherwise trust your staff.
Again... they aren't our staff and the client wants this information not me specifically. And we have no control over their access.
I totally agree with both of you and appreciate the feedback. Unfortunately, that isn't enough in our enviornment.
October 15, 2010 at 12:16 pm
A server-side trace (that's what it's called) sounds like it'll do what you need. Definitely filter it, and manage the disk space in it.
There's a function, fn_trace_gettable, that will turn the trace files into tabular data for you. I use that to insert into a temp or full table, because you can query those more efficiently, but it's pretty easy to use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2010 at 12:38 pm
Cool thank you so much. I've been a developer for many years so getting into the more admin type stuff is new for me. 🙂
Thanks to everyone for the feedback.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply