CONNECT INTERNAL functionality

This is an existing Oracle Support note.  This is note #18089.1

 

1) Introduction

~~~~~~~~~~~~~~~

This article describes the database DBA 'CONNECT INTERNAL' functionality.

 

In Oracle7 there are 3 special classes of user with privilege to perform special operations (such as shutdown and startup) on the database:

        - A DBA user (SYSDBA)

        - An OPERATOR user (SYSOPER)

        - Connect INTERNAL privilege

This article discusses 'CONNECT INTERNAL' and the issues that determine if a user has permission to connect internal or not. The details here apply to releases 7.0.16 through to 8.1. If using Oracle 7.1.6 or higher Oracle strongly advises you use the SYSDBA or SYSOPER privileges instead as 'CONNECT INTERNAL' may be removed after Oracle 8.1 .

 

For details of SYSDBA and SYSOPER see <Note: 50507.1>

 

2) Local Connections

~~~~~~~~~~~~~~~~~~~~

To make local 'internal' connections to an Oracle instance a user must either:

 

a) Supply a password OR b) Be a user belonging to a special UNIX 'dba' group.

 

The first of these follows the same rules as defined in Section (3) below so is not discussed in this section. Here we concentrate on the rules for option (b).

 

2.1) DBA Group

~~~~~~~~~~~~~~

The 'DBA' group is chosen at installation time and is usually the group 'dba' by default.

 

The DBA group is compiled into the 'oracle' executable and so it the same for all databases running from a given ORACLE_HOME directory. The actual group being used as this

DBA group can be checked thus:

 

cd $ORACLE_HOME/rdbms/lib

cat config.[cs]

 

The line '#define SS_DBA_GRP "group"' should name the chosen DBA group. If you wish to change the DBA group change the group name shown in this file.

 

Eg: Change: #define SS_DBA_GRP "dba"

to:     #define SS_DBA_GRP "mygroup"

 

To effect any changes to the DBA group and to be sure you are using the group defined in this file relink the Oracle executable as below. Be sure to shutdown all databases

before relinking:

 

Oracle Version < 7.3         

~~~~~~~~~~~~~~~~~~~~         

rm config.o                              

make -f oracle.mk config.o  

make -f oracle.mk ioracle   

 

Oracle Version >= 7.3

~~~~~~~~~~~~~~~~~~~~~

rm config.o

make -f ins_rdbms.mk config.o

make -f ins_rdbms.mk ioracle

 

(Note the above instructions may vary depending on your platform / release)

 

For a DBA group to be accepted by Oracle it must:

 

- Be compiled into the Oracle executable

- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being used)

- It CANNOT be the group called 'daemon'

 

2..2) Local Connect Internal

~~~~~~~~~~~~~~~~~~~~

On UNIX systems a user may be a member of more than one group and should be allowed to connect internal without a password provided:

 

- One of the groups of which they are a member is the DBA group as defined in config.c (config.s on some platforms) and as linked into the 'oracle' executable.

- The DBA group is a valid group as defined in /etc/group (Or as defined in NIS by 'ypcat group')

- The users PRIMARY group (I.e.: the one shown by the 'id' command) is not the

special group 'daemon'.

 

It is quite common for the 'root' user to be required to have 'connect internal' privilege. Unfortunately it is also common for the root users primary group to be the group 'daemon' which precludes it from being allowed to connect internal. There are two ways to tackle this problem:

 

a) Make the root users PRIMARY group the DBA group

or b) Where available use the 'newgrp' command to change the users primary group to the DBA group.

 

Eg:  $ newgrp dbagroup

$ sqldba mode=line

SQLDBA> connect internal

 

This can also be used in shellscripts thus:

:

newgrp dbagroup <<!

# Commands requiring connect internal privilege

# Eg: dbstart

!

 

or c) For systems where 'newgrp' is not available or does not work from scripts you can use 'su' instead. Eg:

:               

su - oracle <<!

# Commands requiring connect internal privilege

!

 

Note: The user you 'su' to should be able to 'connect internal' without a password, for example by having their primary group as DBA.

 

Some Oracle releases have problems with identifying the DBA group when it is not the user’s primary group. If you encounter problems with connect internal and the DBA group is set correctly try making the users primary group the DBA group, or use 'newgrp' as in (b) above.

 

3) Remote Connect Internal

~~~~~~~~~~~~~~~~~~~~~~

Remote connect internal requires the database to be configured to allow remote DBA operations. The remote user will HAVE to supply a password in order to connect internal. This is a tightening in security from Oracle 6 where remote users could connect internal without a password.

 

I.e.: In Oracle7 to perform a remote connect internal you must use the syntax 'CONNECT INTERNAL/PASSWORD'

 

To allow remote internal connections you must:

- Set up a password file for the database on the server

- Set up any relevant init.ora parameters

- Set up any SQL*Net options required

 

3.1) Setting up a Password File

~~~~~~~~~~~~~~~~~~~~~~~

The connect internal password protection is controlled by an Oracle 'Password' file. The exact commands used to set up this file are different for different Oracle releases but the basic concept is that a special file is created to hold the 'connect internal' password.

 

Oracle  Init.Ora Parameters        Command

~~~~~~  ~~~~~~~~~~~~~~~~~~~        ~~~~~~~

7.1.x   remote_login_passwordfile  orapwd file=<fname> password=<pwd>

7.0.x                              orapasswd

   

7.1.x

~~~~~

To create a password file log in as the Oracle software owner and issue the command:

 

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

 

using the required password. The file name is important and should be specified as above. You should create this file when the database is shut down.

 

To change a password:

- shut down the database,

- Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file

- Issue a new orapwd command with a new password

 

7.0.x

~~~~~

To create a password file log in as the Oracle software owner and issue the command:

 

orapasswd

 

This will prompt for the ORACLE_HOME, the ORACLE_SID and the 'connect internal' password. Enter the required values.

To change a password re-run the 'orapasswd' tool.

 

3.2) Setting up the Init.Ora file

~~~~~~~~~~~~~~~~~~~~~~

To enable remote internal connections set the init.ora parameters thus:

 

7.1.x

~~~~~

Set REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED. EXCLUSIVE forces the password file to be tied exclusively to a single instance.  To disable remote internal connections set REMOTE_LOGIN_PASSWORDFILE to NONE.

 

7.0.x

~~~~~

No init.ora changes are required for 7.0.x releases.

 

Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect internal from a remote machine.              

 

3.3) SQL*Net Options

~~~~~~~~~~~~~~~~

From 7.0.16 onwards there are no SQL*Net settings that control the ability to connect internal from remote machines. In particular the settings shown below are no longer required:

 

SQL*Net V1:     The dbaon / dbaoff options are irrelevant

 

SQL*Net V2:     The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are irrelevant

 

4) Bugs and Special Notes

~~~~~~~~~~~~~~~~~~~~~~~~~

Common Errors

~~~~~~~~~~~

ORA-09910: Unable to find ORACLE password file entry for user.

On 7.0.x the $ORACLE_HOME/dbs/orapasswd file cannot be accessed.

       

ORA-01031: insufficient privileges 

Connect Internal has been issued with no password.

For local connections the user is NOT in the DBA group as compiled into the 'oracle' executable.

For remote connections you must always supply a password.

 

This error can also occur after a successful connect internal/password if there REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.

     

ORA-01017: invalid username/password; logon denied

This is a fairly general error that indicates one of the following:

- REMOTE_LOGIN_PASSWORDFILE is set to NONE

- The password file does not exist

- The password supplied does not match the one in the password file

- The password file been changed since the instance was started

 

7.1.x - Deleting/Changing the 'orapw$SID' File                <Bug:262100>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you delete the Oracle password file while the instance is running you will NOT be able to connect internal from remote machines, even if you re-create the file. You must:

- Shutdown the instance (using a local connection)

- Create the new password file

- You can now connect internal remotely and restart the instance

 

7.1.3 - REMOTE_LOGIN_PASSWORDFILE ignored                     <Bug:209449>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If a password file exists at all then you can perform remote CONNECT INTERNAL operations even if the value of REMOTE_LOGIN_PASSWORDFILE is set to NONE (the default).

 

The workaround is to delete the password file to prevent remote internal connections.

 

7.1,7.2 - Security Loophole                    <Bug:321727>

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Be aware that there is a potential security loophole in the orapwd implementation in Oracle 7.1 and 7.2. Details of this are NOT disclosed so as not to compromise existing users of the option. The fix is in Oracle 7.3.2.