June 15, 2006 at 7:33 am
Got it: master.dbo.sysprocesses
thanks.
Is there a table that is used to log the current sessions in the db (sql server 2000)?
I am using sp_who to determine the sessions but it does not give me all the info I need.
What I am trying to do is to determine what applications are being used to access the database(toad, etc.) and to alert the security officer when an unauthorized application is accessing the db.
Is there a view/table/proc that will give me this data?
Thanks
June 16, 2006 at 12:45 pm
Since your request begins with "Got it" I fear this post may be an orphaned thread, so I appologize ahead of time if I make a statement that has already been addressed.
There are no other logs available unless you 1) Add specific logging into all your table triggers. 2) Do an in-depth analysis of the database log system. I recommend neither. I have been down both paths.
More information is available through sp_who2.
The system function suser_sname() will return the actual user, which may be different from the apparent user.
sysprocesses is a system memory table and you cannot apply any triggers directly against it. The best advice (may not be fgood advice) I can think of is to create a "watchdog" agent that runs on a periodic schedule. This agent could query sysprocesses and get you the application name and the user's machine name (and more).
Please think this through before creating a watchdog because there is usually a better, more efficient method available when added consideration is given to the local work environment. Watchdogs are notorious as system hogs.
...Mel
June 21, 2006 at 6:03 am
Mel,
A "watchdog" is what I inteded to create. It will be a simple routine that will periodically query sysprocesses and check against a table of "identified apps". If the application is known and allowed then the routine bails. If the app is unknown or restricted then the routine generates an email.
Let me ask you this, does sql server 2000 have any built in parameters that will restrict the applications that connect to it?
btw. thanks for the suser_sname() function.
June 21, 2006 at 12:25 pm
There are no settings for SQL Server to blacklist or whitelist applications. This field in sysprocesses is actually free text generated by the calling program through DBLib or ODBC connections. It can be easily spoofed. We can only hope to assume that the restricted application queried SS out of ignorance and not malice. I have seen many times where a user with little knowledge used MS Access via ODBC to link to SS production tables. The connection string is well documented for MS Access and newbies commonly use custom app names in the connection. The subject of MS Access causing locking issues and other concurrencies has been the subject of many flames. That is why they say, "A little bit of knowledge is dangerous."
There may be middleware to wrap SS so that a blacklist may be incorporated, but I am unsure how to Google this.
Be careful of the watchdog, he bites and is temperamental. If an application makes several attempts, e.g., CrystalEase, then you will generate an email for each attempt. CrystalEase is known for highly persistent retry attempts. This - or even attempting to add intelligence to the watchdog - will degrade system performance and overrun an admin mailbox.
Good luck training the dog.
...Mel
June 22, 2006 at 11:19 pm
No, SQL Server doesn't restrict applications, only logins. And as mentioned, it is trivial to spoof the application name. This can easily be done using a File DSN, in which case the application name can be specified.
K. Brian Kelley
@kbriankelley
June 23, 2006 at 11:51 am
gotcha,
back to the drawing board...
Any ideas? sql server 2000, password authenticated, ...
June 23, 2006 at 12:50 pm
How about a solid corporate policy with enforcement? When technology doesn't provide a solution, there are always procedural controls.
K. Brian Kelley
@kbriankelley
June 23, 2006 at 1:05 pm
Usually, the power users that have MS Access link to SS tables do so for reporting reasons only. Unfortunately, they don't always set their app to use snapshots because read-write access is the default. They always take the easy way out and this will cause locking issues.
It is always best to have a security topology that includes user roles. Access by external apps can be easily controlled as long as there is fair trust and communication with your users. The roles can be manipulated to do such things as grant read-only access to specific tables, thus reducing your locking issues.
You can even set up a single generalized login specifically for anyone that needs report-only access to SS, then have users link tables using this ID that has read-only privilages.
There are many ways to manipulate security that accomplishes what you need and leaves users with warm and fuzzy feelings. All of these methods, though, are highly technique dependant. Be sure you have taken the time to gather all known requirements before implementing a security design. It is very easy to paint yourself into a corner when it comes to security issues.
...Mel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply