Rem -----------------------------------------------------------------------------------
Rem
Rem
Rem Name : Encrypt_PassWord.Sql
Rem
Rem Purpose : This script helps create package for Hint Product in DWH Schema
Rem
Rem
Rem
Rem DD/MM/YYYY Created by Reason
Rem ---------- ----------- ------
Rem 11/8/2010 HINT team This script helps create package for Hint Product in DWH Schema
Rem ---------------------------------------------------------------------------------
Spool Encrypt_PassWord.Log;
Create or Replace PACKAGE APP_PASSWORD as
FUNCTION ENCRYPT (P_string IN VARCHAR2) RETURN VARCHAR2;
FUNCTION DECRYPT (P_string IN VARCHAR2) RETURN VARCHAR2;
FUNCTION ADDHASH(P_keystring IN VARCHAR2, P_datastring IN VARCHAR2) RETURN VARCHAR2;
FUNCTION SUBHASH(P_keystring IN VARCHAR2, P_datahex IN VARCHAR2) RETURN VARCHAR2;
FUNCTION SETHASHLIMIT(P_newlimit IN NUMBER) RETURN NUMBER;
END APP_PASSWORD;
/
PROMPT Creating PACKAGE BODY 'APP_PASSWORD'
Create or Replace PACKAGE BODY APP_PASSWORD AS
TYPE INTTAB IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
TYPE NUMTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
X_hextable VARCHAR2(256) := '0123456789ABCDEF'||
'1032547698BADCFE'||
'23016745AB89EFCD'||
'32107654BA98FEDC'||
'45670123CDEF89AB'||
'54761032DCFE98BA'||
'67452301EFCDAB89'||
'76543210FEDCBA98'||
'89ABCDEF01234567'||
'98BADCFE10325476'||
'AB89EFCD23016745'||
'BA98FEDC32107654'||
'CDEF89AB45670123'||
'DCFE98BA54761032'||
'EFCDAB8967452301'||
'FEDCBA9876543210';
X_hashlimit NUMBER := 32;
X_savekey VARCHAR2(256);
X_savetable INTTAB;
X_keylimit NUMBER := 10;
X_crcarray NUMTAB;
FUNCTION ENCRYPT (P_string VARCHAR2) RETURN VARCHAR2 IS
X_key VARCHAR2(100);
X_return VARCHAR2(2000);
X_length NUMBER;
X_counter NUMBER;
BEGIN
X_return := '';
IF P_string IS NOT NULL THEN
X_length := length(P_string);
X_counter := 1;
X_key := '1gf:dUR(DS=29q0+';
WHILE X_counter <= X_length LOOP
X_return := X_return||addhash(X_key,substr(P_string,X_counter, 20));
X_counter := X_counter + 20;
END LOOP;
END IF;
RETURN X_return;
END ENCRYPT;
FUNCTION DECRYPT(P_string VARCHAR2) RETURN VARCHAR2
IS
X_key VARCHAR2(100);
X_return VARCHAR2(2000);
X_length NUMBER;
X_counter NUMBER;
BEGIN
X_return := '';
IF P_string IS NOT NULL THEN
X_length := length(P_string);
X_counter := 1;
X_key := '1gf:dUR(DS=29q0+';
WHILE X_counter <= X_length LOOP
X_return := X_return||subhash(X_key,substr(P_string,X_counter, 64));
X_counter := X_counter + 64;
END LOOP;
END IF;
RETURN X_return;
END DECRYPT;
FUNCTION XORTOHEX(X_b1 IN BINARY_INTEGER, X_b2 IN BINARY_INTEGER) RETURN VARCHAR2
IS
/*
** Compute the XOR of two byte values and return a 2-character
** hexadecimal string result.
*/
X_unibble BINARY_INTEGER;
X_lnibble BINARY_INTEGER;
X_x1 BINARY_INTEGER;
X_x2 BINARY_INTEGER;
BEGIN
X_x1 := MOD(X_b1, 16);
X_x2 := MOD(X_b2, 16);
X_lnibble := X_x1 * 16 + X_x2 + 1;
X_unibble := (X_b1 - X_x1) + (X_b2 - X_x2)/16 + 1;
RETURN(substr(X_hextable, X_unibble, 1)||substr(X_hextable, X_lnibble, 1));
END XORTOHEX;
FUNCTION XORTOCHR(X_b1 IN BINARY_INTEGER, X_b2 IN BINARY_INTEGER) RETURN VARCHAR2
IS
/*
** Compute the XOR of two byte values and return a 1-byte (character)
** result. (Note: this might be invalid for multibyte character
** sets, since this function will THEN be returning only a portion
** of a full character in the CHR result -- needs to be checked.)
*/
X_unibble BINARY_INTEGER;
X_lnibble BINARY_INTEGER;
X_x1 BINARY_INTEGER;
X_x2 BINARY_INTEGER;
BEGIN
X_x1 := MOD(X_b1, 16);
X_x2 := MOD(X_b2, 16);
X_lnibble := X_x1 * 16 + X_x2 + 1;
X_unibble := (X_b1 - X_x1) + (X_b2 - X_x2)/16 + 1;
RETURN(chr(instr('0123456789ABCDEF', substr(X_hextable, X_unibble, 1)) * 16 +
instr('0123456789ABCDEF', substr(X_hextable, X_lnibble, 1)) - 17));
END XORTOCHR;
PROCEDURE RC4INIT(P_keystring IN VARCHAR2, P_rctable IN OUT INTTAB)
IS
/*
** Initialize RC4 from the KEYSTRING, common to both ENCRYPT and DECRYPT.
*/
X_dataint INTTAB;
X_rccount BINARY_INTEGER;
X_rcswap BINARY_INTEGER;
X_slen BINARY_INTEGER;
X_dlen BINARY_INTEGER;
X_kloc BINARY_INTEGER;
X_ktemp BINARY_INTEGER;
BEGIN
-- Check for key match with previous initialization
IF (substr(P_keystring,1,X_keylimit) = X_savekey) THEN
P_rctable := X_savetable;
ELSE
-- Turn the key string into a series of binary integer byte values
X_dlen := 0;
X_slen := length(P_keystring);
IF (X_slen > X_keylimit) THEN
X_slen := X_keylimit;
END IF;
FOR X_kloc IN 1..X_slen LOOP
X_ktemp := ascii(substr(P_keystring,X_kloc,1));
IF (X_ktemp < 256) THEN
X_dlen := X_dlen + 1;
X_dataint(X_dlen) := X_ktemp;
ELSIF (X_ktemp < 256 * 256) THEN
X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
X_dlen := X_dlen + 2;
ELSIF (X_ktemp < 256 * 256 * 256) THEN
X_dataint(X_dlen + 3) := MOD(X_ktemp, 256);
X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
X_dlen := X_dlen + 3;
ELSE
X_dataint(X_dlen + 4) := MOD(X_ktemp, 256);
X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
X_dataint(X_dlen + 3) := MOD(X_ktemp, 256);
X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
X_dlen := X_dlen + 4;
END IF;
END LOOP;
-- Initialize the RC4 algorithm using the encryption key string
FOR X_rccount IN 1..256 LOOP
P_rctable(X_rccount) := X_rccount - 1;
END LOOP;
X_rcswap := 0;
X_kloc := 1;
FOR X_rccount IN 1..256 LOOP
X_ktemp := P_rctable(X_rccount);
X_rcswap := X_rcswap + X_ktemp + X_dataint(X_kloc);
IF (X_rcswap >= 512) THEN
X_rcswap := X_rcswap - 512;
ELSIF (X_rcswap >= 256) THEN
X_rcswap := X_rcswap - 256;
END IF;
P_rctable(X_rccount) := P_rctable(X_rcswap + 1);
P_rctable(X_rcswap + 1) := X_ktemp;
IF (X_kloc >= X_dlen) THEN
X_kloc := X_kloc - X_dlen;
END IF;
X_kloc := X_kloc + 1;
END LOOP;
-- Save table for next time
X_savetable := P_rctable;
X_savekey := substr(P_keystring,1,X_keylimit);
END IF;
END RC4INIT;
FUNCTION ADDHASH(P_keystring IN VARCHAR2, P_datastring IN VARCHAR2) RETURN VARCHAR2
IS
X_dataint INTTAB;
X_rctable INTTAB;
X_rccount BINARY_INTEGER;
X_rcswap BINARY_INTEGER;