Object Security (Securables)

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.

Permissions

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:

GRANT (or DENY or REVOKE) some permission
ON some securable
TO some principal
WITH some option
AS another principle

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.

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.

DDL-Related Permissions

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.

Right	Gives the Principal the Ability To
`ALTER`	Change the structure of the securable.
`CONTROL`	Have total control of the securable.
`IMPERSONATE`	Have one principal to use the rights of another.
`REFERENCES`	Access the securable "below" this one, such as a view or stored procedure to an underlying table.
`TAKE OWNERSHIP`	Take ownership of the securable.
`VIEW DEFINITION`	Examine the metadata about the securable.

DML-Related Permissions

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.

Right	Gives the Principal the Ability To
`CONNECT`	Connect to the securable.
`DELETE`	Delete the securable.
`EXECUTE`	Run the securable.
`INSERT`	Insert data into the securable.
`SELECT`	Retrieve data from the securable.
`UPDATE`	Change the data in the securable.

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.

Server

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):

Server

    ALTER ANY EVENT NOTIFICATION
    VIEW ANY DEFINITION
    VIEW SERVER STATE
    ALTER ANY LINKED SERVER
    ALTER RESOURCES
    ALTER SERVER STATE
    ALTER SETTINGS
    ALTER TRACE
    AUTHENTICATE SERVER 
    CREATE TRACE EVENT NOTIFICATION
    EXTERNAL ACCESS ASSEMBLY
    SHUTDOWN
    UNSAFE ASSEMBLY
    VIEW ANY DATABASE

Logins

    ALTER ANY CREDENTIAL
    ALTER ANY LOGIN

Endpoints

    ALTER ANY CONNECTION
    ALTER ANY ENDPOINT
    CONNECT SQL

Databases

    ADMINISTER BULK OPERATIONS
    ALTER ANY DATABASE
    CONTROL SERVER
    CREATE ANY DATABASE
    CREATE DDL EVENT NOTIFICATION
    CREATE ENDPOINT

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.

Database

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:

ALTER
ALTER ANY APPLICATION ROLE
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
ALTER ANY FULLTEXT CATALOG
ALTER ANY MESSAGE TYPE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROLE
ALTER ANY ROUTE
ALTER ANY SCHEMA
ALTER ANY SERVICE
ALTER ANY SYMMETRIC KEY
AUTHENTICATE
BACKUP DATABASE
BACKUP LOG
CHECKPOINT
CONNECT
CONNECT REPLICATION
CREATE AGGREGATE
CREATE ASSEMBLY
CREATE CERTIFICATE
CREATE CONTRACT
CREATE DATABASE
CREATE DATABASE DDL EVENT NOTIFICATION
CREATE DEFAULT
CREATE FULLTEXT CATALOG
CREATE FUNCTION
CREATE MESSAGE TYPE
CREATE PROCEDURE
CREATE QUEUE
CREATE REMOTE SERVICE BINDING
CREATE ROLE
CREATE ROUTE
CREATE RULE
CREATE SCHEMA
CREATE SERVICE
CREATE SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
DELETE
EXECUTE
INSERT
REFERENCES
SELECT
SHOWPLAN
SUBSCRIBE QUERY NOTIFICATIONS
TAKE OWNERSHIP
UPDATE
VIEW DEFINITION

Database Objects

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.

[Pages 224 - 226]

Object	Permissions Possible
Application role	`ALTER` `VIEW DEFINITION`
Assembly	`ALTER` `EXECUTE` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Asymmetric key	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Certificate	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Contract	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Endpoint	`ALTER` `EXECUTE` `CONNECT` `TAKE OWNERSHIP` `VIEW DEFINITION`
Full-text catalog	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Login	`IMPERSONATE` `ALTER` `VIEW DEFINITION`
Message type	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Remote service binding	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITIONM`
Role	`ALTER` `TAKE OWNERSHIP` `VIEW DEFINITION`
Route	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Service	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Symmetric key	`ALTER` `REFERENCES` `TAKE OWNERSHIP` `VIEW DEFINITION`
Schema	`ALTER` `DELETE` `EXECUTE` `INSERT` `REFERENCES` `SELECT` `TAKE OWNERSHIP` `UPDATE` `VIEW DEFINITION`
Table	`ALTER` `CONTROL` `DELETE` `INSERT` `REFERENCES` `SELECT` `TAKE OWNERSHIP` `UPDATE` `VIEW DEFINITION`
View	`ALTER` `CONTROL` `DELETE` `INSERT` `REFERENCES` `SELECT` `TAKE OWNERSHIP` `UPDATE` `VIEW DEFINITION`
Function	`ALTER` `CONTROL` `EXECUTE` `REFERENCES` `SELECT` `TAKE OWNERSHIP` `VIEW DEFINITION`
Procedure	`ALTER` `CONTROL` `EXECUTE` `TAKE OWNERSHIP` `VIEW DEFINITION`
Queue	`ALTER` `CONTROL` `REFERENCES` `VIEW DEFINITION`
Synonym	`CONTROL` `DELETE` `EXECUTE` `INSERT` `SELECT` `TAKE OWNERSHIP` `UPDATE` `VIEW DEFINITION`
Type	`TAKE OWNERSHIP` `EXECUTE` `VIEW DEFINITION` `REFERENCES`
XML schema collection	`TAKE OWNERSHIP` `ALTER` `EXECUTE` `VIEW DEFINITION` `REFERENCES`

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.

Principal	Table: Client	Table: Event	Table: Method	Table: Class
Jane Doe	RU	CR	CR	UD
John Smith	CR	CR	UD	UD

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.