Catching the culprit!!

Question

Catching the culprit!!

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

mathew.walters (4/3/2012)
Evil Kraig, your statement is incorrect, the host_name() function retrieves the host name of the machine making the request, I have used it in the past for auditing of important tables.

True, but host_name (and application name) are so incredibly easy to spoof that using it alone is not safe...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mathew.walters Old Hand Points: 318 More actions · Answer 2

True, but then you could also delete audit data if you wanted to, I assume that it's not malicious.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 3

Gail, I dont see the screen you pasted in sql server 2008. Is it for sql server 2008R2 only?

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

S_Kumar_S (4/3/2012)
Gail, I dont see the screen you pasted in sql server 2008. Is it for sql server 2008R2 only?

No.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mathew.walters (4/3/2012)
True, but then you could also delete audit data if you wanted to, I assume that it's not malicious.

Deleting audit data requires permission on the server, permissions whereever the audit data is stored and, depending how the auditing is implemented may be hard to do without leaving a trace. This does not require any permissions, it's just something done on the client or written into the app.

The point is that if someone is intending to be malicious (or something that they don't want tracing) they can set the host name and even the app name to anything they want and if that's all that's being used for auditing it will be hard to track where the commands came from.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 6

Got it....thanks.....had to expand the options.......

GilaMonster (4/3/2012)
mathew.walters (4/3/2012)
True, but then you could also delete audit data if you wanted to, I assume that it's not malicious.
Deleting audit data requires permission on the server, permissions whereever the audit data is stored and, depending how the auditing is implemented may be hard to do without leaving a trace. This does not require any permissions, it's just something done on the client or written into the app.
The point is that if someone is intending to be malicious (or something that they don't want tracing) they can set the host name and even the app name to anything they want and if that's all that's being used for auditing it will be hard to track where the commands came from.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 7

Then what should be the best way to trace a hostname?

GilaMonster (4/3/2012)
mathew.walters (4/3/2012)
True, but then you could also delete audit data if you wanted to, I assume that it's not malicious.
Deleting audit data requires permission on the server, permissions whereever the audit data is stored and, depending how the auditing is implemented may be hard to do without leaving a trace. This does not require any permissions, it's just something done on the client or written into the app.
The point is that if someone is intending to be malicious (or something that they don't want tracing) they can set the host name and even the app name to anything they want and if that's all that's being used for auditing it will be hard to track where the commands came from.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

mathew.walters (4/3/2012)
Evil Kraig, your statement is incorrect, the host_name() function retrieves the host name of the machine making the request, I have used it in the past for auditing of important tables.

Hm, everything I remembered about it reads like it gives you the hostname of whatever opened the connection to the SPID, so a hosted app would get the server name. I'll need to play with it, thanks for pointing that out.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Evil Kraig F (4/3/2012)
mathew.walters (4/3/2012)
Evil Kraig, your statement is incorrect, the host_name() function retrieves the host name of the machine making the request, I have used it in the past for auditing of important tables.
Hm, everything I remembered about it reads like it gives you the hostname of whatever opened the connection to the SPID, so a hosted app would get the server name.

You're correct, that's exactly what it does.

So if the app is a simple client-server with the app having a common login, the host_name will return the name of the client machine. If however it's an n-tier where the app connects to some middle-tier (IIS, custom app, SOA, Bixtalk, etc) and that connects to SQL, host_name will return the name of the middle-tier server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

GilaMonster (4/3/2012)
Evil Kraig F (4/3/2012)
mathew.walters (4/3/2012)
Evil Kraig, your statement is incorrect, the host_name() function retrieves the host name of the machine making the request, I have used it in the past for auditing of important tables.
Hm, everything I remembered about it reads like it gives you the hostname of whatever opened the connection to the SPID, so a hosted app would get the server name.
You're correct, that's exactly what it does.
So if the app is a simple client-server with the app having a common login, the host_name will return the name of the client machine. If however it's an n-tier where the app connects to some middle-tier (IIS, custom app, SOA, Bixtalk, etc) and that connects to SQL, host_name will return the name of the middle-tier server.

Ah, thank you, I'd thought so. That's why I don't remember considering it valid for any type of auditing, even non-spoofed malicious versions. I don't deal with many direct-apps anymore.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Eric M Russell SSC Guru Points: 125560 More actions · Answer 11

S_Kumar_S (4/3/2012)
We have a table which is used by many pages on our website.So it's an important table.
Now during development( I am talking about development environment here, no production), some developer will make bad updates to this table and everybody's work get hampered....
...I don't want to run a trace since I am never sure when the change might happen.
Thanks

To determine the best solution to your problem, could you clarify what it means to make a "bad update" to the table?

Perhaps the table is missing a referential constraint which would insure foreign keys are enforced. Or perhaps a check constraint is needed to insure that columns are updated in logically correct combinations.

You could also take the developers out of the SYSADMIN role, leave them as members of DBO so they can continue creating objects, but also deny them ad-hoc insert/update/delete permission on tables.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 12

A bad update can be anything that makes the data which it should not be. e.g. someone running update statement without where clause or someone updating incorrect values e.g. 'b' instead of 'a'.

I can not deny delete\update permission on this table.

Eric M Russell (4/4/2012)
S_Kumar_S (4/3/2012)
We have a table which is used by many pages on our website.So it's an important table.
Now during development( I am talking about development environment here, no production), some developer will make bad updates to this table and everybody's work get hampered....
...I don't want to run a trace since I am never sure when the change might happen.
Thanks
To determine the best solution to your problem, could you clarify what it means to make a "bad update" to the table?
Perhaps the table is missing a referential constraint which would insure foreign keys are enforced. Or perhaps a check constraint is needed to insure that columns are updated in logically correct combinations.
You could also take the developers out of the SYSADMIN role, leave them as members of DBO so they can continue creating objects, but also deny them ad-hoc insert/update/delete permission on tables.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 13

S_Kumar_S (4/5/2012)
A bad update can be anything that makes the data which it should not be. e.g. someone running update statement without where clause or someone updating incorrect values e.g. 'b' instead of 'a'.
I can not deny delete\update permission on this table.

Referential and check constraints can prevent updating a column with an incorrect value, and denying permissions can prevent ad-hoc updates and deletes. Users or developers can still insert/update/delete the tables by granting them exec permission on a stored procedure. Stored procedures can manage the updates of meta-data tables and contain addtional logical constraints. There is no reason for an application or developer to be updating the table in an ad-hoc fashion.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 14

I understand that app or developers shouldn't be updating the tables directly. But this is a dev server and there are some master tables for which we don't have any interface. All constraints are in place but thing is that if you have direct access to update a table and there are columns which doesn't have any constraints on them, someone can always updatem them with any value. Chaging this process to do via some procedure will require lot of time. So what we thought of tracking the people who does the updates as we believe many of the time they are intentional to make one teams work easy and thus putting others work at halt. Knowing that they are now being tracked will discourage them from this practice.If we don't succeed with this approach, then obviously we'll go the long way.

Eric M Russell (4/5/2012)
S_Kumar_S (4/5/2012)
A bad update can be anything that makes the data which it should not be. e.g. someone running update statement without where clause or someone updating incorrect values e.g. 'b' instead of 'a'.
I can not deny delete\update permission on this table.
Referential and check constraints can prevent updating a column with an incorrect value, and denying permissions can prevent ad-hoc updates and deletes. Users or developers can still insert/update/delete the tables by granting them exec permission on a stored procedure. Stored procedures can manage the updates of meta-data tables and contain addtional logical constraints. There is no reason for an application or developer to be updating the table in an ad-hoc fashion.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

S_Kumar_S (4/5/2012)
A bad update can be anything that makes the data which it should not be. e.g. someone running update statement without where clause or someone updating incorrect values e.g. 'b' instead of 'a'.
I can not deny delete\update permission on this table.
Eric M Russell (4/4/2012)
S_Kumar_S (4/3/2012)
We have a table which is used by many pages on our website.So it's an important table.
Now during development( I am talking about development environment here, no production), some developer will make bad updates to this table and everybody's work get hampered....
...I don't want to run a trace since I am never sure when the change might happen.
Thanks
To determine the best solution to your problem, could you clarify what it means to make a "bad update" to the table?
Perhaps the table is missing a referential constraint which would insure foreign keys are enforced. Or perhaps a check constraint is needed to insure that columns are updated in logically correct combinations.
You could also take the developers out of the SYSADMIN role, leave them as members of DBO so they can continue creating objects, but also deny them ad-hoc insert/update/delete permission on tables.

Hard to defend against someone forgetting the where clause. You say a bad update can be an incorrect value, such as 'B' instead of 'a'. Question I have is 'b' a valid value for that column? As another poster indicated, check constraints can be used to prevent invalid values from being inserted/updated.