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
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
