SQL Server – Encryption and Updating Related Tables Via Trigger

Background

We have a pretty straight forward Trigger that updates corresponding tables via a Trigger.

The original query is straightforward, but as we implement encryption, we find that our code was breaking.

Code

Original Code


ALTER TRIGGER [dbo].[tr_pended_status_changed]
ON [dbo].[Delivery]
FOR UPDATE
AS

	declare @delID uniqueidentifier

	declare @pended char(1)
	declare @studentID varchar(100)
	declare @name1 varchar(255)
	declare @name2 varchar(255)
	declare @address1 varchar(255)
	declare @address2 varchar(255)
	declare @address3 varchar(255)
	declare @city varchar(100)
	declare @state varchar(25)
	declare @postcode varchar(40)
	declare @country varchar(100)
	declare @areaCD varchar(10)
	declare @phoneNbr varchar(30)

	if update(pended)
	begin

		select
			@delID 		= inserted.deliveryID,
			@pended	= inserted.pended,
			@studentID 	= inserted.receiverID,
			@address1 	= isnull(rtrim(ltrim(inserted.address1)),''),
			@city    		= isnull(rtrim(ltrim(inserted.city)),''),
			@postcode 	= isnull(rtrim(ltrim(inserted.postcode)),'')
		from inserted

		if (@pended='N' and (@address1='' or @city = ''  or @postcode=''))
		begin

			select
					@address1 	= address1,
					@address2 	= address2,
					@address3 	= address3,
					@city    		= city,
					@postcode 	= postcode,
					@state    	= state,
					@name1 	= first_name+' '+last_name,
					@country	= country,
					@areaCD	= area_code,
					@phoneNbr	= phone_nbr

			from Student

			where studentID = @studentID 

			begin TRANSACTION

				update Delivery
				set
						address1   		= @address1,
						address2   		= @address2,
						address3   		= @address3,
						city       		= @city,
						postcode	    = @postcode,
						state   		= @state,
						name_on_label1 	= @name1,
						country			= @country,
						area_cd 		= @areaCD,
						phone_nbr		= @phoneNbr

				where deliveryID = @delID

				if (@@error<>0)
					ROLLBACK TRANSACTION

				COMMIT TRANSACTION

		end -- if (@pended='N' and (@address1='' or @city = ''  or @postcode=''))

	end -- if update(pended) 

go

 

Revised Code

sys.symmetric_keys

Intent

In the revised code, we will attempt to cover the following goals:

  1. Access the inserted virtual table
    • Read Source data using decryptByKey to decrypt the encypted columns
  2. Save data
    • Apply decrypted-data to Destination table
      • Invoke EncryptByKey pass in Symmetric Key GUID, decrypted data, 1, Salt
      • Because we need the Symmetric Key GUID, we need to either hardcode the GUID or access the sys.symmetric_keys to read it based on our Key Name

 

Code

 

Code – Snippet

  /*
     Get KeyGUID
  */
  set @keyName = 'SMKEY'
 
  select @keyGUID = tblSK.[key_guid]
  from   sys.symmetric_keys tblSK
  where  tblSK.[name] = @keyName

 

Code – Whole

 


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_pended_status_changed]
ON [dbo].[Delivery]
FOR UPDATE
AS
begin

	set nocount on;

	declare @keyName varchar(60)
	declare @keyGUID uniqueidentifier

	if update([pended])
	begin

		/*
			Get KeyGUID
		*/
		set @keyName = 'SMKEY'

		select @keyGUID = tblSK.[key_guid]
		from   sys.symmetric_keys tblSK
		where  tblSK.[name] = @keyName

		if (@keyGUID is null)
		begin

			raiserror('KeyGUID is null', 16,1)

		end	

		/*
			Get Student's Address Information, and update dbo.Delivery Table
				Filtering:
					a) Pended Column Toggled
					b) And
						0] Pended = 'N'
						i] Address1 is null or Address1 is empty
						ii] City is empty
						iii] Post Code is empty
		*/
		update tblD
		set
			   address1   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address1, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
								)
			 , address2   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address2, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
					) 

		   	, city       = tblS.City

		        , postcode   = tblS.PostCode

		        , [state]    = tblS.[State]

			 , [country]   = tblS.country

		from [dbo].[Delivery] tblD

		inner join inserted tblIns

			on tblD.[deliveryID] = tblIns.[deliveryID]

		inner join [dbo].[Student] tblS

			on tblD.[ReceiverID] = tblS.[studentID]

		cross apply dbo.itvf_Salt(tblS.[studentID]) itvfS

		where ( tblIns.[pended] ='N' )

		and   (

				   (
						   (tblIns.[Address1] is null )
						or (tblIns.[Address1] = '' )
				   )
				or ( tblIns.city = '' )
			    or ( tblIns.postcode='')

		     )

	end -- if update(pended) 

end
go			

Error

Here is an error we started getting


SQLERR: The SELECT permission was denied on the object 'symmetric_keys', database 'mssqlsystemresource', schema 'sys'. PROC: SaveDeliveryRecord 

 

Execute As

Intent

  1. If you run into this problem at 1 AM, I will say to modify the Stored Procedure definition and add “with execute as owner
    • The running user, which is far less privilege, does not have explicit access to read from the sys.symmetric_keys view
    • Adding “Execute as owner” allows us to override the current user’s security cloak and instead rely on the object’s owner ( sa )

Code


ALTER PROCEDURE [Products].[SaveDeliveryRecord]
(
	@deliveryID uniqueidentifier,
	@receiverID varchar(100),
	@refID varchar(100),
	@packageCD varchar(15),
	@deliveryCode varchar(25),
	@deliveryTo varchar(15),
	@bPended char(1),
	@bActive char(1),
	@upgrade_to varchar(300) = null,
	@name_on_label1 varchar(255),
	@name_on_label2 varchar(255),
	@Address1 varchar(255),
	@Address2 varchar(255),
	@Address3 varchar(255),
	@City varchar(100),
	@State varchar(25),
	@PostCode varchar(40),
	@country varchar(100),
	@area_cd varchar(10),
	@phone_nbr varchar(30),
	@bSignatureReq char(1),
	@tracking_nbr varchar(255) = null,
	@sentDate datetime = null,
	@keyGUID uniqueidentifier = null
)
with execute as owner
AS
BEGIN
 ....
 ....

END

sys.openkeys

Intent

  1. We intentionally denied the public account access to sys.symmetric_keys
    • When running as a privilege user we were good, but once we started running as a less privileged user, we failed with select permission
  2. A better approach is to access sys.openkeys
    • MSFT docs says
      • This catalog view returns information about encryption keys that are open in the current session

Code


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_pended_status_changed]
ON [dbo].[Delivery]
FOR UPDATE
AS
begin

	set nocount on;

	declare @keyName varchar(60)
	declare @keyGUID uniqueidentifier

	if update([pended])
	begin

		/*
			Get KeyGUID
		*/
		set @keyName = 'SMKEY'

                /*
		select @keyGUID = tblSK.[key_guid]
		from   sys.symmetric_keys tblSK
		where  tblSK.[name] = @keyName
                */

		select @keyGUID = tblOK.[key_guid]
		from   sys.openkeys tblOK
		where  tblOK.[key_name] = @keyName


                /*
                   Security blanket, if Symmetric Key is not opened
                */
		if (@keyGUID is null)
		begin

		   select @keyGUID = tblSK.[key_guid]
		   from   sys.symmetric_keys tblSK
		   where  tblSK.[name] = @keyName

                end

		if (@keyGUID is null)
		begin

			raiserror('KeyGUID is null', 16,1)

		end	

		/*
			Get Student's Address Information, and update dbo.Delivery Table
				Filtering:
					a) Pended Column Toggled
					b) And
						0] Pended = 'N'
						i] Address1 is null or Address1 is empty
						ii] City is empty
						iii] Post Code is empty
		*/
		update tblD
		set
			   address1   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address1, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
								)
			 , address2   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address2, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
					) 

		   	, city       = tblS.City

		        , postcode   = tblS.PostCode

		        , [state]    = tblS.[State]

			 , [country]   = tblS.country

		from [dbo].[Delivery] tblD

		inner join inserted tblIns

			on tblD.[deliveryID] = tblIns.[deliveryID]

		inner join [dbo].[Student] tblS

			on tblD.[ReceiverID] = tblS.[studentID]

		cross apply dbo.itvf_Salt(tblS.[studentID]) itvfS

		where ( tblIns.[pended] ='N' )

		and   (

				   (
						   (tblIns.[Address1] is null )
						or (tblIns.[Address1] = '' )
				   )
				or ( tblIns.city = '' )
			    or ( tblIns.postcode='')

		     )

	end -- if update(pended) 

end
go			

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s