Trigger To Encrypt Field Before Write
Apr 28, 2006
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
View 3 Replies
ADVERTISEMENT
Feb 4, 2006
How to decrypt or encrypt without making user a db_owner. It is for a web
application and I do not want make the web user a db_owner. Is there a
way to make this work without making the user a db_owner. Currently the
user is a db_datareader and db_datawriter.
I created an asymmetric key
for encryption by password. I am not using a master key because I want
to keep the password seperately on the web server, so a hacker cannot
get access to both if database gets hacked.
These are the steps I took when I logged in to SQL server management studio using windows authentication:
CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password';
INSERT INTO Payments (CreditCardNumber,enc_CreditCardNumber)
values( '458724124',
EncryptByAsymKey(AsymKey_ID('ccnumber'), '458724124') )
SELECT CONVERT(varchar(50), DecryptByAsymKey( AsymKey_Id('ccnumber'), enc_CreditCardNumber, N'password' ))
AS Creditcardnumber , Creditcardnumber
FROM payments where Creditcardnumber = '458724124'
When
I use the above select statement it works if I make the user a db_owner
but I get null if the user is just db_reader and db_writer.
Is there a way to do encryption without making the user a db_owner?
View 1 Replies
View Related
May 25, 2006
without writing code in my application? Does SQL Server have stored procedure to do it?
Any help is appreciated.
Thanks.
View 14 Replies
View Related
May 25, 2006
without writing code in my application? Does SQL Server have stored procedure to do it?
Any help is appreciated.
Thanks.
View 7 Replies
View Related
Jul 23, 2015
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')
---------------------------------------------
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
[code]....
View 3 Replies
View Related
Oct 20, 2015
We have to encrypt one of the SQL Server column data at the database level, we don’t want to modify the application programs, because we use ERP application and we don’t want to reapply our customization very few months, so we are looking for an option  to encrypt and decrypt the column level data value using trigger/procedure/function,  I used instead of trigger on that table to encrypt and decrypt the values but it is not working, we want only one user(Application Service account) have an ability to encrypt and decrypt the data, not for any other id.. I read somewhere we cannot automatically encrypt/decrypt the column value using trigger, is that true...
View 5 Replies
View Related
May 30, 2006
hello guys! i have a question hope you'll help me..how can i encrypt the data that is stored in my password field everytime i insert value to it and decrypt it if i want to retrieve it? thanks in advance!!
View 7 Replies
View Related
Jun 21, 2007
Hi,
I have a login table with username and password as attributes. I need to encrypt the password using stored procedure and then save it in the database. And also while retrieving the password, decrypt using the same stored procedure and get the original text.
I dont know how to do it in SQL server 2000.
Please help me on this. Its urgent.
Thanks and Regards
View 4 Replies
View Related
Oct 20, 2005
I understood that sql 2005 has EncryptByCert(varchar) function to encrypt data field. but varchar is limit by 8,000 chars long.
View 3 Replies
View Related
Jul 3, 2006
Hi,
I want to store user-id and passwords in a table in SQL Server. But as passwords are very secure, I want to encrypt them while storing and may be decrypt them when reqd.
How can I achieve this functionality
Thanks
-Sudhakar
View 1 Replies
View Related
Jul 3, 2006
Hi,
I want to store user-id and passwords in a table in SQL Server. But as passwords are very secure, I want to encrypt them while storing and may be decrypt them when reqd.
How can I achieve this functionality
Thanks
-Sudhakar
View 2 Replies
View Related
Apr 30, 2014
How do I reliably symmetrically encrypt a single node in an XML field in my SQL Server 2012 database? I know I cannot use SQL Server encryption to encrypt an XML field and I have been getting unpredictable results with a home grown solution.
View 9 Replies
View Related
Nov 1, 2006
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields. Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If this is a success, I need to set T1.Status=’Done’ for the updated records. So this should only be updated records. T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=’Done’, any example would be greatly appreciated.
View 9 Replies
View Related
Dec 20, 2005
I have a table with a last_updated field and a review_date field. I would like to write a trigger that when the last_updated field is updated, it adds 6 months to the review_date field.
This is what I started, not sure of how to finish it:
CREATE TRIGGER Update_Review_Date
ON dbo.Category_Type
FOR Update
AS
View 6 Replies
View Related
Feb 22, 2005
Hi,
I am pretty new to MSSQL server environment. In Oracle i write Trigger and compile it at command prompt in case of SQL plus the same way i need to know where we write the script for SQL server. Is there any menu driven editor where we can write or simply writing a trigger in SQL Query analyzer and executing it will work
Please advice.
Thanks and Regards
D. Pavan Kumar
View 4 Replies
View Related
Mar 28, 2007
hi
I want to audit data before update or delete with modified date,userid,machineid
How to write trigger for it
Example
-------
I have a table as Immigration With Following fields
Employee_id int(foriegn key)
Passport no varchar(50)
Issue date datetime
Expiry date datetime
Iwant to store data or audit data before update or delete data from above table
Malathi Rao
View 1 Replies
View Related
Apr 29, 2008
Great.
Can you give it a first try? Post it and we will fix it.
View 3 Replies
View Related
Nov 20, 2006
Hi all,
i want to write a trigger which returns a row to the application whenever a row is inserted into a table.
E.g.
When row R1 is inserted into a table T1.
the trigger should return the R1 to the application.
Wanna be coding monkey...
View 7 Replies
View Related
Nov 1, 2006
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>€™Done€™. If this is a success, I need to set T1.Status=€™Done€™ for the updated records. So this should only be updated records.
T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=€™Done€™, any example would be greatly appreciated.
View 6 Replies
View Related
May 28, 2007
Hello All,
As we have INSERTED,DELETED tables to trace what values are inserted and deleted, how to write triggers for Updates on the tables.
Your help would be appreciated.
Shiva Kumar
View 3 Replies
View Related
Jul 23, 2005
Hello ExpertsI am new in Sql triggers and i would like some helpI have a Table called ITEM and another callded SOURCE[ITEM]ItemNo,ItemName,SourceNo[Source]SourceNo,Sourcei want to create a Trigger so when i delete a record from [SOURCE] allthe records in [ITEM] where item.SourceNo = [SourceNo that I deletedfrom SOURCE]How can i do that?
View 1 Replies
View Related
Feb 28, 2006
Hello, theres,
I have a request to write to a file whenever new record added to the table.
When records insert row by row, it goes well. but when more than 2 session insert at the same time, sometimes it duplicate some record in the file. I try to add synchonize code ( like lock , Monitor) but it doesn't work. any idea ?
Regards,
Agi
View 3 Replies
View Related
Nov 11, 2013
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
View 4 Replies
View Related
Nov 8, 2005
One of my table called as 'customertable' contains following fields
customername, emailid, subscriptionendperiod
Example records are:
david, david@john.com, 12/20/2005(mm/dd/yyyy format).
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.
Regards
View 3 Replies
View Related
Oct 12, 2014
how to i write a trigger to enforce this constraint..A rental can be made only if the customer is registered with the company and the car is not currently rented out. If not, the rental will not be successful.
View 1 Replies
View Related
Jan 31, 2005
There must be a better way to write this where clause. Can somebody help
if @id is null
begin
(bla bla bla) where parentid is null
end
else
begin
(bla bla bla) where parentid = @id
end
View 9 Replies
View Related
Mar 22, 2008
hello
my friends
i want write Select without write filed name
example
mytable(id,name1,family)
orginal selected is
select id,name1 from mytable
but i want write:
select field[0],field[1] from my table
that field[0] point to id and field[1] point to name1
thanx
M.O.H.I.N
View 3 Replies
View Related
Jan 17, 2008
Hi
Can u please send the answers for this
1 . Can write more than one trigger for a single table(sql server 2000)?
2. how to create the editable gridview? While clicking particular cell it should be changed to
Edit mode , and I want option for creating new row and delete option, search option
View 1 Replies
View Related
Dec 29, 2006
I need some help in SSIS Package I am trying to write a byte array to an image (blob) in DB2 destination. I am getting SQL code -290 Invalid Description, if i set the output column to a byte stream. If I set the output column to an Image data type then I get a different error the package will not at that time even process it errors out right away. At least using a byte stream datatype it errors out when it is about to write to the olebd destination. Anybody have success using BLOB fields in SSIS package data flow? Thanks for any help.
View 5 Replies
View Related
Sep 19, 2007
I have a table with a column [FileData] of type TEXT or XML. I need to write the content of the column to a file, using sp_OAWrite for example, but the stored procedure who needs to read the content and write it to the file does not work because the limitation is no local variable for those types. What use is that datatype if i can not use it in procedures like this?
Anyone out there who could give me a tip on how to solve that?
View 6 Replies
View Related
Apr 29, 2014
I have created a trigger to call a program that is written by our program. The program is basically read the record in the table and write to a text file, then delete the record from the table.
The trigger is a after insert trigger. After we added the trigger, we insert a record to the table. The result is that the record still and did not get deleted. Also, the text file didn't get created either. It seems that it take a long time for the record to be written to the table.
But if we just run the program (a exe file), it can write a text file in the folder and delete the record. the trigger is basically:
USE [Zinter]
GO
/****** Object: Trigger [dbo].[ZinterProcess] Script Date: 04/29/2014 18:34:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
View 9 Replies
View Related
Aug 28, 2006
I have a web form that is an interface for a database. The code was working fine until a field needed to be added. So I added the new field, updated the data adapter and data set, and when i enter the data into the field, all of the old fields are getting updated, but the new ones aren't. I am at wits end as to why. Can someone please help!!! I am using VS Studio 2000 ASP.NET and C#. The code is below. The bold items are the new fields.using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;namespace TrafficDept{/// <summary>/// Summary description for AddOwner./// </summary>public class AddOwner : System.Web.UI.Page{protected System.Web.UI.WebControls.Button Button1;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3;protected System.Data.SqlClient.SqlConnection sqlConnection1;protected System.Web.UI.WebControls.TextBox TextBox1;protected System.Web.UI.WebControls.TextBox TextBox2;protected System.Data.SqlClient.SqlDataAdapter daAddOwner;protected System.Web.UI.WebControls.TextBox tbOtherOwnerLName;protected System.Web.UI.WebControls.Panel namePanel;protected System.Web.UI.WebControls.TextBox tbOtherOwnerMI;protected System.Web.UI.WebControls.TextBox tbOtherOwnerFName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerLName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerMI;protected System.Web.UI.WebControls.TextBox tbSecondOwnerFName;protected System.Web.UI.WebControls.Button Button4;protected SiteCubed.EditWorksProfessional tbNote;protected System.Web.UI.WebControls.Panel notePanel;protected System.Web.UI.WebControls.DropDownList ddlStatus;protected PeterBlum.PetersDatePackage.DateTextBox tbRegDate;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;protected System.Web.UI.WebControls.TextBox tbOwner;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;protected System.Web.UI.WebControls.TextBox tbOwnerMI;protected System.Web.UI.WebControls.TextBox tbOwnerLName;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator4;protected System.Web.UI.WebControls.TextBox tbBusinessName;protected System.Web.UI.WebControls.TextBox tbAddress;protected System.Web.UI.WebControls.TextBox tbRegNo;protected System.Web.UI.WebControls.TextBox tbPreviousOwner;protected System.Web.UI.WebControls.TextBox tbPOwnerAddress;protected System.Web.UI.WebControls.TextBox tbPORegNo;protected PeterBlum.PetersDatePackage.DateTextBox tbTransferDate;protected System.Web.UI.WebControls.DropDownList ddlMeans;protected System.Web.UI.WebControls.Button Button3;protected System.Web.UI.WebControls.Button Button2;protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;protected TrafficDept.dsAddOwner dsAddOwner1; private void Page_Load(object sender, System.EventArgs e){// Put user code to initialize the page here}#region Web Form Designer generated codeprivate void Button2_Click(object sender, System.EventArgs e){Response.Redirect("AddOthers.aspx");}private void Button1_Click(object sender, System.EventArgs e){daAddOwner.Fill(dsAddOwner1);dsAddOwner.OwnershipRow drOwner = dsAddOwner1.Ownership.NewOwnershipRow();drOwner.Plates_ID = int.Parse(Session["PlatesID"].ToString());drOwner.Status = ddlStatus.SelectedItem.Text;drOwner.Current_Legal_Owner = tbOwner.Text;drOwner.Owner_CurrentLegalOwnerMI = tbOwnerMI.Text.ToString();drOwner.Owner_CurrentLegalOwnerLName = tbOwnerLName.Text;drOwner.Owner_CurrentBusinessName = tbBusinessName.Text;if (!tbRegDate.xIsEmpty)drOwner.Date_of_Registration = DateTime.Parse(tbRegDate.Text.ToString());drOwner.Address_of_Current_Owner = tbAddress.Text;drOwner.Reg_No_for_Current_Owner = tbRegNo.Text;drOwner.Previous_Owner = tbPreviousOwner.Text;drOwner.Previous_Owner_Address = tbPOwnerAddress.Text;drOwner.Reg_No_for_Previous_Owner = tbPORegNo.Text;if (!tbTransferDate.xIsEmpty)drOwner.Date_of_Transfer = DateTime.Parse(tbTransferDate.Text.ToString());drOwner.By_Means_of = ddlMeans.SelectedItem.Text;drOwner.Owner_SecondaryLegalOwnerFName = tbSecondOwnerFName.Text;drOwner.Owner_SecondaryLegalOwnerMI = tbSecondOwnerMI.Text;drOwner.Owner_SecondaryLegalOwnerLName = tbSecondOwnerLName.Text;drOwner.Owner_OtherLegalOwnerFName = tbOtherOwnerFName.Text;drOwner.Owner_OtherLegalOwnerMI = tbOtherOwnerMI.Text;drOwner.Owner_OtherLegalOwnerLName = tbOtherOwnerLName.Text;drOwner.Owner_Notes = tbNote.Text;dsAddOwner1.Ownership.Rows.Add(drOwner);daAddOwner.Update(dsAddOwner1);Response.Redirect("AddOthersTP.aspx");}private void Button3_Click(object sender, System.EventArgs e){//daAddOwner.Fill(dsAddOwner1);//dsAddOwner.OwnershipRow drAddOwner = dsAddOwner1.Ownership.NewOwnershipRow();namePanel.Visible = true; }private void Button4_Click(object sender, System.EventArgs e){notePanel.Visible = true;}}}
View 1 Replies
View Related
Feb 15, 2008
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
View 4 Replies
View Related