The problem:
You read many articles and best practice documents about SQL Server security. Life would be easy if you already had security standards and everything was planned out ahead of time. But your reality might be different. You probably inherited SQL Server 2000 and SQL Server 2005 environments with dozens of servers, hundreds of databases and thousands of users. You have in-house development as well as off-the-shelf applications. There are applications using the sa as their application login, while others have granted permissions by user and object.
Permissions? Help! How do I start handling SQL Server permissions? This article focuses on ways to tackle that type of situation by reorganizing how permissions are set in SQL Server 2005.
The main goal:
The goal when modifying the security model in your database servers is to give the least permissions as possible. That way, users can view and/or modify only the data they are in charge of. Most hackers come from within the organization, however, and users that had more permissions than they needed and used them for different activities might be annoyed by the fact that the permissions were removed.
For instance, imagine the frustration of a developer who once could make changes directly in production and now has to get the DBA's approval to accomplish the same task. Like any other type of profound modification, it might take time for others to adjust to the new rules and sometimes you'll face opposition from different groups in the organization.
When is the best time to start?
In my opinion, the best time to begin reorganizing permissions is when you are planning a SQL Server consolidation or upgrade. When consolidating or upgrading databases, a testing phase is recommended and permissions can be modified and tested as part of that
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
process.
Step 1 -- Information gathering:
To establish the best plan, follow these steps:
Step 2 -- Planning:
When planning permission enhancements in your system, it is very important to use a step-by-step approach. If you attempt to use a general tactic, it's easy to fail because you may have to deal with diverse applications and/or databases differently. When you're in the planning stage, consider the following:
Step 3 -- Testing:
Like every other modification done in a database, after changes to permissions in the server/database are made, the application should be tested and approved by the development team and users. This is an important step in order to make sure the application did not break when changed.
Step 4 -- Applying:
Once the modifications are approved, they can be applied in the different environments.
Step 5 -- Maintenance:
You should have a maintenance plan in place for the new permissions -- either a third-party tool, an in-house developed tool or even manual instructions if sufficient for your environment. The goal is to not lose the momentum of an "organized" environment, security viewpoint, but to continue maintaining it according to the standards.
Conclusion:
There are no rules-of-thumb for reorganizing permissions and the security model of an environment. Devise the best plan for your specific environment depending on the applications, database types and requirements. A step-by-step approach is recommended as well as a testing environment to ensure that the reorganization is not affecting the application. Lastly, have security maintenance standards and tools for keeping a properly maintained security model.
[TABLE]
