Asymmetric Encryption Trigger to Automatically Encrypt

Question

Asymmetric Encryption Trigger to Automatically Encrypt

Ghanta

Hall of Fame

Points: 3999
More actions
July 26, 2011 at 8:05 am

#244982

Hi Guys,
I am planning to use column (cell) level encryption. I wanted to check onething however, can we create a trigger in the column that would automatically encrypt the data comming in? Say if a column has personal information like SSN, I will implement encryption, but I want all data inserted into that column to be encrypted as it is inserted (user inserting the data would not have keys or know if that field is encrypted)... Hope I am making sense..
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

typically i've used a stored procedure to do the whole CRUD operations for tables which contain encrypted values...or at least when the encryption is handled at the SQL server level.

then by granting EXECUTE to the stored procedures, the end users do not have or need access to the underlying tables, or any details related to the encryption process itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ghanta Hall of Fame Points: 3999 More actions · Answer 2

Thanks a lot for your response. So the user can do insert like this:

Insert into TableName(ID, SSN)

Values('1234', '999-111-2323') without even knowing SSN is encrypted and the Public Key using your Proc?

Ghanta Hall of Fame Points: 3999 More actions · Answer 3

I think this trigger does what I am looking for-

create trigger IT1 on Customers

instead of insert

as

begin

insert into encr_Customers (name,SSN)

select name,

EncryptByAsymKey(AsymKey_ID('AsymKey'),SSN)

from inserted

end

GO

http://www.sqlsolutions.com/articles/articles/Encrypted_Columns_and_SQL_Server_Performance.htm

Lowell SSC Guru Points: 323518 More actions · Answer 4

yes exactly...at the data entry level, they would actually type the value in...but the data would be processed by the stored proc, and stored in an encrypted fashion.

here's a really stupid, simple example that i tweaked from a CRUD generator.

CREATE TABLE [dbo].[Example] (

[id] int IDENTITY(1,1) NOT NULL,

[SSN] varbinary(max) NULL,

[name] varchar(100) NULL,

CONSTRAINT [PK__Example__3213E83F1B813676] PRIMARY KEY CLUSTERED (id))

GO

CREATE PROC [dbo].[pr_ExampleInsert]

@SSN varchar(20),

@name varchar(100)

AS

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRAN

INSERT INTO [dbo].[Example] ([SSN], [name])

SELECT EncryptByPassPhrase('NotMyRealPassPhrase', @SSN ), @name

-- Begin Return Select <- do not remove

SELECT [id], convert(varchar(100),DecryptByPassPhrase('NotMyRealPassPhrase',[SSN])), [name]

FROM [dbo].[Example]

WHERE [id] = SCOPE_IDENTITY()

-- End Return Select <- do not remove

COMMIT

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ghanta Hall of Fame Points: 3999 More actions · Answer 5

Ghanta

Hall of Fame

Points: 3999

July 26, 2011 at 9:37 am

#1358618

Thanks a lot Lowell.