June 5, 2007 at 10:37 am
Hi,
I am currently struggling with SQL 2000 security. Our vendor set up every user as "dbowner" and did not bother creating a new database role for their needs. The "public" role was not touched after they created the database.
I could set up a database role for "EVEYONE" but I do not know what access to give each user nor does the vendor want to make that attempt.
I would assume it would be safer, security wise to create an "EVERYONE" role and give users full access that way instead of leaving them as dbowner.
Any recommendation would be greatly appreciated.
Thank you,
John Pinola Jr
June 5, 2007 at 11:55 am
This is one of those "It depends" questions that are common in the SQL Server world. If the vendor does everything through stored procedures, for instance, then you can just give the role execute permissions on those, and they won't be able to directly access the tables, etc. So, what path you should take is almost fully dependent on how the app works.
Personally, I'd pressure your vendor to be a bit more forthcoming with what would work well for their app, but if not, you can lock things down reasonably by default, and open them up as you find the need. An alternate method would be to leave db_owner permissions for a short while, and run profiler to see what the app actually does. If it's using OLE DB to directly mess with the tables, then it gets tricky, or the vendor can work with towards another solution.
June 5, 2007 at 1:20 pm
I have a similar situation. We run different applications and most of them require the users to be db_owner.
What I have done in certain cases is make them members of db_datareader, db_datawriter, execute and add other permissions as they become necessary.
The users don't like it, because of the errors they encounter, but I am sure has avoided other, more critical problems.
June 7, 2007 at 9:41 am
It is an unfortunate situation. Vendor software coded poorly - they did not understand security, nor did they care. You are pretty much stuck with what folks have already stated.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply