Author |
Topic |
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-12-13 : 05:19:13
|
The system I look after uses ODBC for the client app to connect to the SQL Server (7). Some users have realised this and are using Excel etc to connect and write queries. This is all done with Windows authentication to the server. I would like to prevent these people from accessing like this but am not sure if it is even possible.Does anyone have any suggestions?thankssteveTo alcohol ! The cause of - and solution to - all of life's problems |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-13 : 05:23:26
|
ha ha ha, it is possible.just had this scenario happening here (time records, edits and deletes), so what i did is, i placed a trigger on the table and disconnected the users using @@spid if they're not "authorized" to access the objects using programs other than the "authorized" program.it's probably the same logic, so you'll need to monitor the sysprocesses table--------------------keeping it simple... |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-12-13 : 05:26:48
|
Ooooh, thanks Jen, I'll take a look at that :)steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-13 : 05:53:35
|
But they'll only be connecting to the SProcs they have execute permission for, so what's the problem which App. they connect from?<BigG>Kristen |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-13 : 06:04:30
|
problem is, if the queries are not in sprocs, there are systems that are too large to convert or bother converting to or too much hassle to convert (too many dependecies), i guess long term solution is edit the system steve and use sprocs and udfs like what Kristen is suggesting at.in our case, programmers share user information to other programmers, so it's like trying to contain the fire before it spreads. By disconnecting them, they'll be discouraged to try again.--------------------keeping it simple... |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-13 : 10:52:43
|
All joking apart, chaps and chapesses, using SProcs and NOT granting db_datareader / db_datawriter is a huge boon - loads of wasted time ermmmm ... "not wasted" :-)Kristen |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-12-14 : 05:39:10
|
Don't get me wrong here guys I agree with you absolutely (and appreciate the advice)The system I have to babysit is NOT written by me. It is essentially a database that was originally converted from a hierarchical database system (didn't see anyone mention this one in my survey!) into MS Access. It was then pushed through the Access upsizing wizard to get it onto SQL Server. i.e. SQL Server is largely being used as a glorified version of Access - NO Security - in fact, if I tried to put some into place it would probably break the app or at best would deny legitimate access or break the support contract. Essentially what I have is tables linked through to an MS Access front end. And that's it. Anyone can break into the app (deliberately or by accident) and have whatever access they like to the underlying data (I can hear the gasps of horror as I write). And if you think that's bad, this thing isn't even in FIRST normal form never mind any other - now I know there are sometimes good reasons for denormalising but believe me they don't apply in this case. This supplier even wrote their own record locking system - the icing on the cake being - yes, you've guessed it - it requires user input to complete the process! And as for relationships - pah! who needs 'em.Apologies, I'll try to stop ranting now. Anyway the bottom line is that because of the way the system is constructed pretty much anyone can access this data should they choose to and have a little knowledge. One of the local favourites is Excel, we also have a few arbitrary Access databases doing all sorts of things. I've run out of fingers and toes to plug these gaps with so have now made sure these users are trained in SQL and have become more familiar with Query Analyser. One of my problems now is making sure that they dont' turn it to point at the production database and that they desist from using Excel, Access etc which is what my question was about.I suppose in an 'ideal' version of my world I would only like certain apps (in conjunction with certain users) to be able to access the production system. I'd like to be able to tell the Server that it was the main app that was accessing the data rather than any old Access file but am not sure how to - just to add to the joy we are using Access 97 and ODBC links (remember them anyone?)Still, at least the network admins are quiet at the moment, though one of them did come to me the other day and announce that it had taken a week to sort out a problem with a Transaction log growing too large. He didn't look too happy when I said he should have asked me steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-14 : 22:01:40
|
ouch...you should try to migrate that server to a more secured state, if not normal. Rome was not built in one day, if they desist then you're not accountable.In the end, they'll ask you and my guess is that they'll gonna blame you for not being proactive. --------------------keeping it simple... |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-12-15 : 04:10:40
|
JenI can't disagree with you but it needs a lot of work from the supplier who refuses to acknowledge that there is a risk - and to be honest wouldn't have the knowledge to improve it much anyway. I've offered to advise him but he isn't interested. It's a one man and his dog outfit and he has a near monopoly - why should he change his system when most people who use it will not realise the rick and even if they do have little alternative. He only discovered that there are such things as Views and Stored Procedures when I mentioned them to him!The consolation is that he is therefore culpable - I just need to keep reminding people here that they have been warned about this risk (and I have a trigger that logs significant changes to data)thankssteveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-15 : 08:14:34
|
I hope you didn't tell people about the trigger...that way when they FUBAR something you can nod your head sagely saying "I f---ing told ya so! Let me see if I can actually rescue something..." and go off for a couple hours and surf the web, then just run your handy data recovery script and save the day! |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-15 : 22:09:53
|
that sounds like a game plan, i like it, simple yet effective --------------------keeping it simple... |
|
|
|