Digital Signing Demo

  • Article

This post demonstrates the use of a digital signing function to ensure data within a table is unaltered outside a given set of stored procs. To understand how these and other crytographic functions 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 within which sensitive data will be housed:

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

CREATE DATABASE SigningFunctionDemo;
GO

Next, a table will be created to house some sensitive data.  It's important to note that for this demonstration, the data is not being encrypted but it could be to make unauthorized access and modifications more challenging:

USE SigningFunctionDemo;
GO
 
CREATE TABLE dbo.MySensitiveData (
   Id INT NOT NULL IDENTITY(1,1),
   MyData NVARCHAR(25) NOT NULL,
   MySignature VARBINARY(256) NOT NULL
   );
GO

To support signing, an asymmetric key will be created along with two stored procedures making the signing and verification calls on behalf of the application:

CREATE ASYMMETRIC KEY MySigningKey
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'asd!i36oheQ#wr8iW#%qwei4!orqhq9w7as';
GO
 
CREATE PROC dbo.spPutData @MyData NVARCHAR(25)
AS
   INSERT INTO dbo.MySensitiveData (MyData, MySignature)
   SELECT
    @MyData,
    SIGNBYASYMKEY(
         ASYMKEY_ID('MySigningKey'),
         @MyData,
         N'asd!i36oheQ#wr8iW#%qwei4!orqhq9w7as'
         );
GO
 
CREATE PROC dbo.spGetData @Id int
AS
 SELECT
    MyData,
    VERIFYSIGNEDBYASYMKEY(
  ASYMKEY_ID('MySigningKey'),
  MyData,
  MySignature
  ) AS IsValid
 FROM dbo.MySensitiveData
 WHERE Id = @Id;
GO

With this in place, data can now be placed into the database along with a signature:

EXEC dbo.spPutData N'This is my sensitive data'
GO

Accessing the table directly, the data and its signature can be seen:

SELECT * FROM dbo.MySensitiveData
GO

Id MyData MySignature
----------- ------------------------- ----------------
1 This is my data 0x91A7FCB73CD...

(1 row(s) affected)

Executing the intended stored procedure to retrieve the data, we can see it is confirmed as valid:

EXEC dbo.spGetData 1
GO

MyData IsValid
------------------------- -----------
This is my data 1

(1 row(s) affected)

Now, suppose an authorized user alters the data:

UPDATE MySensitiveData
SET MyData = 'This is my data'
WHERE ID = 1;

When the data retrieval stored procedure is executed, we can see the data is not valid:

EXEC dbo.spGetData 1
GO

MyData IsValid
------------------------- -----------
This is my data 0

(1 row(s) affected)

To clean up the demonstration enviroment, execute the following script:

USE master;
GO
DROP DATABASE SigningFunctionDemo;
GO