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.
Finally all the MY code from this post can be downloaded from here.
