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.

 

What is Oracle Application Express?

Oracle Application Express or APEX as it is famously known is a rapid application development for the web closely coupled with Oracle's flagship product, its database. It was earlier known as HTML DB.

It's key features are:
Reporting
Forms
Charting
PDF Printing
Web Services
Spreadsheet Upload
Session State Management
User Interface Themes
Flow Control & Navigation
Conditionality on all Components
External Interfaces & Extensibility
Declarative BLOB Support
Optional Runtime-Only Installation
Security
SQL Workshop Tools
Supporting Objects Utility
Performance
Packaged Applications
Hosted Development


For more information read through What is Apex

Converting Forms using APEX-By David Peake

Modernize Oracle Forms applications with Oracle Application Express.

Oracle Forms has been around since before the days of client/server computing, and numerous Oracle Forms applications have been faithfully meeting business requirements for many years.

Converting Oracle Forms applications to use another development tool is a labor-intensive, time-consuming process, and recent rumors that Oracle will soon drop support for Oracle Forms are unfounded. With these things in mind, why would anyone want to convert their Oracle Forms applications to use another development tool?

Typically, Oracle Forms modernization projects convert legacy applications to use the latest Oracle Database version and enable developers to satisfy user demands for greater user interactivity and Web 2.0 capabilities. A modernization option for Oracle Forms applications that will deliver on both of these objectives is Oracle Application Express.

Oracle Application Express renders applications using HTML, and it has built-in Web 2.0 functionality such as interactive reports, Flash charts, and the ability to extend user interactivity by use of JavaScript and Asynchronous JavaScript and XML (Ajax). In addition, the rapid application development environment offered by Oracle Application Express is similar to that found in Oracle Forms, utilizing a declarative framework together with SQL and PL/SQL to define processes and enter business logic.

Understanding the Conversion Process

The capabilities for converting from Oracle Forms to Oracle Application Express in Oracle Application Express 3.2 generate an initial application design that you then enhance and expand to fully replicate the original application. The conversion maps blocks to pages and various item types, but it does not convert business logic stored in Oracle Forms triggers, program units, or libraries. After you generate the initial design, you can take full advantage of the rapid application development capabilities in Oracle Application Express to enhance the generated pages. Figure 1 describes the process flow of an application migration from Oracle Forms to Oracle Application Express.

.

.

Read the full article here:Oracle Magazine

Conclusion

Oracle Forms conversion in Oracle Application Express is not designed to fully replicate an Oracle Forms application. The conversion process does, however, offer significant benefit by generating user interface components and providing project management tracking capabilities.

The effort and resources required for each Oracle Forms conversion project are largely dependent on the size, complexity, and business logic implementation within the Oracle Forms application. I strongly recommend that you undertake a prototype or a proof of concept to properly plan each Oracle Forms conversion project before you begin.


David Peake is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1993.

Wipro BPO partnered with Oracle

BANGALORE: Wipro Technologies said on Monday that its Business Process Outsourcing division, Wipro BPO has partnered with Oracle for 'best-of-breed HR platform solutions.'


Wipro has also selected The Hackett Group, a global strategic advisory firm, to provide empirical data, best practices and world-class performance insights on the development of its innovative bundled solution platform, it said in a release here.

The solution simPlify, allows employers to reduce and control cost as it provides an opportunity to centralize and standardize processes while eliminating duplicative management structures.

The solution has the ability to leverage a many-to-one technology capability, while maximizing service quality and HR customer satisfaction.

 

From News: Economic Times