Database Reference
In-Depth Information
$ sqlplus "ndebes/
secret@ten_tcp.world AS
SYSDBA"
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
The connect command fails, after the
SYSDBA
privilege has been revoked from user NDEBES.
SQL> SHOW USER
USER is "SYS"
SQL> REVOKE SYSDBA FROM ndebes;
Revoke succeeded.
SQL> CONNECT ndebes/
secret@ten_tcp.world AS
SYSDBA
ERROR:
ORA-01031: insufficient privileges
Now we may test connecting in the same way with Perl DBI. So far, I have not addressed
the parameter
\%attr
of the connect method. The backslash (
\
) indicates that the method
expects a reference, while the percent sign (
%
) indicates that a Perl hash is expected. Special
constants defined in the database driver module DBD::Oracle have to be passed as part of
\%attr
to make connecting as
SYSDBA
or
SYSOPER
possible. Thus, we can no longer rely on the
DBI to automatically load the driver module DBD::Oracle for us. Instead, we must explicitly
load the module with the command
use
DBD::Oracle
and request that the constants
ORA_SYSDBA
and
ORA_SYSOPER
be loaded into the Perl symbol table. Here's the Perl code that connects in the
same way as was done previously using SQL*Plus:
use DBI;
use DBD::Oracle qw(:ora_session_modes); # imports ORA_SYSDBA and ORA_SYSOPER
my $dbh = DBI->connect("DBI:Oracle:", "ndebes", "secret",
{ora_session_mode => ORA_SYSDBA})
or die "Connect failed: $DBI::errstr";
Make sure the
SYSDBA
privilege is granted and the other requirements are met before
testing the Perl code.
Connecting with Operating System Authentication
On UNIX systems, the DBA group name commonly used to assign
SYSDBA
privilege is “dba”,
while the suggested group name for the OPER group and the
SYSOPER
privilege is “oper”. These
UNIX group names are merely suggestions. When installing with the Oracle Universal Installer
(OUI), other group names may be chosen.
On Windows, the DBA group name for the
SYSDBA
privilege is always “ORA_DBA” and the
OPER group name is “ORA_OPER”. NTS (NT Security) must be enabled as an authentication
service in
sqlnet.ora
for operating system authentication to work. This is done with the following
line in
sqlnet.ora
:
SQLNET.AUTHENTICATION_SERVICES = (NTS) # required for connect / as sysdba
