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:
- Access the inserted virtual table
- Read Source data using decryptByKey to decrypt the encypted columns
- 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
- Apply decrypted-data to Destination table
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
- 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
- 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
- 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
- MSFT docs says
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