July 23, 2015 at 2:13 pm
Can I accomplish encrypting a column with a trigger similar to below, but without the 'SSN' column?
I added the SSN column just so I could pass an UNencrypted value. Seems round-about & clunky.
This works:
Insert into TestSSNs(UserID, SSN, Name) VALUES (NEWID(), '890-12-0007', 'Gina')
Thanks in advance
Tom
---------------------------------------------
CREATE TABLE TestSSNs (
UserIDuniqueidentifier,
SSNVarchar(11) , --plain text, should always be null
eSSN VarBinary(128), --encrypted value
[Name]Varchar(50)
);
CREATE TRIGGER dbo.IU_TestSSNs ON dbo.TestSSNs
INSTEAD OF INSERT, UPDATE
AS
BEGIN
OPEN SYMMETRIC KEY SSN_SymmKey
DECRYPTION BY CERTIFICATE CertSSNEncrypt
IF (SELECT COUNT(*) FROM TestSSNs t inner join inserted i on t.UserID = i.UserID) = 0
Insert TestSSNs (Userid, ssn, eSSN, Name )
SELECT i.Userid, null, --i.SSN,
EncryptByKey(key_guid('SSN_SymmKey'), i.SSN), i.Name FROM inserted i
ELSE
UPDATE TestSSNs
SET
TestSSNs.SSN = Null,
TestSSNs.eSSN =
CASE
WHEN LEN(i.SSN) > 0 THEN EncryptByKey(key_guid('SSN_SymmKey'), i.SSN) ELSE t.eSSN --encrypt new or keep old
END,
TestSSNs.[Name] =
CASE
WHEN LEN(i.Name) > 0 THEN i.Name ELSE t.Name
END
FROM TestSSNs t INNER JOIN inserted i ON t.UserID = i.UserID
CLOSE SYMMETRIC KEY SSN_SymmKey
END
July 23, 2015 at 2:36 pm
Looks ok. But you don't need to do a full count of rows to determine INSERT vs UPDATE:
CREATE TRIGGER dbo.IU_TestSSNs ON dbo.TestSSNs
INSTEAD OF INSERT, UPDATE
AS
SET NOCOUNT ON;
OPEN SYMMETRIC KEY SSN_SymmKey
DECRYPTION BY CERTIFICATE CertSSNEncrypt
IF NOT EXISTS(SELECT TOP (1) * FROM deleted)
Insert TestSSNs ( Userid, ssn, eSSN, Name )
SELECT i.Userid, null, --i.SSN,
EncryptByKey(key_guid('SSN_SymmKey'), i.SSN), i.Name
FROM inserted i
ELSE
UPDATE TestSSNs
SET
TestSSNs.SSN = Null,
TestSSNs.eSSN =
CASE
WHEN LEN(i.SSN) > 0 THEN EncryptByKey(key_guid('SSN_SymmKey'), i.SSN) ELSE t.eSSN --encrypt new or keep old
END,
TestSSNs.[Name] =
CASE
WHEN LEN(i.Name) > 0 THEN i.Name ELSE t.Name
END
FROM TestSSNs t
INNER JOIN inserted i ON t.UserID = i.UserID
CLOSE SYMMETRIC KEY SSN_SymmKey;
GO --end of trigger
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 24, 2015 at 9:15 am
I appreciate you taking the time to look through my code and comment.
With all due respect, I disagree. It does not look good to have a column 'SSN' that is and must be null in 100% of the rows. I'm asking if I can write the trigger without that column. The application will execute a stored proc that will 'Insert into' the table. That insert statement would include the UNencrypted SSN in character format. The encrypted column eSSN is varbinary.
Thanks again
Tom
July 24, 2015 at 10:32 am
I suppose you could. I think inevitably you will damage some data eventually by trying to have the same column try to contain plain data and encrypted data. When I have to encrypt column data, I use the "temporary" column method also.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply