~~~~~~~~~~~~~~~
This article describes the different ways you can connect to Oracle as an
administrative user.
It describes the options available to connect as SYSDBA or SYSOPER.
Oracle 8.1 is expected to be the last release to support the 'CONNECT INTERNAL'
syntax : it is therefore advisable to use SYSDBA or SYSOPER privileges instead.
1) Administrative Users
~~~~~~~~~~~~~~~~~~~~~~~
There are two main administrative privileges in Oracle: SYSOPER and SYSDBA
These are special privileges as they allow access to a database instance even
when it is not running and so control of these privileges is totally outside of
the database itself.
SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.
SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously
connected INTERNAL.
2) Password or Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~
Unless a connection to the instance is considered 'secure' then you MUST use a
password to connect with SYSDBA or SYSOPER privilege.
Users can be added to a special 'password' file using either the 'ORAPWD'
utility, or 'GRANT SYSDBA to USER' command.
Such a user can then connect to the instance for administrative purposes using
the syntax:
CONNECT username/password AS SYSDBA
or
CONNECT username/password AS SYSOPER
This is described in more detail in section (5) below.
Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If the connection to the instance is local or 'secure' then it is possible to
use the operating system to determine if a user is allowed SYSDBA or SYSOPER
access.
In this case no password is required.
The syntax to connect using operating system authentication is:
CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER
Oracle determines if you can connect thus:
On Unix: On UNIX the Oracle executable has two group names compiled into it,
one for SYSOPER and one for SYSDBA.
These are known as the OSOPER and OSDBA groups.
Typically these can be set when the Oracle software is installed.
When you issue the command 'CONNECT / AS SYSOPER' Oracle checks if
your Unix logon is a member of the 'OSOPER' group and if so allows you
to connect.
Similarly to connect as SYSDBA your Unix logon should be a member of
the Unix 'OSDBA' group.
The OSDBA groups is the same group as has been historically used to
allow CONNECT INTERNAL.
On NT: On NT the OSOPER and OSDBA groups are hard coded groups thus:
Group Name Oracle uses this as...
~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
ORA_OPER OSOPER group for all instances
ORA_DBA OSDBA group for all instances
or
ORA_sid_OPER OSOPER group for a specific Oracle SID
ORA_sid_DBA OSDBA group for a specific Oracle SID
When you issue a 'CONNECT / AS SYSDBA' , Oracle checks if your NT logon is a
member of the 'ORA_sid_DBA' or 'ORA_DBA' group.
3) OSDBA & OSOPER Groups on Unix
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default to the group 'dba'.
These groups are compiled into the 'oracle' executable and so are the same for
all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:
cd $ORACLE_HOME/rdbms/lib
cat config.[cs]
The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.
If you wish to change the OSDBA or OSOPER groups this file needs to be modified
either directly or using the installer.
Eg: For an OSDBA group of 'mygroup'
If your platform has config.c (this is the case for HP-UX, Compaq Tru64
Unixware and Linux):
Change: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"
If your platform has config.s:
Due to the way different compilers under different architectures generate
assembler code, it's not possible to give a universal rule.
Here are some examples:
Sun SPARC Solaris:
------------------
Change both ocurrences of
.ascii "dba\0"
to
.ascii "mygroup\0"
IBM AIX/Intel Solaris:
----------------------
Change both ocurrences of
.string "dba"
to
.string "mygroup"
To effect any changes to the groups and to be sure you are using the groups
defined in this file relink the Oracle executable.
Be sure to shutdown all databases before relinking:
Eg:
rm config.o
make -f ins_rdbms.mk config.o ioracle
For a group to be accepted by Oracle as the OSDBA or OSOPER group 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'
Note: The commands above are examples and may vary between platforms.
Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER
roles using group names of the form 'ORA_sid_OSDBA'.
This functionality has not been implemented on Unix (See Bug 224071)
Important notes about 'CONNECT / AS SYSDBA'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Unix systems a user may be a member of more than one group.
To connect as an administrative user without supplying a password:
- One of the groups of which the user is a member should be either the OSDBA or
OSOPER groups as defined in config.c (config.s on some platforms) and as
linked into the 'oracle' executable.
- The group must be a valid group as defined in /etc/group (or as defined in NIS
by 'ypcat group')
- The users PRIMARY group (Ie: the one shown by the 'id' command) cannot be the
special group 'daemon'.
It is quite common for the 'root' user to be required to have SYSDBA or SYSOPER
privilege.
Unfortunately it is also common for the root users' primary group to be the
group 'daemon' which may prevent it from being allowed to connect without a
password.
There are two ways to tackle this problem:
a) Make the root users PRIMARY group the OSDBA group
OR
b) Where available use the 'newgrp' command to change the users primary group to
the DBA group.
Eg: $ newgrp dbagroup
$ svrmgrl
SVRMGR> connect / as sysdba
This can also be used in shellscripts thus:
:
newgrp dbagroup
This is described in detail in Note 60634.1
5) Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~
Remote connections require the database to be configured to allow remote DBA
operations.
The remote user will have to supply a password in order to connect as either
SYSDBA or SYSOPER.
The only real exception to this is on NT where remote connections may be secure.
Ie: To perform a remote connect as SYSDBA or SYSOPER you must use the syntax
'CONNECT username/password AS SYSDBA'
To allow remote administrative connections you must:
- Set up a password file for the database on the server
- Set up any relevant init.ora parameters
5.1) Setting up a Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The SYSDBA/SYSOPER password protection is controlled by an Oracle 'Password'
file.
The basic concept is that a special file is created to hold the 'SYSDBA' and
'SYSOPER' passwords.
Users with SYSDBA or SYSOPER privilege granted in the password file can be seen
in the view V$PWFILE_USERS.
To create a password file log in as the Oracle software owner and issue the
command:
orapwd file=
using the required password.
The file name is important and should be specified as above.
-WINDOWS PWD+sid
-UNIX orapw+sid
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
5.2) Setting up the Init.Ora file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To enable remote administrative connections set the init.ora parameters thus:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
EXCLUSIVE forces the password file to be tied exclusively to a single instance.
To disable remote administrative connections set REMOTE_LOGIN_PASSWORDFILE=NONE
Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as
SYSDBA or SYSOPER from a remote machine.
Note: Some (old) documentation may indicate SQL*Net needs configuring to connect
from remote machines.
In particular the following are NOT used:
SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are
irrelevant
6) Bugs and Special Notes
~~~~~~~~~~~~~~~~~~~~~~~~~
Common Errors
~~~~~~~~~~~~~
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
Deleting/Changing the 'orapw$SID' File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 remotely and restart the instance
No comments:
Post a Comment