Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database connection from remote server

RE: Database connection from remote server

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Mon, 19 Jul 2004 16:14:11 -0400
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA6506D9EF@25exch1.vicorpower.vicr.com> 





OK, I'll share with everyone, though this is going to get LONG.



First create a user inside your database that you know the password to & =
no one else does.  Inside this do the following:



create table password(username varchar2(30) constraint passwd_pk

                                            primary key,
                      ps_password varchar2(30) not null)


storage(initial 32K next 32K pctincrease 0);



create table psaccess(username varchar2(20),

                      priv_user varchar2(40) not null,
                      server_name varchar2(30) not null)


storage(initial 32K next 32K pctincrease 0);
alter table psaccess


add constraint psacc_fk


foreign key (username)


references password(username)


on delete cascade;


create table psaccess_audit(priv_user varchar2(40),

                             username varchar2(30),
                             server_name varchar2(30),
                             date_of_access date default sysdate,
                             status varchar2(10))


storage(initial 512K next 512K pctincrease 0);



Now populate password with the usernames (upper case please) and =
passwords needed.  The passwords need to be encrypted.  The encrypt =
package below does that.  It's not RSA secure, more like PGP.  Fill in =
psaccess with the username you want user, the Unix login name of the =
user (case sensitive) and the server's name (case sensitive).



create or replace package encrypt as


   function code(inp_data varchar2, key varchar2 default =
'MY_ENCRPTY_KEY') 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 :=3D '';
     divis :=3D 128;
     value :=3D ascii(c1);
     for loop1 in 0..7 loop
       if(trunc(value/divis) =3D 1) then
         r1 :=3D r1||'1';
       else
         r1 :=3D r1||'0';
       end if;
       value :=3D mod(value, divis);
       divis :=3D divis/2;
     end loop;
     return r1;




   end;


  =20


   function code(inp_data varchar2, key varchar2 default =
'MY_ENCRYPT_KEY') 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 :=3D key;
     while (length(inp_data) > length(key1)) loop
       key1 :=3D key1||key1;
     end loop;
     result :=3D '';
     for loop1 in 1..length(inp_data) loop
       r1 :=3D convbin(substr(inp_data,loop1,1));
       r2 :=3D convbin(substr(key1,loop1,1));
       divis :=3D 128;
       r3 :=3D 0;
       for loop11 in 1..8 loop
         =




if(to_number(substr(r1,loop11,1))+to_number(substr(r2,loop11,1)) =3D 1) =
then

             r3 :=3D r3+divis;
         end if;
         divis :=3D divis/2;
       end loop;
       result :=3D result||chr(r3);
     end loop;
     return result;




   end;


end;


/



grant execute on encrypt to public;


create public synonym encrypt for system.encrypt;



Recommend changing "MY_ENCRYPT_KEY" to a more acceptable key. Also =
recommend it be in the SYSTEM schema & that you wrap it with Oracle's =
WRAP utility.



Now as the Oracle user on Unix, compile the following Pro*C program.



#include <stdio.h>


#include <stdlib.h>



EXEC SQL BEGIN DECLARE SECTION;


         VARCHAR usr[20];
         VARCHAR pwd[20];
         VARCHAR db[20];
         VARCHAR upass[20];
         VARCHAR sname[20];
         VARCHAR state[11];
         char *evkr =3D NULL;
         VARCHAR nme[30];




EXEC SQL END DECLARE SECTION;



EXEC SQL INCLUDE SQLCA;


int main(int argc, char *argv[])


{  char rval[20];


   FILE *p =3D NULL;


   int pv =3D 0;



   rval[0] =3D '\0';


   strcpy(usr.arr, "YOUR_SCHEMA");


   strcpy(pwd.arr, "YOUR_PASSWORD");


   usr.len =3D strlen(usr.arr);


   pwd.len =3D strlen(pwd.arr);


   p =3D popen("/usr/bin/hostname", "r");
   if(p)


   {    fgets(rval, sizeof(rval), p);


        pv =3D pclose(p);


   }



   strcpy(sname.arr, rval);


   sname.len =3D strlen(sname.arr)-1;


   if(argc =3D=3D 3)


   {    strcpy(db.arr, argv[2]);

        db.len =3D strlen(db.arr);
        EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :db;




   }



   else


   {    EXEC SQL CONNECT :usr IDENTIFIED BY :pwd;


   }



   if(sqlca.sqlcode !=3D 0) exit(1);


   evkr =3D getenv("LOGNAME");


   if(argc >=3D 2)


   {    strcpy(nme.arr, argv[1]);

        nme.len =3D strlen(nme.arr);
        EXEC SQL SELECT ENCRYPT.CODE(PS_PASSWORD) INTO :upass
                 FROM PASSWORD A, PSACCESS B
                 WHERE A.USERNAME =3D B.USERNAME
                   AND PRIV_USER =3D :evkr
                   AND B.USERNAME =3D UPPER(:nme)
                   AND B.SERVER_NAME =3D :sname;




   }



   else


   {    EXEC SQL SELECT ENCRYPT.CODE(PS_PASSWORD), A.USERNAME


                 INTO :upass, :nme
                 FROM PASSWORD A, PSACCESS B
                 WHERE A.USERNAME =3D B.USERNAME
                   AND PRIV_USER =3D :evkr
                   AND B.SERVER_NAME =3D :sname;




   }



   if(sqlca.sqlcode !=3D 0)


   {    upass.arr[0] =3D '\0';


        strncpy(state.arr, sqlca.sqlerrm.sqlerrmc, 10);


   }



   else


   {    upass.arr[upass.len] =3D '\0';


        strcpy(state.arr, "PASS");


   }



   state.len =3D strlen(state.arr);


   EXEC SQL INSERT INTO PSACCESS_AUDIT



            VALUES(:evkr, :nme, :sname, SYSDATE, :state);
   if(sqlca.sqlcode !=3D 0)


   {    EXEC SQL ROLLBACK WORK RELEASE;



   }



   else


   {    EXEC SQL COMMIT WORK RELEASE;



   }



   printf("%s", upass.arr);


   exit(0);


}



NOTE: Put the username & Password in the right places above.  Compile =
with:



proc iname=3Dpspass.pc oname=3Dpspass.c


make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk EXE=3Dpspass =
OBJS=3Dpspass.o



To use:



sqlplus scott/`pspass scott MYDB`@MYDB



If you want I've a NT/Windoze 2k equavilent as well.  Not as pretty, but =
it works.  The nice part here is that it audits itself incase your =
paranoid. =20



Dick Goulet


Senior Oracle DBA


Oracle Certified 8i DBA





Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Mon Jul 19 2004 - 15:11:09 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US