Security and the Data Path

In Chapter 2, "Architecture and Tools," I explained the system architecture by showing the path a query takes from a data perspective. It makes sense to follow that same procedure here, because knowing where the packets go affects what you have to be concerned about from a security standpoint. This overview will help you navigate the rest of the chapter.

Let's start with the concept of endpoints. An endpoint is some combination of transport, such as TCP/IP or HTTP, and a payload, such as a T-SQL query and its results in a Tabular Data Stream (TDS) or a Simple Object Access Protocol (SOAP) call. In effect, it is the interaction between the user (principal) and the database services. The server listens using the Browser Service, which redirects the request to the proper port for a particular instance. You do not have to use this service if your application is using the default instance port (1433) or knows which port to connect to.

You can encrypt the transmissions across the channel and use a certificate between the two systems. A certificate is a piece of software that guarantees an identity. SQL Server 2005 can generate a self-signed certificate for the session, which protects against most "man-in-the-middle" attacks. You can also use a certificate from a certification authority, which provides better protection. In either case, to use the certificate by default, you need to set the Force Protocol Encryption switch on the server using the SQL Server Configuration Manager.

Once the connection is configured, the principal is authorized to an endpoint using either a Windows login or a login created and managed by SQL Server 2005, both of which grant entry into the server. After that, the principal is authenticated (using a user identity value) directly through their account or by virtue of a group (called a role) to perform actions in or on the database.

If you are not using Windows accounts but you are using Windows 2003 Server, SQL Server 2005 can enforce the password policies controlled by the operating system. Otherwise, you can set up a few simple policies within SQL Server 2005 such as password length, complexity, and lockout durations. You can also force the principal to change their SQL Server 2005 login password the first time he or she connects, and you can disable accounts without deleting them.

You can secure the following objects, which Microsoft calls scopes:

Server
Database
Schema
Other objects such as tables and views

Each of these scopes contains other objects, which we explore throughout the chapter. As the administrator, you can grant access to change a table (DDL-type statements) or to view, edit, add, or delete the values (DML-type statements) the table holds. Normally if a principal has the ability to use DDL statements, that principal also has the ability to use DML statements.

DBA 101: DDL and DML

In the Structured Query Language (SQL), there are two types of commands (called statements) that you can use. The first is called Data Definition Language (DDL). These commands include CREATE, DROP, ALTER, CONTROL, TAKE OWNERSHIP, and GRANT. They allow a principal to add, delete, change, or allow access to constructs such as tables and views inside the database, or even the database itself.

The second set of statements is called Data Manipulation Language (DML). These statements include SELECT, INSERT, UPDATE, and DELETE. Granting these statements to a principal allows the principal to add, change, and delete data in tables. Another set of DML statement are the EXECUTE and EXECUTE AS statements, which allow principals to run a stored procedure or function. To secure the metadata within the server, you can also grant or deny the text that shows the object's T-SQL creation using the VIEW DEFINITION statement.

In the sections that follow, I explain these statements and how you use them.

Security principals of the "user" type do not own objects within a database; instead, the schema object holds all database objects. Using a fully qualified name in the format servername.database.schema.object, you can grant or deny access on a single object to a principal with fine granularities. Using a schema allows you to bring users accounts in and out of the system without "orphaning" other objects.

Your developers have a couple of other measures to control security, which do not require you to set privileges and rights on securables. They can use tables within the application to implement their own security, or they can use an application role, which I describe shortly. If they use one of these other constructs, you need to be aware of what they are doing, but you will not always be in control of the application security. You will, however, still be in charge of overall data security, so it is best to understand the way the application implements security.

Let's explore the outermost security layer and some of the tools and techniques you can use to develop and implement your security plan. This process involves thinking about of every place that the data travels and identifying each intersection in that path as a security risk. Documenting the path and the data exposure points forms the core of your security plan. The rest of the plan details what you have done about those risks.