Re: Can you encrypt a column value in Oracle?

From: Chris Kempster <c.kempster_at_cowan.edu.au>
Date: Fri, 5 Jun 1998 17:08:12 +0800
Message-ID: <6l8cq6$rqb$1@news.cowan.edu.au>

Give this a go:

PACKAGE BODY security_maintenance IS

/*------------------------- Public
Modules ------------------------------------*/

FUNCTION encrypt(in_val VARCHAR2, crypt_mask VARCHAR2) RETURN VARCHAR2; FUNCTION unencrypt(in_val VARCHAR2, crypt_mask VARCHAR2) RETURN VARCHAR2;

/*------------------------- Private
Modules -----------------------------------*/

FUNCTION convbin(c1 char) RETURN CHAR;
FUNCTION XORBIN(c1 char,c2 char) RETURN CHAR;

/*
|| Parameters : in_val to be crypted.
|| crypt_mask value used to crypt in_val
||
|| Return Values : encrypted value
||
|| Purpose : Encrypts a value based on a specified input mask.
||
|| Dependencies : crypt_mask must be >= in size to in_val
||
|| Modification History : 21/05/98, created.
*/
FUNCTION encrypt(in_val VARCHAR2, crypt_mask VARCHAR2) RETURN VARCHAR2 IS

BEGIN

• Must be greater or equal to the value that will be crypted

 IF LENGTH(crypt_mask) < LENGTH(in_val) THEN   RETURN NULL;
 ELSE
  RETURN (XORBIN(in_val, crypt_mask)); /* "Crypt" */  END IF;
END; /*
|| Parameters : in_val to be unencrypted.
|| crypt_mask value used to unencrypt in_val
||
|| Return Values : un-encrypted value
||
|| Purpose : Un-encrypted in_val previously crypted with the crypt
function (see above)
||
|| Dependencies : used crypt function before, and mask is the same
||
|| Modification History : 21/05/98

*/
FUNCTION unencrypt(in_val VARCHAR2, crypt_mask VARCHAR2) RETURN VARCHAR2 IS

BEGIN
 RETURN(XORBIN(in_val, crypt_mask)); /* "Un-Encrypt" */ END; /*
|| Parameters : c1, character to be converted to a binary value
|| Return Values : binary equiv of c1
||
|| Purpose : Used during crypt/unencrypt
||
|| Dependencies :
||
|| Modification History : 21/05/98, created.
*/
FUNCTION convbin(c1 char) RETURN CHAR IS

loop1 number;
value number;
divis number;

r1 varchar2(30);

BEGIN
 r1 := '';
 value := ASCII(c1);
 divis := 128;

 FOR loop1 IN 0..7 LOOP

  IF TRUNC(value/divis) = 1 THEN
   r1 := r1 || '1';
  ELSE
   r1 := r1 || '0';
  END IF;   value := value mod divis;
  divis := divis / 2;
 END LOOP;  RETURN r1;
END; /*
|| Parameters : c1 and c2
|| Return Values : xors c2 with c1
||
|| Purpose : Used during encryption
||
|| Dependencies : c1 and c2 similar sizes
||
|| Modification History : 21/05/98, created.
*/
FUNCTION XORBIN(c1 char,c2 char) RETURN CHAR IS

loop1 NUMBER;
loop11 NUMBER;
r1 VARCHAR2(8);
r2 VARCHAR2(8);
r3 NUMBER;
result VARCHAR2(40);
divis NUMBER;

BEGIN  result := '';

 FOR loop1 IN 1..length(c1) LOOP

  r1 := convbin(substr(c1,loop1,1));
  r2 := convbin(substr(c2,loop1,1));
  divis := 128;
  r3 := 0;

  FOR loop11 IN 1..8 LOOP

   IF TO_NUMBER(substr(r1,loop11,1)) + TO_NUMBER(substr(r2,loop11,1))=1 THEN     r3 := r3 + divis;
   END IF;    divis := divis / 2;

  END LOOP;   result := result || chr(r3);

 END LOOP;  RETURN(result);

END XORBIN; END; Sean Dolan wrote in message ...
> I am trying to put a user's password in a table called USER_INFO in a column called PASSWORD... is there a way to have that value encrypted so that I can;t see the user's password? (but rather an encrypted garble)... I am on Oracle 7.3.4.

>
>Thanks,
>Sean Dolan
>Senior Systems Engineer

Received on Fri Jun 05 1998 - 04:08:12 CDT