Databases Reference
In-Depth Information
On UNIX systems, membership in the DBA group is sufficient to connect without a pass-
word. With default settings (
REMOTE_OS_AUTHENT=FALSE
), connecting as
SYSDBA
without providing
a password is only possible with the bequeath adapter. This default behavior should not be
changed, since setting
REMOTE_OS_AUTHENT=TRUE
is a security hazard.
As before, it is good practice to verify that
CONNECT
/
AS
SYSDBA
works in SQL*Plus, before
attempting the same with Perl DBI code such as this:
use DBI;
use DBD::Oracle qw(:ora_session_modes); # imports ORA_SYSDBA and ORA_SYSOPER
my $dbh = DBI->connect("DBI:Oracle:", "/", undef, {ora_session_mode => ORA_SYSDBA})
or die "Connect failed: $DBI::errstr";
Note that the preceding example uses both
SYSDBA
privileges and operating system
authentication. The latter is characterized by passing
"/"
as
$user
and
undef
(i.e., no password)
as
$auth
.
Let's take a look at how a non-privileged user might connect with operating system
authentication. This is useful for running batch jobs without embedding a password in scripts
or passing a password on the command line, which might be eavesdropped by looking at the
process list with the UNIX command
ps
. Let's say we want to permit the UNIX user
oracle
to
connect without a password. The database user name required for that purpose depends on
the setting of the initialization parameter
OS_AUTHENT_PREFIX
. In the following example, the
default value
ops$
is set:
SQL> SHOW PARAMETER os_authent_prefix
NAME TYPE VALUE
----------------- ----------- --------------
os_authent_prefix string ops$
Next, we create a database user by using
ops$
as a prefix for the UNIX user name
oracle
.
SQL> CREATE USER ops$oracle IDENTIFIED EXTERNALLY;
SQL> GRANT CONNECT TO ops$oracle;
To test connecting as the new database user, we must be logged in as the UNIX user
oracle
.
$ id
uid=503(oracle) gid=504(oinstall) groups=504(oinstall),505(dba)
$ sqlplus /
Connected.
The following Perl program
os.pl
uses operating system authentication and retrieves the
database user name by executing
SELECT
user
FROM
dual
:
#!/usr/bin/env perl
use DBI;
my $dbh=DBI->connect("dbi:Oracle:", "/", undef) or die "Failed to connect.\n";
my $sth=$dbh->prepare("SELECT user FROM dual");
$sth->execute;
