Database Reference
In-Depth Information
the TCP/IP protocol adapter. Creating
OPS$
users with password authentication allows the
convenience of omitting the user name and password when connecting locally using the Oracle
Net bequeath adapter, while being able to connect over a network using password authentication.
OPS$ Database Users and Password Authentication
Operating system authentication is intended for local connections. The
Oracle Database SQL
Reference 10g Release 2
manual states the following on externally-identified users:
EXTERNALLY Clause
Specify EXTERNALLY to create an external user. Such a user must be authenticated by
an external service, such as an operating system or a third-party service. In this case,
Oracle Database relies on authentication by the operating system or third-party service
to ensure that a specific external user has access to a specific database user.
In the same way that a user who belongs to the DBA group (usually the UNIX group dba)
can connect with
SYSDBA
privileges without entering a password using
CONNECT
/
AS
SYSDBA
, an
externally-identified user can connect using
CONNECT
/
. When verifying credentials for an exter-
nally-identified user, the value of the ORACLE initialization parameter
OS_AUTHENT_PREFIX
is
prepended to the operating system user name. If the resulting user name exists in the data
dictionary and
DBA_USERS.PASSWORD=EXTERNAL
for this user, then the user may connect without
entering a password. The syntax for creating an externally-identified user is as follows:
CREATE USER <os_authent_prefix><os_user_name> IDENTIFIED EXTERNALLY;
It is undocumented that operating system authentication also works for users created with
password authentication as long as
OS_AUTHENT_PREFIX
is left at its default setting of
ops$
. That
is, users created with the syntax
CREATE
USER
ops$
os_user_name
IDENTIFIED
BY
password
may
connect locally without entering a password as long as
OS_AUTHENT_PREFIX=ops$
. In a way, this
approach combines the best of both worlds. The need to enter passwords for interactive data-
base sessions as well as storing passwords for batch jobs running locally is dispelled and the
same user name may be used to connect over the network.
Case Study
The environment for this case study is a UNIX system, where the DBA group name is “dba”, the
OPER group name is “oper”, and the ORACLE software owner group is “oinstall”. Furthermore,
a password file is used. In a moment you will see how a user who is not a member of any of the
aforementioned three special groups may be granted the
SYSOPER
privilege, allowing him to
start and stop an instance, while not being able to change parameters or to modify the ORACLE
software installation. This is an additional option that may be implemented with the undocu-
mented approach discussed in the previous section.
First of all, we verify that the parameter
OS_AUTHENT_PREFIX
has the default value
ops$
.
SQL> SHOW PARAMETER os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- -----
os_authent_prefix string ops$
