Digital Signing Demo

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

Read more: MSDN Blogs

Posted via email from Jasper-net