Any perl script to connect to DB using OPS$ account?

[sambavan]

Hi Folks,

IS there anyone in the wild who could give me a sample "working" perl script which would connect to the database using OPS$ account without the password ?

I'm almost exhausted on searching for the script. My concern is not to let out the passwords in cleartext in any of the perl scripts.

Any help would greatly be appreciated.

Thanx,
Sam

[sambavan]

Here for those interested minds.

You can use the perl DBI to connect to the database account that have been set to externally authenticate. Hence one can prevent exposing the passwords in the perl script. Well, it also depends on how secure the server is too. Also bear in mind this script can only be made worked on the environment where client and the server are on the same system.

1. Create an os account name SAM on the database server

2. create an externally authenticated user OPS$SAM in the database ABC.

3. Add the following entries to your tnsnames.ora file

  ABC_BEQ.TEST.COM =                                 <-- beq-local for a local connection TEST.COM  = domain 
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = 
              (PROTOCOL = BEQ)		
              (PROGRAM = /oracle/product/9207)   <-- identifies the Oracle exe
              (ARGV0 = abc)	                             <-- identifies the sid
              (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')                    <--identifies the source of the connection (local)
)
          )
          (CONNECT_DATA = (SID = abc))	  <-- sid = default sid of database
        )

If you have the names.default_domain defined in the sqlnet.ora remember to include it on defining the net service name to avoid any confusions later.

4. Now use the following perl script under os user account SAM to test the correct workings of perl.

#!/usr/bin/perl
# Setup DBI stuff

use DBI;
use strict;
use vars qw($sth @ary);
$ENV{'ORACLE_HOME'}='/oracle/product/9207';
# Remember to use the SID of the database in ORACLE_SID
$ENV{ORACLE_SID}='abc';
delete $ENV{TWO_TASK};

my $stmnt = 'SELECT \'Connected to database successfully! \' FROM dual';                     

# Remember to repoint the net service name to the one defined with the BEQ protocol
my $dbh1 = DBI->connect("dbi:Oracle:abc_beq.test.com", "/")
 or die "Can't make the database connection : $DBI::errstr\n";

   $sth = $dbh1->prepare($stmnt);
   $sth->execute()  or die $sth->errstr ;
   while (@ary= $sth->fetchrow_array) {
     print "@ary \n";
   }
  $dbh1->disconnect or warn "Disconnection failed $DBI::errstr\n";
exit;

This should carry you long way.

[marist89]

Also bear in mind this script can only be made worked on the environment where client and the server are on the same system.

This is not quite true. As long as an OS authenticated user can connect to your database he can connect via perl DBI. In fact, this his how I run a lot of anonymous web-server sessions.

[sambavan]

remote_os_authentication needs to be enabled. which in my opinion is a security threat.


sam

[marist89]

Well, that's true, it has to be enabled. Is that a security threat? Maybe, maybe not.