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
