January 18, 2008 at 1:32 pm
We are upgrading one of our applications to SQL Server 2005 from SQL Server 2000. The previous developers coded the application to execute all data queries/functions/procedures as the DB_owner. Using the Principle of Least Privilege, shouldn't I create a user that has DB_datareader, DB_datawriter, and execute permissions instead? Most of the data access for this application uses stored procedures with some dynamic SQL. I'm just wondering as to what would be the best way to go about securing the database since security was not up at the top of the list when they developed the application. Any ideas?
I suppose I could create a user and grant explicit rights on specific stored procedures and tables, correct?
January 18, 2008 at 1:35 pm
My suggetion would be to create a role, Let us say UserRole. Create a user with just connect permission. That means that user is just in the DB Role Public. Add this user to the role you have created.
All the stored procs should have execute permission for the role and not the user.
Just my 2 cents.
-Roy
January 18, 2008 at 1:38 pm
I agree with Roy. Creating a role with the correct (minimum) permissions to get the job done will allow you add more users, etc., to that role as appropriate, without having to go through the whole process all over again. (Planning now will save you work later, and all that.)
- 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
January 18, 2008 at 1:38 pm
Generally it is best to use a role and assign the appropriate permissions to the role, then add the appropriate users to the role. what you stated above is what I see a lot of companies have done in the past. Due to regulatory compliaince issues, we have to explicitly grant ONLY the permissions that are absolutely necessary, so we have to go out and see what tables the need select, update, insert, and delete and execute on what objects. then we assign those to a role and simply add the users to the role.
January 18, 2008 at 10:40 pm
If you used your last idea of creating separate users and assigning separate rights per stored procedure and such to individual users it would be a nightmare to keep track of. Every time you added a new procedure you would have to remember which users needed rights to it. With roles its much easier to document, especially if you divide your users up by "job", and then just keep track of which jobs need what, and which users belong to each job. Then when you need to add a permission you update the one role and all the users in that role get updated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply