May 10, 2011 at 10:28 am
Hi all,
I have a piece of software (bought in) which, when setting up users, gives them all 'db_owner' access. Unfortunately, the SQL Server 2005 on which this database is hosted also has ODBC connections open in use on other databases, and thus it is possible for users to connect via ODBC and make any data changes they wish!
I have contacted the company and they do not have a solution, in fact they asked me to let them know when I'd worked something out... :crazy: Grr!
I think I've got two options:
1) Move the database to a server that has no ODBC access
2) Remove db_owner and allocate db_datareader, start a profiler session and and perform user tasks to see what table permissions to allow. Allow these, rinse, repeat, see what falls over when. I fear this may damage any integrity if transactions aren't in place. There are stored procedures, but no easy way of spotting items.
1) is tricky as we don't have capacity, 2) may be very time consuming and mean the system could either fall over or can't be administered properly in the front-end.
As I don't have a choice on software and its been in use here for a few years, IT are stuck supporting this at an appropriately secure level and need to find a way out. If it costs to do option 1), it will have to cost.
Any suggestions gratefully received.
Thanks
CD
May 10, 2011 at 11:27 am
Should the application have direct table access, or does it do everything via stored procedures?
- 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
May 10, 2011 at 2:29 pm
Well, first I do not know of any way to prevent ODBC connections to a database system and still allow an application to connect.
With that said, just because a user has db_owner rights in one database - does not mean they have access to any other database on the server. I do not see any need to move the database.
Since db_owner rights is really just the vendor throwing their arms up in the air and saying we are not going to figure out security on our system - there really isn't much you can do. Yes, you can use your approach to try and identify everything the application is doing - what objects are access, what tables need insert/update/delete, what stored procedures need execute privileges granted, etc... However, almost guaranteed you are going to miss something and the users are going to have problems.
What I would do is setup a logon trigger. In the trigger, you would have to check the login - identify whether or not that login only has access to that database, and if so - check the application or host they are connecting from. If not valid, then rollback the logon.
Be very careful writing this kind of trigger - you could cause yourself to be locked out of the system. I would make sure you check at the top of the trigger for sysadmin rights and exit if the login is a sysadmin.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 10, 2011 at 2:42 pm
yep i had inherited a similar situation; i created a role that had access to db_datareader,db_datawriter and EXECUTE, and replaced their original db_owner model; that worked for 99% of what the app did.
some time later we found that a couple of their more complex reports needed DDL_admin rights, because it would create a VIEW, select from that new view, and then drop the view....annoying. after i identified that, they changed the way the report was generated, and they did not need that anymore.
Lowell
May 11, 2011 at 11:14 am
Hi guys,
Thank you for your responses.
I understand that other databases cannot be accessed, but db_owner on this database is bad enough as the financial data within can be changed 'round the back'.
I will try the firm again regarding if everything is performed using stored procs, though I think the user would still need write access to the tables.
Lowell's idea sounds the quickest, though doesn't db_datawriter still give write access via odbc? Would that role still require update/delete permissions on the tables?
I could also try to reduce usage by replacing functionality for users who do access the system - i believe most of them are only read only and don't use it very often. Some pertinent Reporting Services reports may reduce the potential exposure and thus impact, assuming the type and variety of data accessed is not very large and I can work out the schema...
Cheers chaps,
CD
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply