The overall security scheme in SQL Server 2005 is the intersection of principals and securables. Principals, as you have just seen, include principals and roles. Securables are the objects you can secure, such as databases and the objects they contain. The security for your data and objects involve giving the principals rights and permissions to the securables. The security for the system objects works in a hierarchy, with the server at the top level and the database objects at the bottom. Let's examine the permissions process and then work our way down the hierarchy to learn how to create a security matrix.
All database object permissions intersect with three basic commands and two classifications of those commands. The commands are GRANT, DENY, and REVOKE, and the two classifications are Data Definition Language (DDL)-related statements and Data Manipulation Language (DML)-related statements.
The GRANT permission allows a principal to perform an action, the DENY permission prevents them from performing it, and REVOKE takes away a privilege without affecting the role or other specifically GRANTed permissions. The basic format for applying permissions using T-SQL is this:
I flesh out this format as we move along, but you can use that little outline to create the most complicated security matrix.
There are also graphical methods for assigning permissions, which many people find a lot easier to use. Using the SQL Server Management Studio, right-click a securable such as a database or a table and select Properties from the menu that appears. Navigate to the Permissions section and add any principals you want from there. You can see that in Figure 4-21.
[View full size image]
Graphical methods are useful but not practical for bulk situations. To grant or deny massive amounts of rights to multiple principals, you will normally create large scripts. The rest of this section shows you how to build your security statements from the basic format shown previously. This example sets the same permissions as the previous graphic:
use [DBAMDT] GO GRANT INSERT ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] WITH GRANT OPTION GO GRANT REFERENCES ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] WITH GRANT OPTION GO GRANT ALTER ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] GO GRANT CONTROL ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] GO GRANT DELETE ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] GO GRANT SELECT ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] GO GRANT UPDATE ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] GO GRANT VIEW DEFINITION ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] GO DENY TAKE OWNERSHIP ON [dbo].[ServerInfo] TO [LOGCABINPRESS\Buck] GO
Although it may seem like a lengthy process, creating a script like this is far quicker than clicking through hundreds of tables, stored procedures, and views to set up security. Although the syntax for adding permissions to securables for principals looks simple, it is in the layering where it is easy to become confused.
To properly create your security layout, you need to understand the permissions hierarchy within SQL Server. I cover the full hierarchy in a moment, but for this part of the discussion, the important separation is the base unit of a table and the items that call or reference a table, such as a view or a stored procedure. If a principal is granted the ability to select from a view but not from the table it references, the principal will not see any data. If, however, a single principal creates all the objects in a database, granting a higher-level object permissions implies the permissions below it. In other words, if a developer creates a view and a table and then grants other principal rights to the view, the other principal can use the view even without explicit rights to the table. The developer can still deny or revoke the rights for the principal to the table if he wants.
To help keep this layering straight, I normally create a matrix to help sort it out. Another useful tool is using the Permissions panel I showed you earlier. There is a button there called Effective Permissions that will show you the "real" permissions the principal has. For instance, if you grant the principal the right to use the view but deny them the right to the table it references, you have removed their ability to see the data. Clicking this button helps you see what they really have.
Let's examine the permissions and see what each provides. Not all of these permissions apply to every object, so I show you a chart in a few moments that shows where each is used.
Although these commands are not categorized strictly as DDL, they do affect the DDL operations a principal can perform. You can use this chart to begin the layering of the security for each object.
These commands affect how the principals are allowed to access data, such as viewing, inserting, editing, and deleting data. This chart shows the data access part of your security layers.
The Security Hierarchy
Not all of the rights you have just seen apply to every securable. The securables on the system range from the server, which contains logins, endpoints, and databases, down through the database, which contains objects such as tables and stored procedures. Each of these is secured using the GRANT, DENY, or REVOKE commands, followed by the permission, and then the securable you want to protect.
The server object is at the top of the hierarchy, because you can conceptually consider it as the engine for all of the objects it contains. Most of the rights and permissions at this level deal with maintenance and control of the system rather than the data it contains. Earlier I explained that you cannot add new fixed server roles to help with these functions, but the server object has several control permissions associated with it. Here is a list that separates the permissions for each of the objects the server contains. Some of these permissions, such as VIEW ANY DEFINITION, apply not only to one category (Server) but to others (Databases):
The CONTROL SERVER is the highest permission you can grant, somewhat equivalent to adding a principal to the sysadmin fixed server role. You should grant only those rights a principal needs to do the jobdo not use the "shotgun" approach of granting more rights than are needed simply because it is easier.
The database securable has permissions that are similar to the Server object. Most of them have to do with controlling the database. A few have to do with data access, but most of those are down at the object level, which I explain next.
The permissions on the database range from the specific, such as the ALTER permission, down to the general, such as ALTER ANY APPLICATION ROLE. This allows your security to be as granular as you need it to be.
Here is a list of the database securable permissions:
The permissions for a database securable are based on the securable itself. For instance, you can get data from a table by SELECTing it, but you EXECUTE a stored procedure.
You can use the following chart to help you build a security statement. Place the GRANT, DENY, or REVOKE verbs in front of the object, add the permissions possible next (if you are GRANTing the permission), then add the principal name, and finally add your choices from the permissions possible. Adding a WITH GRANT OPTION means that the principle can also grant the rights to others, and adding AS sets the role that the permission is derived from.
To put everything together, I have found it is easiest to use a security matrix. Take a spreadsheet and list the principals down the first column. Across the rows, list the securables you have been given by the developers. Below the securables, list the permissions for that object. Here is an example.
In this example, the C stands for create (or insert), the R stands for read, the U stands for update, and the D stands for delete. This is often called a CRUD matrix, and the developers might have already created it for you.
With this matrix in hand, you can now put the commands, the objects, and the permissions together to create the security. Remember that GRANT allows, DENY prevents (and trumps all other permissions), and REVOKE removes the permission but does not deny it if the principal has the right in another way, such as through a role membership.
Because Jane Doe in the example CRUD matrix needs read and update on the table called Client (which uses a schema called Teachers), you would run this code:
USE [SchoolDB] GO GRANT SELECT, UPDATE ON [Teachers].[Client] TO [LOGCABINPRESS\DoeJ] GO
With a little work, you can also have the spreadsheet create the commands for you, ready to cut and paste into your script.
You can also right-click a securable and grant rights as I mentioned earlier; if you have several to do, however, the scripts are faster. You can also save those scripts out to audit security later or to compare against your CRUD matrix.