Module Signing Demo

This post demonstrates the use of module signing to provide controlled access to objects to which a user otherwise does not have permissions. To understand how module signing can be employed to improve the security of database applications, please review this post

The first step in the demonstration is to create an empty database.  In this database, a few objects and user accounts will later be created:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ModuleSigningDemo')
   DROP DATABASE ModuleSigningDemo;
GO

CREATE DATABASE ModuleSigningDemo;
GO

The next step is to create a few user accounts within the database.  To keep things simple, these will be created without an associated login:

USE ModuleSigningDemo;
GO

CREATE USER UserX WITHOUT LOGIN;
GO

CREATE USER UserY WITHOUT LOGIN;
GO

Next, a table named TableX is created within SchemaX which is owned by UserX.  The table is populated with data to make the results of a simple SELECT statement against the table easier to see:

CREATE SCHEMA SchemaX AUTHORIZATION UserX;
GO

CREATE TABLE SchemaX.TableX (Column1 int);
GO

INSERT INTO SchemaX.TableX VALUES (1),(2),(3);
GO

Next, a stored procedure named ProcY is created within SchemaY which is owned by UserY.  The stored procedure executes a simple SELECT statement against TableX:

CREATE SCHEMA SchemaY AUTHORIZATION UserY;
GO

CREATE PROC SchemaY.ProcY
AS
   SELECT * FROM SchemaX.TableX;
GO

Because ProcY and TableX have different owners, there is no ownership chain between the two.  As a result, when UserY executes stored procedure ProcY, an error message is returned:

EXECUTE AS USER='UserY';
GO

EXEC SchemaY.ProcY;
GO

Msg 229, Level 14, State 5, Procedure ProcY, Line 3
The SELECT permission was denied on the object 'TableX', database 'ModuleSigningDemo', schema 'SchemaX'.

REVERT;
GO

By creating a certificate and a user from that certificate and then assigning permissions to that user, we’ve created the foundation for module signing:

CREATE CERTIFICATE MyModuleSigningCert 
ENCRYPTION BY PASSWORD='MyVeryStr0ngPassw0rd' 
WITH SUBJECT='My Module Signing Certificate';
GO

CREATE USER ModuleSigningUser FROM CERTIFICATE MyModuleSigningCert;
GO

GRANT SELECT ON OBJECT::SchemaX.TableX TO ModuleSigningUser;
GO

The final step is to sign the stored procedure with the certificate.  In doing so, the permissions of the user associated with the certificate are assigned to the stored procedure:

ADD SIGNATURE TO SchemaY.ProcY
BY CERTIFICATE MyModuleSigningCert
WITH PASSWORD='MyVeryStr0ngPassw0rd';
GO

Now when UserY executes ProcY, the stored procedure executes successfully:

EXECUTE AS USER='UserY';
GO

EXEC SchemaY.ProcY;
GO

REVERT;
GO

The following group of statements cleans up the demonstration environment:

USE master;
GO

DROP DATABASE ModuleSigningDemo;
GO