SQL Server Reference Guide

I'm continuing a previous discussion of SQL Server Security in this series, where I've already described security in general, on the specific platform, as well as a graphical step-by-step tutorial on how to create users and groups of users called "roles" in the last tutorial.

I've been comparing a SQL Server setup to a bank building, and the users in a database to the patrons of a bank. For instance, in my bank account, the company I work for is allowed to put money into one of my accounts, but they can't see any of the other accounts. Not only that, they are only allowed to deposit money — they can't take any out. Credit companies, on the other hand, can inquire about my balances but can neither put money in nor take money out. Different patrons in the bank setting have different rights and privileges.

It's the same with the database, but you're working with users and objects (like tables) instead of patrons and accounts. Users have different kinds of access to different objects in the database.

In fact, the database objects aren't the only things that you can secure. You can create rights and privileges for everything from the server itself down to each database and each object that they contain. Microsoft calls these items "securables," although I'll mostly refer to each object by its individual name. In this tutorial I'll focus only on database objects.

There are multiple objects in the database that you can secure. Here's a partial list of the SQL Server securables for a database:

• Aggregate
• Constraint
• Function
• Assembly
• Message Type
• Fulltext Catalog
• Certificate
• Statistic
• Synonym
• Procedure
• Queue
• Route
• Role
• Application role
• Object
• Service
• Remote Service Binding
• View
• Symmetric Key
• Contract
• Schema
• Table
• Asymmetric Key
• User
• Type
• XML Schema Collection

As you can see, there are quite a few things you can secure in SQL Server.

I'm going to focus on just two objects to keep this tutorial simple: tables and stored procedures. The reason for that is that the way you work with a table is similar to the way you work with any "static" object. The way you work with a stored procedure is similar to the way you work with most any "code" objects. Of course there are exceptions, but I'll focus on just these two to keep the tutorial simple.

Also, although you can set the permissions for these objects graphically, I'll show you the three basic commands you need to set the security in Transact-SQL (T-SQL). This will keep the tutorial consistent, and most of the time you'll work with scripts in your own databases. Although creating one or two users is easier in the graphical tools, it can be more efficient to work with commands when you're setting up security.

Remember from the last two tutorials that I'm not just talking about users for object access, but any "thing" you've allowed in the database. Microsoft calls users, groups and the like "principals." You will more than likely create groups of users (roles) that you'll give access to various objects. That simplifies your security a great deal.

As I mentioned, there are only three basic commands you need to learn for security: GRANT, DENY and REVOKE. The first two commands are fairly straightforward. GRANT allows users (or any principal) to do something with an object. DENY prevents a principal from doing something with an object. REVOKE, on the other hand, removes the permissions from a user, unless they belong to a role where they do have the right to work with the object. I'll explain this with an example or two.

I'll create a simple database to work with called "PermissionsExample," add a table, and then add a couple of users. You can use this script to create that whole setup by copying this code (Don't do this on a production system — the passwords are far too weak!):

 /* Set up the database */
CREATE DATABASE PermissionsTest
GO

USE PermissionsTest
GO

/* Create two tables */
CREATE TABLE AccountInfo
( AccountID int
, AccountName varchar(50)
, AccountLocation varchar(50)
)
GO

CREATE TABLE AccountBalance
( AccountID int
, CurrentBalance money
, CurrentBalanceDate datetime
)
GO

/* Fill the tables with data */
INSERT INTO AccountInfo
VALUES (1, 'Checking', 'Tampa')
INSERT INTO AccountInfo
VALUES (2, 'Savings', 'Florence')
GO

INSERT INTO AccountBalance
VALUES (1, 500, '12/05/1985')
INSERT INTO AccountBalance
VALUES (2, 1000, '12/05/1985')
GO

/* Create two users to work with */
EXEC sp_addlogin 'Buck', 'password', 'PermissionsTest'
EXEC sp_grantdbaccess 'Buck', 'Buck'

EXEC sp_addlogin 'Marjorie', 'password', 'PermissionsTest'
EXEC sp_grantdbaccess 'Marjorie', 'Marjorie'
GO 

That script creates a database, adds two tables, a view and a stored procedure. It then creates two SQL Server logins, and gives them access to a database. All this you've seen in earlier tutorials. Note that in newer versions of SQL Server the proper commands for creating a server login and database user are CREATE LOGIN and CREATE USER respectively - but the two stored procedures I show here are still in use as of this writing. Either will create a SQL Server login for you - assuming that your Instance of SQL Server is set to allow both Windows and SQL Server Logins (sometimes called “Mixed Authentication).

So at that point, what can the users do? Well, they can log in to the server and access the database with a "USE PermissionsTest" statement. That's it.

To allow Buck or Marjorie to access data in a table or other object, I'll need to use the GRANT statement. At this point it's Important to think about what you want these users to be able to do.

To get that right, you need three pieces of information: Who you want to allow into the object, the object you want them to work with, and what level of access you want them to have.

You can let users view the data using the SELECT statement, put new data in using the INSERT statement, delete data using the DELETE statement, or change one value to another using the UPDATE statement. Here's the general format:

GRANT some permission (statement)
ON some object
TO some principal

Now all I have to do in this example is plug in the permissions, objects and principals to allow the user to do what they need. I'll allow Buck the ability to read data from the AccountInfo table:

GRANT SELECT 
ON AccountInfo
TO Buck
GO 

The interesting thing is that you can combine permissions in a single GRANT:

 GRANT SELECT, INSERT, DELETE, UPDATE 
ON AccountInfo
TO Buck
GO

Since that's a lot of typing, Transact-SQL has a single command to give those kinds of permissions at one time, by specifying ALL instead of the permissions. Here's how you could do that:

GRANT ALL
ON AccountInfo
TO Buck
GO 

Now Buck can see, enter, remove and alter data in the AccountInfo table. There's one other part to the GRANT command we need to look at. You may want to allow Buck to be able to give other people permission to this table. You can do that by adding the WITH GRANT option to the end of the GRANT statement, like this:

 GRANT ALL
ON AccountInfo
TO Buck
WITH GRANT OPTION
GO

So far, Buck can see and manipulate all rows in the AccountInfo Table. Let's allow Buck to see everything in the AccountBalance table:

GRANT SELECT
ON AccountBalance
TO Buck
GO

Now Buck has different rights on different objects, but Marjorie can't do anything. Assume that you want to allow Marjorie to look at both account information and balances, but only for the Melbourne location. You can't grant permissions only on certain rows in both tables, but you can create a view that shows only that data and then grant Marjorie permission to use the view. In fact, this is a common use-case for views. Here's an example:

/* Create a view showing the account balances
only for the Melbourne accounts */
CREATE VIEW MelbourneAccounts AS 
SELECT a.AccountName
, a.AccountLocation
, b.CurrentBalance
FROM AccountInfo a
INNER JOIN AccountBalance b
ON a.AccountID = b.AccountID
WHERE a.AccountLocation = 'Melbourne'
GO
/* Grant Marjorie permissions to the view */
GRANT SELECT
ON MelbourneAccounts
TO Marjorie
GO

Keep in mind that in this sample I granted Marjorie access to the view, but a view is just a SELECT statement — it doesn't hold any real data. I never granted Marjorie access to either of the two tables underneath (often called base tables). The view still works for Marjorie because of something called a permissions "chain." That means if the same user creates one object that depends on another, like a view on a table, permissions only have to be granted on the "parent" or top object, like the view.

Now Marjorie can query the view and get all of the rows that have Melbourne information.

So far no one can update any values in the AccountBalance table. I've decided that I  want to allow Buck to update the current date in that table, but nothing else. To do that I can create a stored procedure that will work similar to the view, but only allow the user to do what I want by writing the appropriate code in the stored procedure. It uses the same permission chains to make this work. Here's an example:

/* Create a Stored Procedure that updates 
the account Balance Date */
CREATE PROCEDURE UpdateBalanceDate 
@NewDate datetime
AS 
UPDATE AccountBalance
SET CurrentBalanceDate = '01/31/2006'
WHERE CurrentBalanceDate = '12/05/1985'
GO

The only thing that is different about this set of statements is that the user doesn't access this program using a SELECT or INSERT statement, because this object is a program object. In this case I grant the EXECUTE statement to Buck:

GRANT EXECUTE
ON UpdateBalanceDate
TO Buck
GO

Now Buck can execute the stored procedure, and the data is changed on his behalf. Of course, to truly make this useful, I'd allow Buck to send the date he wants as a variable — this one simply makes all the dates recorded here the same, at least the ones that meet the WHERE criteria.

In future tutorials I'll expand on these statements to show you how to build more complex security features.