site:http://articles.techrepublic.com.com/5100-22_11-5035150.html?tag=rbxccnbtr1
Takeaway: Security in the development of a SQL Server database must be a priority right from the start, beginning with the design process. Familiarize yourself with these guidelines before you start your next project and you will prepare a more secure database application.
By Susan Sales Harkins and Mike Gunderloy
Developers and administrators are constantly forced to rethink their strategies on application security—adding safeguards as an afterthought is clearly no longer acceptable. Security must be a priority right from the start, beginning with the design process. The next time you’re responsible for developing a SQL Server database, follow these guidelines to prepare a more secure application from the get-go.
The basics
If you don’t understand the basic concepts of SQL Server security, stop right now and read up on them; you can’t properly secure a database without knowing these concepts. It may help to view the security machinery as a car. You have an engine, a key, and all the processes that occur between the key turning in the ignition and the engine roaring to life. If you’re ignorant of those processes, the chance of successfully troubleshooting any problems is very low. You can always take your car to a mechanic, but in the case of application security, youare the database mechanic.
To begin with, you can choose between two security modes:
- Windows Authentication Mode: Users connect to a server via an existing Windows user account. When a user attempts to connect to a server, SQL Server validates the user's Windows account name and password. The user doesn't have to log in to both the network and SQL Server; one login is all that's required. This approach is also called a trusted connection.
- Mixed Mode: This mode combines Windows Authentication and SQL Server Authentication. Users can connect via a Windows user account, just as in Windows authentication mode. But you can also create user accounts directly in SQL Server that bear no relation to Windows accounts. Each SQL Server account stores a username and password.
We recommend using Windows Authentication mode when possible. However, Mixed mode is required when working with SQL Server 7.0 (or earlier). SQL Server Authentication (Mixed) is provided for backward compatibility with earlier versions of SQL Server. Windows Authentication is integrated with the Windows security system, which provides more features than SQL Server Authentication and is generally easier to use, more efficient, and more secure. You'll want to decide early in the design process which mode you want to apply.
Passwords
Regardless of which mode you work with, always remember to set a password for the system administrator (sa) user in SQL Server itself. When you install SQL Server, it automatically creates an administrative user with the SQL Server login name sa and a blank password. If you leave this user untouched and use Mixed Mode security, anyone with a basic knowledge of SQL Server can easily log in to your database and do whatever they like. If you’re using Windows Authentication Mode, in theory you don’t have to set a password for the sa user, because SQL Server logins won’t be accepted. But setting one anyway is a good practice, just in case you’re forced to switch to Mixed Mode in the future.
If the security mode is the engine, logins are the keys that turn over the engine. You must have the right key to start your engine. Logins are the same way—if you don't enter the proper username and password, you can't connect to the network, and subsequently, SQL Server.
As administrator, once you've unlocked the ignition and started the engine, you're ready to drive. You'll do so by defining the following attributes:
- User (account): A SQL Server security account that represents a single user. A user has a Windows account or a SQL Server login, which corresponds to a user account in a database.
- Group (account): Each user can belong to one or more groups, defined in Windows or in SQL Server depending on your authentication mode. Each group has specific permissions. As a member of a group, you acquire all the group's permissions.
- Object ownership: Ownership belongs to the user who creates the object. Owners can grant access to other users. If you own a view, for example, you can decide which users should be able to see data through that view.
- Permission: A permission represents the ability to perform some action, such as opening a view or modifying a stored procedure. SQL Server recognizes three states for permissions: GRANT gives a user access; REVOKE removes it; and DENY keeps a user from accessing the object.
- Role: This is a SQL Server security account that treats a collection of accounts as a single unit when managing permissions. In a nutshell, roles define what a user can and can't do in a particular database.
Security by design
The design process is the best place to define just what needs to be secured and how. Early in this process you'll watch out for two qualifiers:
- Sensitive data
- Who can see sensitive data
Sensitive data can be any data, including all of the data. There are some databases that are so sensitive that access to all data is restricted—but this level of security is rare for most of us. Your job is to protect data that's defined as sensitive within the context of the business and the database.
The authentication mode you choose and the logins that you create will enforce the first step of security by limiting which users get into the database. Sometimes, that's the only security a database needs.
The next step is to list all users who should have access to the database and then decide whether all the data is appropriate for all users. Often, you'll have data, such as salaries or other personal data, which you'll want to protect. That means allowing only certain users to access and view that data. You'll also need to decide who can alter that data, which may very well be two different groups of users.
One cardinal rule to keep in mind is the notion of “least privilege.” If someone doesn’t need particular data to do their job, don’t let them have it. Avoid the temptation to make all users the equivalent of the sa user, just because it makes your job as a developer easier. Users should be able to view and alter only the data that business requirements dictate.
Concrete advice
Experience is the best teacher when it comes to security, but there are a few guidelines that are fairly universal to every database:
- Get ownership of databases and objects right from the start. When you create a new database, you become the database owner and can dictate everything that happens in that database. Ideally, you should use a login reserved for administrative jobs for this task, not one that is an everyday user. Similarly, objects are owned by the user who created them. Though it’s possible to transfer ownership, it’s easier to make sure that a special administrative login is used to create all objects.
- Understand ownership chains. This security feature prevents users from creating their own views to sneak into sensitive data that they don’t own. For example, suppose you create a view that draws together data from two tables. If you are the owner of both tables, SQL Server does not check permissions on the tables when you give someone else permission to use the view. But if another developer owns one of the tables, SQL Server will check that table’s permissions in addition to the view’s permissions.
- Use views and stored procedures to grant users access to data instead of forcing them to write ad hoc queries that directly access tables. This way, you don’t need to grant users permissions on the underlying tables. Views and stored procedures can also limit the data users can see. For example, if your employee table contains confidential salary information, you can create a view that simply omits the salary column.
- If users get to your database from particular applications, you may want to create application roles. An application role is like a user assigned to a particular application, and can be granted its own permissions. With application roles, users don’t authenticate directly to the database; instead, they authenticate to their own application, which decides which application role to present to the server.
- Keep up with service packs. Admittedly, service packs are a mixed bag of tricks. Service releases, patches, and other updates often introduce new problems. Regardless, staying current on service packs is probably the best and easiest step you can take toward protecting your data from outside abuse. Visit Microsoft's SQL Service Pack download page to find the most current service pack.
The bottom line
Security is every developer’s business. Don't wait until the database is designed and in use—security is an integral part of the design process. In addition, don't just apply security haphazardly and hope for the best. Learn the security models and apply them appropriately.
