Oracle connect internal/OS authentification

[Christian Merz]

Hello all,

this topic has been discussed several times, i know. But i have not seen a
solution by now.

On UNIX i can open an Oracle DB "connect internal" connection by
$ sqlplus "/ as sysdba"
when i am in OS group 'dba' - there is no need to specify a password.

Whichever type of connection i use with DBI i HAVE TO specify a password.
But i certainly do not want to code my sys/system passwords in my skripts.

Any short slution on this?

Do i have to set up a password manager (host/instance/user/password) ?
-> Which i would do only as an emergency solution.

Thanx, Christian
---------------------------------------------------------
What difference does it make to the dead, the orphans,
and the homeless, whether the mad destruction is brought
under the name of totalitarianism or the holy name of liberty
and democracy?
- Gandhi

[Samuel Gold]

Sorry, but there is no way. / as sysdba is a SQL*Plus specific command. I
opened a tar with oracle on the issue of being able to connect / as sysdba
and using OCI to shutdown the database like the shutdown command in
SQL*Plus. They told me that neither could be done. I created a user with
very specific privileges and set very restrictive permissions on my script
that contains that information. I also created packages to do specific
things and granted execute on those packages to that user. That is the best
solution that I have been able to come up with. I hope that helps.

Thanks
Sam Gold

[Alan Burlison]

Gold, Samuel (Contractor) wrote:

> Sorry, but there is no way. / as sysdba is a SQL*Plus specific command. I
> opened a tar with oracle on the issue of being able to connect / as sysdba
> and using OCI to shutdown the database like the shutdown command in
> SQL*Plus. They told me that neither could be done. I created a user with
> very specific privileges and set very restrictive permissions on my script
> that contains that information. I also created packages to do specific
> things and granted execute on those packages to that user. That is the best
> solution that I have been able to come up with. I hope that helps.

use DBD::Oracle qw(:ora_session_modes);

# Connect and initialise.
my %attr = ( AutoCommit => 0, PrintError => 0 };
if ($username =~ /^SYS\s+AS\s+(SYS(?:DBA|OPER))$/i) {
$username = 'SYS';
if ($1 =~ /^SYSDBA$/i) {
$attr{ora_session_mode} = ORA_SYSDBA;
} else {
$attr{ora_session_mode} = ORA_SYSOPER;
}
}
if (! ($Db = DBI->connect("dbi:Oracle:$database",
$username, $password, \%attr))) {
error("Can't login to Oracle:", $DBI::errstr);
return(0);

--
Alan Burlison
--

[Samuel Gold]

Yes, I am aware of that, but you still need a username and password. In
SQL*Plus which is an Oracle specific application you can connect using "/ as
sysdba" which uses the operating system to verify if you belong to the dba
group. Using DBD you cannot use the "/ as sysdba" to connect. You can use
the ora_session_modes but you still need to pass a username and password.
If you look at your code you are connecting as "SYS as SYSDBA" not "/ as
sysdba".

[Alan Burlison]

Gold, Samuel (Contractor) wrote:

> Yes, I am aware of that, but you still need a username and password. In
> SQL*Plus which is an Oracle specific application you can connect using "/ as
> sysdba" which uses the operating system to verify if you belong to the dba
> group. Using DBD you cannot use the "/ as sysdba" to connect. You can use
> the ora_session_modes but you still need to pass a username and password.
> If you look at your code you are connecting as "SYS as SYSDBA" not "/ as
> sysdba".

Ah right, I see what you mean. Bummer.

--
Alan Burlison
--

[Samuel Gold]

tell me about it. :)

-----Original Message-----
From: Alan Burlison [mailto:Alan.B...@sun.com]
Sent: Thursday, June 05, 2003 12:32 PM
To: Gold, Samuel (Contractor)
Cc: 'Christian Merz'; dbi-...@perl.org
Subject: Re: Oracle connect internal/OS authentification

[Tim Bunce]

I heard it was a limitation of OCI. The kind of connection you needs
uses a lower level API that's not publically available. If it was, I'd add it.

Tim.

[Andy Hassall]

Christian Merz wrote:
> this topic has been discussed several times, i know. But i have not
> seen a solution by now.
>
> On UNIX i can open an Oracle DB "connect internal" connection by
> $ sqlplus "/ as sysdba"
> when i am in OS group 'dba' - there is no need to specify a password.
>
> Whichever type of connection i use with DBI i HAVE TO specify a
> password. But i certainly do not want to code my sys/system passwords
> in my skripts.
>
> Any short slution on this?

FWIW it _appears_ to work on Windows, at least, unless I've done something
silly?

#!perl -w
use strict;
use DBI;
use DBD::Oracle qw/:ora_session_modes/;

my $dbh = DBI->connect('dbi:Oracle:',
shift||'',
shift||'',
{
RaiseError => 1,
AutoCommit => 0,
ora_session_mode => ORA_SYSDBA,
})
or die DBI->errstr;

print $dbh->selectrow_array('select instance_name from v$instance') . "\n";
print $dbh->selectrow_array('select user from dual') . "\n";

$dbh->disconnect;

__END__

ActiveState Perl 5.8.0, DBD-Oracle 1.14 + a couple of minor patches (nothing
to do with OS auth though), DBI 1.37.
Windows 2000, Oracle 9.2.0.3, user is a member of the ORA_DBA group and OS
authentication temporarily turned on for this test.

Running this with a few different parameters gives:

andyh@testbox /cygdrive/h/temp
$ perl os_auth_test.pl
dev92
SYS

andyh@testbox /cygdrive/h/temp
$ perl os_auth_test.pl darth vader
dev92
SYS

andyh@testbox /cygdrive/h/temp
$ perl os_auth_test.pl darth
DBI connect('','darth',...) failed: ORA-01005: null password given; logon
denied (DBD ERROR: OCISessionBegin) at os_auth_test.pl line 6

Which seems to show it works... but if you specify a username you have to at
least put some nonsense in for the password.

Also works if I put the database name into the DSN, i.e. 'dbi:Oracle:dev92'.

(Haven't got around to putting Oracle on the Linux partition yet though, and
haven't got access to a Unix environment with Oracle until next week; so
milage may vary on Unix)

--
Andy Hassall (an...@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Alan Burlison]

Hmm, this seems to work for me as well (on Solaris). The trick seems to be
to set the ora_session_mode to ORA_SYSDBA, provide a username of 'SYS' and a
*non-null* password - anything will do, even a single space. I've checked
this from my primary oracle login account, and by adding/removing a normal
user from/to the dba group:

# User not in dba group
t$ /tmp/dba
ORA-01031: insufficient privileges (DBD ERROR: OCISessionBegin)

# User added to dba group
$ /tmp/dba
BASALT
SYS

--
Alan Burlison
--

[Christian Merz]

Yeah! That's it! Thank you very much, Andy!

You simply have to provide an empty user AND an empty password AND
ora_session_mode of SYSDBA:
-------------------------------
$ cat dummy.pl
#!/usr/bin/perl -wT

# setup environment
BEGIN {
$ENV{ORACLE_HOME} = '/oracle/app/oracle/product/9.2.0';
$ENV{ORACLE_SID} = 'ora920';
}

use strict;
use DBI;

# does'nt work im my environment:
# "ora_session_modes" is not defined in %DBD::Oracle::EXPORT_TAGS
#use DBD::Oracle qw/:ora_session_modes/;

my $mode = 2; # SYSDBA
# $mode = 4; # SYSOPER

my $dbh = DBI->connect( 'dbi:Oracle:','', '',
{ ora_session_mode => $mode,
RaiseError => 1, AutoCommit => 0 } )
|| die "cannot connect to oracle\n $DBI::errstr\n";

print $dbh->selectrow_array('select instance_name from v$instance') . "\n";
print $dbh->selectrow_array('select user from dual') . "\n";

$dbh->disconnect;

__END__
-------------------------------
$ perl -wT dummy.pl
ora920
SYS
-------------------------------

----- Original Message -----
From: "Andy Hassall" <an...@andyh.co.uk>
To: "Christian Merz" <christi...@muenchen.de>; <dbi-...@perl.org>
Sent: Thursday, June 05, 2003 11:35 PM
Subject: Re: Oracle connect internal/OS authentification

[Samuel Gold]

Very interesting.....

-----Original Message-----
From: Alan Burlison [mailto:Alan.B...@sun.com]

Sent: Thursday, June 05, 2003 8:16 PM
To: Andy Hassall
Cc: Christian Merz; dbi-...@perl.org
Subject: Re: Oracle connect internal/OS authentification

[Tim Bunce]

So do you all think that setting ora_session_mode to ORA_SYSDBA should force
the password to be non-null?

Tim.

[Samuel Gold]

I would like to see if you set ora_session_mode to SYSDBA and don't supply a
username or password that it defaults to SYS and some non-null password.
That's my opinion.

Thanks,
Sam Gold

-----Original Message-----
From: Tim Bunce [mailto:Tim....@pobox.com]
Sent: Friday, June 06, 2003 9:16 AM
To: Gold, Samuel (Contractor)

[Alan Burlison]

> I would like to see if you set ora_session_mode to SYSDBA and don't supply a
> username or password that it defaults to SYS and some non-null password.
> That's my opinion.

>> So do you all think that setting ora_session_mode to ORA_SYSDBA should force

>> the password to be non-null?
>>
>> Tim.

That isn't necessary. If you set ora_session_mode to ORA_SYSDBA and provide
(undef, undef) as the (username, password) it works fine. If however you
provide a username you have to provide a password as well - the values are
irrelevant, it is just that you need to specify a password if you specify a
username.

--
Alan Burlison
--