473,326 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Trigger to encrypt field before write

Hello,
While working through my encryption questions from preivous posts, I am
finding that I may have to resort to use triggers to do the encryption
(not that this is the only way, but might be the best way for my
circumstances).
I would like to create a trigger that will encrypt the field before the
write is committed.
I've found serveral posts about triggers, but nothing that fits what I
wish to do.
Upon an insert or update I want to modify the ssn field with this:

cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN as
nvarchar(11))) as nvarchar(40))

so, ssn '123456789' in SSN would become <something encrypted> in SSN

This is the trigger I have so far, but it is generating an error:

CREATE TRIGGER F_Student_SSN.eSSN
ON F_STUDENT_SSN
INSERT, UPDATE
AS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN as
nvarchar(11))) as nvarchar(40))

TIA
Rob

Apr 28 '06 #1
3 7655
more research I have:
CREATE TRIGGER F_Student_SSN.eSSN
ON ds_v6_staging.F_STUDENT_SSN
update, insert
AS
update f_student_ssn
set essn = cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
cast(eSSN as nvarchar(11))) as nvarchar(40))

But still get error

Apr 28 '06 #2
rcamarda (ro*****@hotmail.com) writes:
more research I have:
CREATE TRIGGER F_Student_SSN.eSSN
ON ds_v6_staging.F_STUDENT_SSN
update, insert
AS
update f_student_ssn
set essn = cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
cast(eSSN as nvarchar(11))) as nvarchar(40))

But still get error


I realise that you are working with sensitive data, and may want to
disclose much. However, with knowing what error you get, it's difficult
to assist.

Of course the trigger as such is not a good one, since you are updating
the entire table, you will encrypt already encrypted data on each
INSERT or UPDATE.

You need to add:

update f_student_ssn
set essn = cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'),
cast(eSSN as nvarchar(11))) as nvarchar(40))
from f_student_ssn f
join inserted i ON f.pkcol = i.pkcol

The table "inserted" holds an after-image of the inserted/updated rows.
Note the plural: a trigger fires one per statement.

But the above only makes sense for an INSERT trigger. For an UPDATE
it's tricker. You could add:

IF UPDATE(eSSN)
BEGIN
UPDATE ...
END

But if someone for some reason says:

UPDATE tbl SET eSSN = eSSN

you will end up encrypting the encrypted value.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 28 '06 #3
OH...thanks for the Help Erland!
You bring up some very valid points. I may luck out because my plan was
to truncate the table after I am done. Once the data is encrypted, I
can then processed it 'normally', so I wont have to worry about
encypting encrypted data. I am tying to minimize the exposure of clear
text data that I wish to encrypt.
I tried to load the text and encypted using Integration Services, but
could spit that atom yet. (I got data to load, but was stumped when I
needed to add the encrypted functions)
Rob

Apr 28 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: wqhdebian | last post by:
As far as I know,when encrypt or decrypt ,a key must first be got,and the key is first generate by a tool or from SecurityRandom,that means I can not generate the same key with the same input.Does...
0
by: Alan Murrell | last post by:
Hello, I am setting up a Postfix + MySQL + Courier-IMAP system. I am trying to write a shell script which will insert the values into the database. For the password encryption, I wish to use...
2
by: Trevor Fairchild | last post by:
I am trying to create a very minimal auditing system for a series of databases. I am in the process of writing Update triggers for 5 Tablse. I will write a trigger for each table-the trigger's...
9
by: Lauren Quantrell | last post by:
Is there a way to create a text file (such as a Windows Notepad file) by using a trigger on a table? What I want to do is to send a row of information to a table where the table: tblFileData has...
10
by: Javier Gomez | last post by:
I have a table with 15.000 records. How can encrypt all information if after will shown in a form (text box)decryted ????? Thanks in advance Javier Gomez
2
by: Chris Gamache | last post by:
Tsearch2 comes with its own tsearch2 trigger function. You pass column names to it, and it puts a vanilla tsvector into the column named in TG_ARGV. Not only can you pass column names to it, but...
3
by: Justin Clift | last post by:
Hi all, I'm creating a centralised table to keep a log of changes in other tables. In thinking about the PL/pgSQL trigger to write and attach to the monitored tables (probably a row level...
9
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger,...
6
by: Oliver | last post by:
I'm fairly new to DB2. I have been assigned to build a delete trigger that finds the data type of each of the table's fields so that the trigger can then build a string consisting of OLD values...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.