Re:encrypt passwords and hold on Oracle tables

From: <dgoulet_at_vicr.com>
Date: Tue, 15 Aug 2000 10:38:56 -0400
Message-Id: <10590.114608@fatcity.com>

John,

    We use a similar type of method for our automated line. Basically each cell logs onto the database with a unique username for each workstation & the users are only 'validated' by the application. Anyway, to get encryption of passwords in 8.0 I downloaded a pile of PL/SQL from Metalink. Here is the reworked version:

create or replace package encrypt as

   function code(inp_data varchar2, key varchar2 default '<YOUR DESIRED DEFAULT>') return varchar2;

   pragma restrict_references(code, RNDS, WNDS, WNPS); end;
/

create or replace package body encrypt is

   function convbin(c1 varchar2) return varchar2 is

     loop1 number;
     value number;
     divis number;
     r1 varchar2(30);
   begin
     r1 := '';
     divis := 128;
     value := ascii(c1);
     for loop1 in 0..7 loop
       if(trunc(value/divis) = 1) then
         r1 := r1||'1';
       else
         r1 := r1||'0';
       end if;
       value := mod(value, divis);
       divis := divis/2;
     end loop;
     return r1;

   end;    

   function code(inp_data varchar2, key varchar2 default '<YOUR DESIRED DEFAULT>') return varchar2 is

     loop1 number;
     loop11 number;
     r1 varchar2(8);
     r2 varchar2(8);
     key1 varchar2(4000);
     r3 number;
     result varchar2(40);
     divis number;
   begin
     key1 := key;
     while (length(inp_data) > length(key1)) loop
       key1 := key1||key1;
     end loop;
     result := '';
     for loop1 in 1..length(inp_data) loop
       r1 := convbin(substr(inp_data,loop1,1));
       r2 := convbin(substr(key1,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;
end;
/

grant execute on encrypt to public;
create public synonym encrypt for system.encrypt;    

____________________Reply Separator____________________

Subject: encrypt passwords and hold on Oracle tables Author: John Dunn <john.dunn_at_sefas.co.uk> Date: 8/15/00 4:26 AM

Our development team want to control access to application functionality via 'logical' users. That is, a list of users and the application functions they can use will be maintained in a database table. Actual connection to the database would always be via one user(maybe the schema owner, maybe some other single specified user).

Does anyone else have applications that work in this way? What use do you use to connect to the database?

The 'logical' users would also have passwords that would need to be held on the database tables. Is there any (easy) way to encrypt a character string and store it on the database?

The front end application is Visual Basic using OO4O...but we use lots of PL/SQL too.

Database is Oracle 8.0.5

John  

-- 
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Tue Aug 15 2000 - 09:38:56 CDT