Re: Field Encryption
From: Fernando G. Guerrero (fernan@guerrerog.org)
Date: 09/17/02
- Next message: Jake Morath: "RE: Hiding database objects"
- Previous message: Refd0m: "Re: SQL Security in ASP"
- In reply to: Widi: "Field Encryption"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Fernando G. Guerrero" <fernan@guerrerog.org> Date: Mon, 16 Sep 2002 22:58:41 -0400
Although using the CryptoAPI from a middle layer object could give you some
extra flexibility, you could use the following example, which creates a
table with encrypted passwords:
-- Table to store addresses and
-- encrypted passwords
CREATE TABLE UserAccounts (
LoginAddress varchar(100)
PRIMARY KEY,
LoginPassword varbinary(100)
)
GO
-- Procedure to store
-- encrypted passwords
CREATE PROCEDURE CreateAccount
@Address varchar(100),
@Password varchar(100)
WITH ENCRYPTION
AS
INSERT UserAccounts
SELECT @Address, ENCRYPT(@Password)
WHERE NOT EXISTS (
SELECT *
FROM UserAccounts
WHERE LoginAddress = @Address
)
IF @@ROWCOUNT = 0
RAISERROR('Impossible to create repeated account', 16, 1)
GO
-- Procedure to test
-- encrypted passwords
CREATE FUNCTION TestAccount (
@Address varchar(100),
@Password varchar(100))
RETURNS bit
WITH ENCRYPTION
AS
BEGIN
RETURN CASE
WHEN (
SELECT ENCRYPT(LoginPassword)
FROM UserAccounts
WHERE LoginAddress = @Address
) = ENCRYPT(@Password) THEN 1
ELSE 0 END
END
GO
-- Example
EXEC CreateAccount 'myname@mydomain.com', 'mypassword'
-- This account is correct
-- therefore it returns 1
SELECT dbo.TestAccount( 'myname@mydomain.com', 'mypassword') AS
'Correct'
-- This password is not correct
-- therefore it returns 0
SELECT dbo.TestAccount( 'myname@mydomain.com', 'MypassworD') AS 'Bad
password'
I would recommend using encryption at the network protocol level as well to
avoid passing passwords through the network as clear text.
You could something similar for SQL Server 7.0 by replacing the UDF by a
stored procedure.
I hope this helps
-- Fernando G. Guerrero SQL Server MVP QA plc., UK PASS Spanish Group www.sqlserverbyexample.com www.callsql.com www.qa.com "Share what you know, learn what you don't" "Widi" <widi711@hotmail.com> wrote in message news:OoP#90jWCHA.1660@tkmsftngp09... > Dear All, > > I have a table contain users accounts and passwords > > how to encrypt password fields > > Regards > Mohamed Talaat > >
- Next message: Jake Morath: "RE: Hiding database objects"
- Previous message: Refd0m: "Re: SQL Security in ASP"
- In reply to: Widi: "Field Encryption"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
