Module Signing (Database Engine)
Database applications often require that access to underlying tables and objects within the application schema be mediated through entry level procedures or views. The intent is to be able to grant access to end users on the entry level objects, which then access the underlying objects on behalf of the user. End users thus do not have to be granted access on all the objects in the application schema. This approach serves two purposes:
It simplifies permissions management in that permissions need be managed only on a smaller subset of objects, as apposed to all objects in the application schema.
It is possible to hide the underlying schema layout from the end user, since only the entry points are exposed.
Microsoft SQL Server has a number of features that help achieve these scenarios, such as ownership chaining and using the EXECUTE AS statement. Beginning with SQL Server 2005, SQL Server also provides the capability to sign modules within the database. Module signing offers similar capabilities, but does not change the execution context. A module in this context refers to a stored procedure, function, trigger or assembly. For more information, see CREATE ROLE (Transact-SQL) and Using EXECUTE AS to Create Custom Permission Sets.
Module Signatures
SQL Server 2005 introduced the capability to sign modules within the database, such as stored procedures, functions, triggers, or assemblies. Note that data definition language (DDL) triggers cannot be signed. A digital signature is a data digest encrypted with the private key of the signer. The private key ensures that the digital signature is unique to its bearer or owner.
To sign data, the signer digests the data, encrypts it with a private key, and attaches the encrypted digest value to the data. To verify the signature, the verifier uses the signer's public key to decrypt the encrypted digest value attached to the data. The verifier then compares this decrypted digest value with the digest value computed on the companion data. It is important that both the signer and the verifier use the same hash function to digest the data.
Warning
Module signing should only be used to grant permissions, never to deny or revoke permissions.
Scenario
Let us assume that access to the sys.sysprocesses view should be mediated by the usp_sysprocesses stored procedure. Users can access the sys.sysprocesses information only when going through the usp_sysprocesses procedure. Since the usp_sysprocesses and sys.sysprocesses objects have different ownerships, ownership chaining does not apply.
First, a certificate must be created on the server from a key pair using the CREATE CERTIFICATE statement. We then grant the certificate the permissions to select from the sys.sysprocesses table. However, because SQL Server only grants permissions to principals, we would first need to create a login from the certificate using the CREATE LOGIN statement. This login does not need connect permissions on the server, as it is only a permissions placeholder and is not intended for connecting to the server instance. This certificate mapped login can then be granted SELECT permissions on the sys.sysprocesses table using the GRANT VIEW SERVER STATE TO statement. After the usp_sysprocesses stored procedure is created, we can then sign the stored procedure with the certificate (actually the private key corresponding to this certificate) using the ADD SIGNATURE statement. A new role is created and granted execute permission on the usp_sysprocesses stored procedure. Any users who are a member of this role then have permission to execute the usp_sysprocesses stored procedure, and thereby SELECT from the sys.sysprocess view. When you execute a signed module, the permissions granted to the principal (using the GRANT statement) associated with the signing certificate are temporarily UNION-ed into your run-time security token for the duration of the call. As soon as execution control returns, those permissions are removed from your security token. Thus, just for the lifetime of the module execution, you effectively have an extra set of permissions. Any other user or role that is granted EXECUTE permissions on this procedure will also have the same capabilities.
Example
The following Transact-SQL script provides an example of the above scenario. A certificate is created from a key pair and mapped to a new login. A test key pair must be created first using the MakeCert tool that is included with the .NET Framework SDK. Select permissions to the sys.sysproceses view are then granted to the login associated with the certificate. The usp_sysprocesses managed stored procedure is created in the new database and signed with the certificate. The SysProcRole role is created and that role is granted execute permissions on the usp_sysprocesses stored procedure. A test user is created and added to the SysProcRole role. The test user performs a SELECT statement on sys.sysprocess and then executes the usp_sysprocesses stored procedure, for comparison. The script then cleans up the test environment.
use master
go
-- Create a test database.
CREATE DATABASE db_Demo
go
-- Create a certificate on the server. A test key pair can be created
-- using the MakeCert tool that ships with the .NET Framework SDK.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
go
-- Create a login and map it to the certificate.
CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCert
Go
-- Revoke the connect permission.
REVOKE CONNECT SQL FROM login_SysProcCert ;
go
-- Grant the certificate, through the login, permission to select from sys.sysprocesses view.
GRANT VIEW SERVER STATE TO login_SysProcCert
go
-- Create a test login.
CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'
go
-- Connect to the test database.
use db_Demo
go
-- Create the master key for the test database (used to protect
-- private keys and certificates in the database).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>'
-- Create a certificate from a private key.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
WITH PRIVATE KEY
(FILE = 'e:\programming\testCert.pvk',
DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>',
ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')
go
-- Create the assembly on the server. The assembly DLL must be signed.
CREATE ASSEMBLY SysStoredProcedures
FROM 'E:\programming\SysStoredProcs.dll'
WITH PERMISSION_SET = SAFE
go
-- Create the managed stored procedure on the server.
CREATE PROCEDURE usp_sysprocesses
AS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocesses
go
-- Add the signature to the stored procedure.
ADD SIGNATURE TO [dbo].[usp_sysprocesses]
BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'
go
-- Create a role.
CREATE ROLE SysProcRole
go
-- Create a test user
CREATE USER bob
go
-- Add the test user to the role.
EXEC sp_addrolemember 'SysProcRole', 'bob'
go
-- Grant execute permissions on the stored procedure to the new role.
GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole
go
-- Connect as the test user.
EXECUTE AS LOGIN = 'bob'
use db_Demo
go
-- User only has permission to see their own processes.
SELECT * FROM sys.sysprocesses
go
-- Execute the stored procedure, which has been signed.
exec usp_sysprocesses
go
-- REVERT
REVERT
----------------------------------------------------------------------
-- Cleanup
use db_Demo
go
use master
go
DROP DATABASE db_Demo
go
DROP login login_SysProcCert
DROP login bob
go
DROP CERTIFICATE SysProcCert
go
The following is the source code for the usp_sysprocesses stored procedure, which performs a SELECT * statement on the sys.sysprocesses view. The assembly must be signed when it is built.
C#
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_sysprocesses()
{
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM sys.sysprocesses", connection);
SqlContext.Pipe.ExecuteAndSend(command);
}
}
};
Visual Basic
Imports System
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub usp_sysprocesses ()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub
End Class