--**************************************
-- Name: Encryption Utilities
-- Description:Utility to Encrypt and Salt Hash sensitive passwords.
-- By: Brad G Skidmore
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1182&lngWId=5--for details.--**************************************
USE [!!!!EnterYourDatabaseNameHERE!!!!!]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udtGetHash]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udtGetHash]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udtGetRandomStr]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udtGetRandomStr]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwGetRanNum0To9]'))
DROP VIEW [dbo].[vwGetRanNum0To9]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwGetRanLetterAtoZ]'))
DROP VIEW [dbo].[vwGetRanLetterAtoZ]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwGetRanLetterAtoZLow]'))
DROP VIEW [dbo].[vwGetRanLetterAtoZLow]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwGetRanLetterAtoZUp]'))
DROP VIEW [dbo].[vwGetRanLetterAtoZUp]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwFlipCoin]'))
DROP VIEW [dbo].[vwFlipCoin]
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
*
* Description: View a Random Letter A to Z Lower CASE ONLY
* Returns single record single char Lower CASE
* Col [GetRanLetterAtoZLow] = "{Random Letter A to Z Lower CASE}"
*
* Author: Brad Skidmore
* Date: 9/21/2007
*
******************************************************************/
CREATE VIEW [dbo].[vwGetRanLetterAtoZLow]
AS
SELECT
(
CASE tRet.AscNum
WHEN 96 THEN Char(97)
WHEN 123 THEN Char(122)
ELSE Char(tRet.AscNum)
END
) As [GetRanLetterAtoZLow]
FROM
(
SELECT (97 + Cast(Round(26 * RAND(),1) As TinyInt)) As AscNum
) tRet
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
*
* Description: View a Random Letter A to Z UPPER CASE ONLY
* Returns single record single char UPPER CASE
* Col [GetRanLetterAtoZUp] = "{Random Letter A to Z UPPER CASE}"
*
* Author: Brad Skidmore
* Date: 9/21/2007
*
******************************************************************/
CREATE VIEW [dbo].[vwGetRanLetterAtoZUp]
AS
SELECT
(
CASE tRet.AscNum
WHEN 64 THEN Char(65)
WHEN 91 THEN Char(90)
ELSE Char(tRet.AscNum)
END
) As [GetRanLetterAtoZUp]
FROM
(
SELECT (65 + Cast(Round(26 * RAND(),1) As TinyInt)) As AscNum
) tRet
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
*
* Description: View a Coin Toss (Flip A Coin)
* Returns single record Bit
* Col [FlipCoin] = {Random Bit value 0 or 1}
*
* Author: Brad Skidmore
* Date: 9/21/2007
*
******************************************************************/
CREATE VIEW [dbo].[vwFlipCoin]
AS
SELECT Cast(Round(1 * RAND(),0) As Bit) As FlipCoin
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
*
* Description: View a Random Number 0 to 9
* Returns single record single digit Tiny Int
* Col [GetRanNum0To9] = {Random Number 0 to 9}
*
* Author: Brad Skidmore
* Date: 9/21/2007
*
******************************************************************/
CREATE VIEW [dbo].[vwGetRanNum0To9]
AS
SELECT Cast(Round(9 * RAND(),0) As TinyInt) As [GetRanNum0To9]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
*
* Description: View a Random Letter A to Z
* Returns single record single char
* Col [GetRanLetterAtoZ] = "{Random Letter A to Z may be UPPER or Lower Case}"
*
* Author: Brad Skidmore
* Date: 9/21/2007
*
******************************************************************/
CREATE VIEW [dbo].[vwGetRanLetterAtoZ]
AS
SELECT
(
CASE tRet.[FlipCoin]
WHEN 1 THEN
tRet.GetRanLetterAtoZLow
ELSE
tRet.GetRanLetterAtoZUp
END
) As [GetRanLetterAtoZ]
FROM
(
SELECT [FlipCoin]
, (SELECT [GetRanLetterAtoZLow] FROM [EncryptTEST].[dbo].[vwGetRanLetterAtoZLow]) As GetRanLetterAtoZLow
, (SELECT [GetRanLetterAtoZUp] FROM [EncryptTEST].[dbo].[vwGetRanLetterAtoZUp]) As GetRanLetterAtoZUp
FROM [dbo].[vwFlipCoin]
) tRet
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
*
* Description: Get a Random Str
*
* Author: Brad Skidmore
* Date: 9/21/2007
*
******************************************************************/
CREATE FUNCTION [dbo].[udtGetRandomStr]
(
@piMaxLen TinyInt = 255
, @pbUseNumbers bit =1
, @pbUseLetters bit=1
, @pbUseUPPER bit =1
, @pbUseLower bit =1
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @sRandomStr varchar(255)
DECLARE @sLetter char(1)
DECLARE @iMaxLen TinyInt
DECLARE @iNum TinyInt
DECLARE @iPos TinyInt
DECLARE @bFlipACoin bit
DECLARE @bUseNumbers bit
DECLARE @bUseLetters bit
DECLARE @bUseUPPER bit
DECLARE @bUseLower bit
-- --BEGIN DEBUG
-- SET @iMaxLen = 255
-- SET @bUseNumbers = 0
-- SET @bUseLetters = 1
-- SET @bUseUPPER = 1
-- SET @bUseLower = 0
-- --END DEBUG
--BEGIN Prod
SET @iMaxLen = @piMaxLen
SET @bUseNumbers = @pbUseNumbers
SET @bUseLetters = @pbUseLetters
SET @bUseUPPER = @pbUseUPPER
SET @bUseLower = @pbUseLower
--END Prod
SET @sRandomStr = ''
SET @iPos = 0
WHILE @iPos < @iMaxLen
BEGIN
SET @iPos = @iPos + 1
SET @bFlipACoin = (
SELECT [FlipCoin]
FROM [dbo].[vwFlipCoin]
)
IF @bUseNumbers = 1 And @bUseLetters = 0
BEGIN
SET @bFlipACoin = 1
END
ELSE IF @bUseNumbers = 0 And @bUseLetters = 1
BEGIN
SET @bFlipACoin = 0
END
ELSE IF @bFlipACoin = 1
BEGIN
--Flip again to use numbers less frequently than letters
--Peppered Numbers!
SET @bFlipACoin = (
SELECT [FlipCoin]
FROM [dbo].[vwFlipCoin]
)
END
IF @bFlipACoin = 1
BEGIN
--Get a Random Number
SET @iNum = (
SELECT [GetRanNum0To9]
FROM [EncryptTEST].[dbo].[vwGetRanNum0To9]
)
SET @sLetter = Cast(@iNum As VarChar(1))
END
ELSE IF @bUseUPPER = 1 and @bUseLower = 0
BEGIN
SET @sLetter = (
SELECT [GetRanLetterAtoZUp]
FROM [EncryptTEST].[dbo].[vwGetRanLetterAtoZUp]
)
END
ELSE IF @bUseUPPER = 0 and @bUseLower = 1
BEGIN
SET @sLetter = (
SELECT [GetRanLetterAtoZLow]
FROM [EncryptTEST].[dbo].[vwGetRanLetterAtoZLow]
)
END
ELSE
BEGIN
--IF both flags are 0 or 1 then get random letter random upper or lower
SET @sLetter = (
SELECT [GetRanLetterAtoZ]
FROM [EncryptTEST].[dbo].[vwGetRanLetterAtoZ]
)
END
SET @sRandomStr = @sRandomStr + @sLetter
END
-- --BEGIN DEBUG
-- PRINT 'Random Str: ' + @sRandomStr
-- --END DEBUG
RETURN(@sRandomStr)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
*
* Description: Get Hash
* "Finger Print of passed in Plain Text string (Password)"
* Leave @psOldSaltyHash null to get a new "Salted" Hash.
* Or
* Pass in Plain Text with previously salted hash
* to see if the hash is identical. if so... then the
* plaintext authenticates to @psOldSaltyHash
*
* This Hash function is a wrapper to:
* SQLCrypto Extended Stored Procedures
* 1. master.dbo.xp_sqlcrypto_encrypt
* 2. master.dbo.xp_sqlcrypto_hash
* See their Web Site (www.sqlcrypto.com)
* Author: Brad Skidmore
* Date: 9/21/2007
*
******************************************************************/
CREATE FUNCTION [dbo].[udtGetHash]
(
@psPlainText varchar(50)
,@psOldSaltyHash char(40)=null
)
RETURNS varchar(40)
AS
BEGIN
DECLARE @sudtGetHash varchar(40)
DECLARE @sEncryptOut varchar(78)
DECLARE @iRet int
DECLARE @sALGcrypt varchar(20)
DECLARE @sALGhash varchar(20)
DECLARE @sHashSalt varchar(51)
DECLARE @sRandomString varchar(8)
DECLARE @iLenRand TinyInt
DECLARE @sPlainText varchar(50)
DECLARE @sOldSaltyHash varchar(40)
-- --BEGIN DEBUG
-- SET @sPlainText = 'pass123'
-- SET @sOldSaltyHash = null
-- --SET @sOldSaltyHash = 'faa4e8b4320f34b875786c57313942b1270f5bez'
-- --END DEBUG
--BEGIN PROD
SET @sPlainText = @psPlainText
SET @sOldSaltyHash = @psOldSaltyHash
--END PROD
--BEGIN ****** Whatever you do in life... Hari-kiri or seppuku****
--DO NOT CHANGE THESE AlgorithmS without first contemplating Hari-kiri or seppuku.
--Once you choose an Algorithm and hash all your passwords it would be impossible to change the Algorithm!
--If you do there is no way to update exisiting hashes without the actual plain text passwords!
/*
AVAILABLE Encryption Algorithms
(
'AES'
, 'BLOWFISH'
, 'CAST5'
, 'DES3'
, 'DESX'
, 'IDEA_ECB'
, 'IDEA_CBC'
, 'IDEA_CFB'
, 'IDEA_OFB'
, 'RC4'
, 'RC5'
)
AVAILABLE Hash Algorithms
(
'MD5'
, 'SHA1'
, 'RIPEMD160'
)
--MUST HAVE SQLCrypto Team Edition System Intalled for certain Algorithms.
*/
SET @sALGcrypt = 'RC4' --Encryption Algorithm
SET @sALGhash = 'SHA1' --Hash Algorithm
--END ****** Whatever you do in life... Hari-kiri or seppuku****
EXEC @iRet = master.dbo.xp_sqlcrypto_encrypt
@sPlainText --VAR1
, @sPlainText --Key!
, @sALGcrypt --Type of Algorithm
, @sEncryptOut OUTPUT --Encryption Output to Var
-- --BEGIN DEBUG
-- Print 'Encryption: ' + @sEncryptOut
-- --END DEBUG
--Salting chars
IF @sALGhash = 'MD5'
BEGIN
Set @iLenRand = 8
END
ELSE
BEGIN
Set @iLenRand = 1
END
IF @sOldSaltyHash Is null
BEGIN
SET @sRandomString = dbo.udtGetRandomStr
(
@iLenRand --@piMaxLen TinyInt = 1
, 1 --, @pbUseNumbers bit =1
, 1 --, @pbUseLetters bit=1
, 0 --, @pbUseUPPER bit =1
, 1 --, @pbUseLower bit =1
) --udtGetRandomStr varchar(255)
END
ELSE
BEGIN
SET @sRandomString = Right(@sOldSaltyHash, @iLenRand)
END
-- --BEGIN DEBUG
-- Print @sRandomString
-- --END DEBUG
SET @sHashSalt = @sPlainText + @sRandomString
--DECLARE @iRet int
EXEC @iRet = master.dbo.xp_sqlcrypto_hash
@sEncryptOut
,@sHashSalt
,@sALGhash
,@sudtGetHash OUTPUT
IF @sALGhash = 'MD5'
BEGIN
SET @sudtGetHash = @sudtGetHash + @sRandomString
END
ELSE
BEGIN
SET @sudtGetHash = Left(@sudtGetHash, Len(@sudtGetHash) - Len(@sRandomString)) + @sRandomString
END
-- --BEGIN DEBUG
-- Print 'Hash; =' + @sudtGetHash
-- --END DEBUG
RETURN(@sudtGetHash)
END
|
