Maintaining Security and Performance Using Stored Procedures Part II – Signing

Well, it has been a couple of weeks since my last blog post and over a month since Maintaining Security and Performance Using Stored Procedures Part I – Using EXECUTE AS was posted, although I did spend time working on the originally unplanned follow up to that post when I would have been doing this post.  I know you all have been anxiously awaiting this post.

EXECUTE AS vs. Signing

Like EXECUTE AS, the ability to sign a module (stored procedure, DML trigger, function, or assembly) with a certificate was added in SQL Server 2005.  In addition to being able to allow access to objects within the current database context, signing also allows you to access resources in another database or that require server level permissions.  With EXECUTE AS on functions, stored procedures, and DML triggers you are limited by database context, so if you need access to objects in another database you are out of luck, even if the EXECUTE AS user has proper rights in the database.  You can download code that demonstrates this behavior here.

Demonstrations of Signing

Laurentiu Cristofor has an excellent blog post that demonstrates signing a stored procedure to grant server level permissions here, so I am not going to duplicate his work in this post.  I will demonstrate how to sign a procedure for use within a database and when accessing another database.

Using Signing to enable Dynamic SQL within the database

USE AdventureWorks;
GO

/*
Create a restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

/*
Create a Certificate first
*/    
CREATE CERTIFICATE cert_dynamic_sql 
ENCRYPTION BY PASSWORD = 'c3rtificatePa$$w0rd'
WITH subject = 'Dynamic SQL Security'   
GO 

/* 
Create user based on the certificate
*/ 
CREATE USER certificate_user FROM CERTIFICATE cert_dynamic_sql;

/*
Give the certificate_user select on all objects
*/
GRANT SELECT ON SCHEMA::Person TO certificate_user;
GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE PROCEDURE dbo.FindPhoneByName
(
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null
)
AS   
BEGIN
 SET NOCOUNT ON;

Declare @sql_cmd nvarchar(2000),
@select nvarchar(1000),
@where nvarchar(1000),
@parameters nvarchar(1000);

 Set @parameters = N'@FirstName nvarchar(50), @LastName nvarchar(50)';

Set @select = N'Select
Title,
FirstName,
MiddleName,
LastName,
Suffix,
Phone  
From
Person.Contact';

Set @where = N' Where 1=1 '                

If @LastName is not null
Begin
Set @where = @where + N' And LastName Like @LastName + N''%'' ';
End;

If @FirstName is not null     
Begin
Set @where = @where + N' And FirstName Like @FirstName + N''%''';
End;

Set @sql_cmd = @select + @where;

Exec sys.sp_executesql @sql_cmd, @parameters, @LastName = @LastName, @FirstName = @FirstName;

Return;         
END

GO

/*
Give the restricted user exec on the proceduere
*/
GRANT EXEC ON dbo.FindPhoneByName TO restricted_user; 

GO
/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will fail on the dynamic portion
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO

/*
Sign the procedure
*/
ADD SIGNATURE TO [dbo].[FindPhoneByName]
BY CERTIFICATE cert_dynamic_sql
WITH PASSWORD = 'c3rtificatePa$$w0rd';
GO

/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will work now
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO

Using Signing to Access an Object in Another Database

In this example I’ll use a signed procedure to access a table in the Northwind database (download here) from the AdventureWorks database (I use a 2005 copy with extra data, the unmodified version is available here).  One thing I found in my testing is that you have to use a private key file in this case.  If anyone knows how to do it without the file please let me know.  Here is the code:

USE MASTER;
GO

/*
Create the restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

USE Northwind;

GO

/*
Create the Certificate
*/    
CREATE CERTIFICATE cert_access_other_db 
ENCRYPTION BY PASSWORD = 'c3rtPa$$word'
WITH subject = 'Access Other DB'   
GO 

/* 
Backup the certificate being sure to use a Private Key
*/
BACKUP CERTIFICATE cert_access_other_db TO FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk' ,
ENCRYPTION BY PASSWORD = '3ncRyptKeyPa$$word',
DECRYPTION BY PASSWORD = 'c3rtPa$$word');
GO

/*
Create the certificate user in the Northwind and give needed permissions
*/
CREATE USER certificate_user FROM CERTIFICATE cert_access_other_db;

GO

GRANT SELECT ON dbo.Categories TO certificate_user;

GO 

USE AdventureWorks;

GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE PROCEDURE [dbo].access_other_db
AS
SET NOCOUNT ON

SELECT 
SYSTEM_USER AS USERName, 
*
FROM
[Northwind].dbo.[Categories] AS C;

RETURN;    

GO

/*
Give the restricted_user execute rights on the sp
*/
GRANT EXEC ON dbo.access_other_db TO restricted_user;

GO

/*
Create the certificate in this database from the file
*/
CREATE CERTIFICATE cert_access_other_db FROM FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk',
DECRYPTION BY PASSWORD = '3ncRyptKeyPa$$word', /*The password used to create the private key */
ENCRYPTION BY PASSWORD = 'D3cryptKeyPa$$word');
GO

/* 
Execute as a sysadmin - works - this is my user
*/
EXEC [dbo].[access_other_db];

GO


/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will fail.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Sign the procedure 
*/
ADD SIGNATURE TO dbo.access_other_db
BY CERTIFICATE cert_access_other_db WITH Password = 'D3cryptKeyPa$$word'
GO

/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will now work.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Be sure to delete the certificate and
private key when done.
*/

Summary

As you can see from this post and the previous post (or two), the SQL Server team has given you some good options when it comes to using Stored Procedures for data access and manipulation while still maintaining security.  You can use EXECUTE AS to allow cross-schema or within database access and you can use module signing to allow access to system objects or cross-database queries without specifically granting users access to the objects.

Resources

After having planned and started the post because I had not found anything outside of Books On Line, I found a few resources that covered the material as well, and I used each to help me write this post once I found them.

• I linked to Laurentiu Cristofor’s post earlier
• Erland Sommarskog has an excellent write-up on Giving Permissions through Stored Procedures which handles this subject very thoroughly as you would expect form Erland.  Erland’s post helped me get past the fact that I needed to use a private key in order to get the cross database piece working as none of my other resources did this.
• Jonathan Kehayias also answered this this forum post with an example.

In reality I could have just posted these links as these other folks covered the subject on signing thoroughly, but I decided that since I did the research and wanted to try the code myself that I’d share my experiences as well, crediting these guys for their work and hopefully sending them some traffic, however limited, from this blog.

Finally all the MY code from this post can be downloaded from here.

Maintaining Security and Performance using Stored Procedures Part I – Using EXECUTE AS

Anyone who knows me, or has worked with me, knows that I am a proponent of using stored procedures for all database access.  I believe that using stored procedures makes your database more secure and makes it simpler to maintain a well performing system.  One area where stored procedures are more difficult to work with than building queries in the GUI or business layer are with dynamic search queries.  Here are some traditional issues with dynamic search in stored procedures:

1. If you use traditional IF, ELSE statements to build the procedure you get a long and hard read procedure, and you are less likely to get plan re-use.
2. If you try tricks like WHERE LastName = IsNull(@LastName, LastName) and FirstName = IsNull(@FirstName, FirstName) you can get plan re-use, but the plan used may not be, and many times is not, the best plan to use.
3. If you use dynamic SQL using the EXEC (@sql) syntax you do not get plan re-use, you open yourself up for SQL Injection, and, prior to SQL Server 2005, you had to grant access to the objects used in the query defeating part of the reason for using stored procedures in the first place.
4. If you use dynamic SQL using sp_executsql and parameters you are more likely to get plan re-use, you are safe from sql injection, but, pre-2005, you still needed to grant access to the queried objects.
5. Either dynamic SQL option means creating a large string of SQL and concatenating it, so it can be and, in my opinion is, a pain to read and a pain to make sure you have all your syntax right.

See Erland Sommarskog's excellent articles, The curse and blessings of dynamic SQL and Dynamic Search Conditions, for more details.

Early in my career, when working with SQL Server 7 and 2000 I tended to use option 1, sometimes with temporary tables, then I moved to option 2.  I never used dynamic SQL because I did not want to grant select access to the tables being queried.  I sacrificed performance for security and counted on ownership chaining to handle access to the underlying tables.  With the advent of SQL Server 2005 and the EXECUTE AS I have moved to option 4, dynamic SQL using sp_executesql and parameters as I believe it gives me the best of both worlds.

How’s it work

Essentially you create the stored procedure and add the WITH EXECUTE AS Caller/Owner/Self/’user name’/’login name’ (see the Books on Line entry for EXECUTE AS for more details) and this changes the context in which the code within the procedure is run.  So you can create a user (SelectAll) in the database that has select rights on all the tables and then no matter who calls the stored procedure the procedure will run correctly.  If you choose to use EXECUTE AS OWNER then the procedure executes in the security context of the Owner of the procedure so you can simulate ownership chaining. 

Example

Security

A post like this wouldn’t be complete without at least a simple example.  I will be using the AdventureWorks database (get it at CodePlex, I’m still using the 2005 version) with some added data (I used RedGate SQLDataGenerator).  All the code to run the examples is available for download here.

First you need to create a user with limited persmissions:

Use AdventureWorks;

Go

Create User DynamicSQLTest without login;

Notice that I used the Without Login syntax so I did not need to create a login as well.  This is because I will also be using EXECUTE AS before running the stored procedure to change my execution context to this limited rights user instead of creating a new connection with a limited rights login.  Next you need to create the stored procedure.  I’m going to start with a “normal” stored procedure using Option 2 from above, because I also want to demonstrate the difference in performance.  Here’s the procedure:

Use AdventureWorks;
GO

IF OBJECT_ID('dbo.FindPhoneByName', N'P') Is Not Null
    Begin
        Drop Procedure dbo.FindPhoneByName;
    End;

Go
    
CREATE PROCEDURE dbo.FindPhoneByName
    (
    @LastName nvarchar(50) = null,
    @FirstName nvarchar(50) = null
    )
AS

BEGIN
    SET NOCOUNT ON;

    Select
         Title,
         FirstName,
         MiddleName,
         LastName,
         Suffix,
         Phone  
    From
        Person.Contact
    Where 
        LastName Like IsNull(@LastName, LastName) + N'%' And 
        FirstName Like IsNull(@FirstName, FirstName) + N'%';
        
    Return;         
END
GO

This procedure is pretty self explanatory.  Now we need to give the limited rights use, DynamicSQLTest, execute rights on our procedure:

Use AdventureWorks;

Go

Grant Exec on dbo.FindPhoneByName to DynamicSQLTest;

To test the security and performance of the stored procedure we are going to execute it 3 times with a dbo user and then repeat as the limited rights user, DynamicSQLTest.  Here is what I used:

Exec dbo.FindPhoneByName @FirstName = 'J', @LastName = 'A';

Go

Exec dbo.FindPhoneByName @FirstName = 'J';

Go

Exec dbo.FindPhoneByName @LastName = 'A';

Go

Then execute the same 3 calls, but run this first to change the security context:

Execute AS User = 'DynamicSQLTest';

Go

If you are running the code in the same SSMS session be sure to issue the REVERT command to return to your original security context.

The stored procedure calls should run successfully for both users and should produce the same results and performance for both users.  Now we’ll ALTER the procedure to use dynamic SQL:

Alter PROCEDURE dbo.FindPhoneByName
    (
    @LastName nvarchar(50) = null,
    @FirstName nvarchar(50) = null
    )
AS

BEGIN
    SET NOCOUNT ON;

    Declare @sql_cmd nvarchar(2000),
            @select nvarchar(1000),
            @where nvarchar(1000),
            @parameters nvarchar(1000);
    
    Set @parameters = N'@FirstName nvarchar(50), @LastName nvarchar(50)';
    
    Set @select = N'Select
                     Title,
                     FirstName,
                     MiddleName,
                     LastName,
                     Suffix,
                     Phone  
                From
                    Person.Contact';
    
    Set @where = N' Where 1=1 '                

    If @LastName is not null
        Begin
            Set @where = @where + N' And LastName Like @LastName + N''%'' ';
        End;
        
    If @FirstName is not null     
        Begin
            Set @where = @where + N' And FirstName Like @FirstName + N''%''';
        End;

    Set @sql_cmd = @select + @where;
     
    Exec sys.sp_executesql @sql_cmd, @parameters, @LastName = @LastName, @FirstName = @FirstName;
    
    Return;         
END

Now when you run our examples, you’ll see that it runs successfully under your original security context, but you receive an error when you run it as the limited rights user:

Msg 229, Level 14, State 5, Line 1




The SELECT permission was denied on the object 'Contact', database 'AdventureWorks', schema 'Person'.

This is because the execution context changed and ownership chaining no longer applies.  To get the Dynamic SQL Stored procedure to work add WITH EXECUTE AS OWNER after the parameter definition like this:

ALTER PROCEDURE dbo.FindPhoneByName
    (
    @LastName nvarchar(50) = null,
    @FirstName nvarchar(50) = null
    )
With Execute As owner
AS

Then you can re-run the your stored procedure calls and they should work both for the dbo user and the limited rights user because the EXECUTE AS OWNER has enabled access to the tables.

Perfomance

I ran all of my examples with SET STATISTICS IO ON so I could see the results.  Here are those results (also part of the download):

Parameters	Non-Dynamic SQL		Dynamic SQL
	Scans	Reads	Scans	Reads
@FirstName = 'J', @LastName = 'A'	1	593	1	593
@FirstName = 'J'	1	7792	1	1116
@LastName='A'	1	3039	1	1116

Notice the reduced number of reads required by the Dynamic SQL when only 1 parameter is supplied.  This is because it is using a different query plan, while the Non-Dynamic procedure has one query plan which is not optimal when only one parameter is supplied

Conclusions

As you can see some of the limitations of Dynamic SQL have been “cured” by the advent of the EXECUTE AS clause.  This has made it simpler to use Dynamic SQL and get the performance benefits provided by getting a proper execution plan and getting plan re-use.  Again all code is available here.

Next I’ll be discussing using a Certificate to sign a stored procedure.

Part Ib, Part II

Windows 7, UAC, and SQL Server

This is just a quick note, almost a continuation of my Access Denied, Not Possible post.  I have been working on some queries for a Default Trace presentation that I am preparing for the Space Coast User Group and SQLSaturday #21 – Orlando, and one of the queries has to do with trying to find logins that have gained access through a Windows Group.  Since I am working on my laptop (no domain), I decided to add the Builtin\Administrators group, delete my explicit login, and get access via the group.  Interestingly enough, in order to get access to SQL Server via Builtin\Administrators you need to run SSMS as Administrator.  Here’s the error I get when not running SSMS as administrator:

When I did run SSMS as administrator, I was able to successfully login to my local SQL Server.

No, I do not leave Builtin\Adminstrators as sysadmin on my servers and with SQL Server 2008, I do not have it at all.

What’s ownership chaining?

Edit: 04-27-2009 K. Brian Kelley corrrectly pointed out in a comment that objects can still be owned by users in 2005 and later.  See his blog post to see how.

Edit:  While the code section is not as nice as I would like it is better.  Thanks for sticking with me.

I recently answered a question on SSC about how you granting execute rights on a stored procedure allows you to access tables without having any permissions granted on the tables.  The way that SQL Server implements this it through ownership chaining.  Here is the BOL (SQL Server 2005) definition of Ownership Chains:

When multiple database objects access each other sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server 2005 traverses the links in a chain, SQL Server evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately. These differences have important implications for managing security.
Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view. Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.

How Permissions Are Checked in a Chain

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Prior to SQL Server 2005, objects were owned by users, and, in most cases, designers and developers used the dbo user to own common objects.  In my experience it was very rare for objects to be owned by a “real” user. Thus if the customers table and orders table are both owned by dbo, then you can create a customer_orders_get stored procedure owned by dbo, grant user Joe execute permissions on the stored procedure, and Joe would be able to successfully use the stored procedure to query the customers and orders tables without having select permissions on the tables.  This is pretty easy to understand, as long all the objects are dbo.object_name I can access them using ownership chaining.
SQL Server 2005 introduced schemas which removed the use of users as owners.  Now objects belong to schemas, not users.  From BOL:

Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. You can think of a schema as a container of objects. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

The question now is, how does ownership chaining work when using schemas?  As the last line of the BOL quote states, a schema is owned as well, and ownership chaining in SQL Server 2005 and greater is based on schema ownership.  So if schema A is owned by dbo then you would be able to use ownership chaining to access objects in dbo from schema A and vice versa.  I think the best way to explain is with an example:

USE [master]
GO

-- drop the DB if it exists
IF EXISTS ( SELECT
name
FROM
sys.databases
WHERE
name = N'oc_test' ) 
DROP DATABASE [oc_test]
GO

USE [master]
GO

-- create the test database
CREATE DATABASE [oc_test] ON PRIMARY 
( 
NAME = N'oc_test', 
FILENAME = N'C:\Data\oc_test.mdf' , 
SIZE = 194560KB, 
MAXSIZE = 1024000KB, 
FILEGROWTH = 10240KB 
)
LOG ON 
( 
NAME = N'oc_test_log', 
FILENAME = N'C:\Logs\oc_test_log.ldf', 
SIZE = 204800KB, 
MAXSIZE = 204800KB, 
FILEGROWTH = 10240KB 
)
GO

-- switch to the new databse
USE oc_test ; 

GO

-- drop the user if it exists
IF EXISTS ( SELECT
1
FROM
sys.database_principals AS DP
WHERE
DP.[name] = 'test_user' ) 
BEGIN
DROP USER test_user ;
END

-- create our test user    
CREATE USER test_user WITHOUT LOGIN 
WITH DEFAULT_SCHEMA = dbo ;
GO

-- drop proc and schema
IF OBJECT_ID('ownership_chaining.table_one_ins') 
IS NOT NULL 
BEGIN
DROP PROCEDURE ownership_chaining.table_one_ins ;
END

GO

IF SCHEMA_ID('ownership_chaining') IS NOT NULL 
BEGIN
DROP SCHEMA ownership_chaining ;
END
GO

-- create our test schema, notice it is "owned" by dbo
CREATE SCHEMA ownership_chaining AUTHORIZATION dbo ;

GO

-- drop our test table if it exists
IF OBJECT_ID('dbo.table_one') IS NOT NULL 
BEGIN
DROP TABLE dbo.table_one ;
END

/*
create a test table NOTICE it is in the dbo 
schema which is owned by dbo
*/
CREATE TABLE dbo.table_one
(
column_one INT IDENTITY(1, 1),
column_two VARCHAR(10) NOT NULL
) ;

GO

/* 
Create the test procedure in the 
ownership_chaining schema which 
is owned by dbo
*/    
CREATE PROCEDURE ownership_chaining.table_one_ins
@column_two VARCHAR(10)
AS 
INSERT INTO
dbo.table_one (column_two)
VALUES
(@column_two) ;

RETURN ;

GO

/*
give out test user execut permissions on the 
procedure
*/
GRANT EXECUTE ON ownership_chaining.table_one_ins 
TO test_user ;

GO

-- change user context
EXECUTE AS USER = 'test_user' ;

-- execute the procedure
EXEC ownership_chaining.table_one_ins 
@column_two = 'Test 1' ;

-- go back to my login
Revert ;

-- verify that it worked which it should
SELECT
*
FROM
dbo.table_one AS _TO ;

/*
Now change the owner of the ownership_chaining 
schema so that it is no longer owned by dbo 
thus breaking the chain
*/
ALTER AUTHORIZATION ON SCHEMA::ownership_chaining 
TO db_owner ;

-- change user context
EXECUTE AS USER = 'test_user' ;

/* 
using try - catch so I get the revert as 
I expect failure
*/
BEGIN TRY 
EXEC ownership_chaining.table_one_ins 
@column_two = 'Test 1' ;
END TRY 
BEGIN CATCH
-- show the error message
SELECT
ERROR_MESSAGE() AS error ;

-- return to my user
revert ;
END CATCH 

-- verify data
SELECT
*
FROM
dbo.table_one AS _TO ;

I hope this helps clear up what I found to be a somewhat confusing topic.

Citibank Online Payment Rant

While we were in New Hampshire my wife used my AT&T Universal Mastercard (provided by Citibank) to make a purchase, but she didn't mean to use this card. Now the only reason we have this card is because we get 30 free calling card minutes a month, and we have not used it for anything but the free calling card minutes in at least 3 years.

My payment is due so I went on-line and logged into my account (remember this for later) to make my payment. Well, it turns out I don't have a bank account setup to make the payment from. Hey, no big deal, right? Just get the routing and account numbers out of the check book and away we go! NOT! After I entered the routing and account numbers I had to make it past the verification step which consists of entering the 3 digit code on the back of the card and entering the security word you chose when you setup the account. Remember I have already logged into the account and am trying to set up payment account, I'm not trying to get money, I'm trying to pay THEM! Of course I have no idea what my security word is, so I click on the "need help remembering your security word" link expecting to get a question like, "What's your mother's maiden name?". Of course this is not what happens at all, nope, just a list of "You may have chosen your mother's maiden name, the last 4 digits of your or a friend's phone number, etc...". Great! This is like Super Genius Jeapordy. Not only do you need to come up with the question, but you need to come up with the question AND the answer. Now I am ready to throw everything out the window. Why do they need to verify who I am when I am ALREADY LOGGED IN? Shouldn't they be asking this when I log in? I just want to set up an account to send them money, not take money. Hey if someone hacks into my on-line credit card account and wants to pay the balance, I'm all for it! As a matter of fact I wish they would choose to do it to the credit card I do use. Fortunately I made this attempt 5 days before the payment is due, so I may actually get it done before the payment is late.

Don't get me wrong, I'm all for keeping my credit card data secure, but that should be done by not letting me log in, not AFTER I'm logged in.

Thanks for reading, I had to get this out of my system.

Kerberos Authentication and SQL Server

Great article by K. Brian Kelley on SQLServerCentral today on Configuring Kerberos Authentication. If, like me, this was something you do not really understand this is a very good explanation that is easier to understand than most other resources I have found.

As always, it is a good idea to check out the discussion as well, as questions you may have are probably in there and answered.