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 charg...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
e 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 process.
Step 1 -- Information gathering:
To establish the best plan, follow these steps:
- Collect information on permission requirements for each application/database.
Some applications manage permissions in the application layer. You should gather the required permission in the server/database layer. This can be done via a questionnaire. The questions should be answered by the vendor or development team and by the users, if possible.
- Analyze existing server and database permissions.
You can use a third-party tool (such as Idera's SQLsecure) or develop your own. Analyzing permissions in SQL Server 2000 is not an easy task, but SQL Server 2005 has a few system functions that can assist in this analysis (i.e., fn_builtin_permissions, fn_my_permissions).
- Monitor login practices.
This is a difficult task. The goal is to be able to analyze which logins are used by each application and the correlation between the login or Windows group, the database user and in which role or roles the user belongs.
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:
- Confront the most dangerous permission outlets.
For example, applications that use sa login or a login with sysadmin privileges, applications that use the xp_cmdshell system procedure to execute operating system commands and other high-risk permissions should be handled with a very rigid approach. These are threats to your environment. Also, when possible, make sure SQL injection capabilities in the applications do not exist because this also weakens your system's security barricade.
- Take care of the databases/applications with the most clandestine data.
For example, databases holding salaries or personal information should be as protected as possible. Sometimes it is better to reduce permissions to the minimum required and add more permissions later as needed.
- Do you intend to upgrade the SQL Server 2000 database(s) to SQL 2005 in the near future?
If so, it is better to plan permissions using the SQL Server 2005 enhancements. One of the most important new features in SQL Server 2005 security is the schema-user separation. It makes database permission management much easier.
- Keep it simple.
The permission model will be complicated, so it has potential to make managing it an intricate process. The DBA's goal is to keep the data safe but without adding management overhead. You can achieve that by creating standards and developing or purchasing tools to leverage management costs.
- Establish some fundamental security standards.
Having basic rules or common standards is always beneficial. For example, you can decide that each application or database should have three main roles:
- End-user – the user with the day-to-day activities' privileges
- Privileged user – the manager of the users, with more sophisticated permissions
- Super user – a powerful user, generally with dbo permissions
Another example could be using pre-defined database roles such as db_datareader and db_datawriter. In my opinion, however, these permissions are too general and do not fit most of the applications.
A good approach in SQL Server 2005 is the schema method. If the database is designed to use different schemas for different types of roles, permission management becomes easier. The problem is that for already existing applications, making such a change might mean application modifications. There are ways to deal with these situations -- synonyms or views -- but the methods may complicate the environment and add another layer of permissions management.
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.
ABOUT THE AUTHOR:
Michelle Gutzait works as a senior database consultant for Itergy International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills also include SQL Server infrastructure design, database design, performance tuning, high availability, VLDBs, replication, T-SQL/packages coding and more.
Copyright 2007 TechTarget
