Database Reference
In-Depth Information
To allow the changed value of
OS_AUTHENT_PREFIX
to take effect, the instance must be
restarted. Clearly, the operating system user “ndebes” will not be able to connect as database
user “ndebes” without entering the password “secret”.
$ sqlplus -s /
ERROR:
ORA-01017: invalid username/password; logon denied
When setting the authentication method for the user to operating system authentication,
the string “EXTERNAL” instead of a password hash is stored in
DBA_USERS.PASSWORD
.
SQL> ALTER USER ndebes IDENTIFIED externally;
User altered.
SQL> SELECT password FROM dba_users WHERE username='NDEBES';
PASSWORD
------------------------------
EXTERNAL
Now the operating system user “ndebes” is able to connect without entering the password.
$ id
uid=500(ndebes) gid=100(users) groups=100(users)
$ sqlplus /
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
SQL> CONNECT ndebes/secret as SYSOPER
ERROR:
ORA-01031: insufficient privileges
However, the ability to connect as
SYSOPER
using the password stored in the password file
is lost for the now externally-identified database user. The same applies to the privilege
SYSDBA
.
Lessons Learned
There is an undocumented code path that enables operating system authentication for data-
base users whose user names start with
OPS$
, even when these users are created with password
authentication. This combines the best aspects of the otherwise mutually-exclusive approaches
of operating system and password authentication. To leverage the undocumented feature, the
initialization parameter
OS_AUTHENT_PREFIX
must have the default value
ops$
. The feature may
also be used to set up a single database user with
SYSDBA
or
SYSOPER
privileges who does not
belong to the DBA or OPER operating system groups and who can connect locally without
entering a password. Such a user must only enter the password when connecting over the network
or when needing a session with
SYSDBA
or
SYSOPER
privileges. Separate database users are
required without the undocumented feature or if a nondefault setting of
OS_AUTHENT_PREFIX
is
in effect. If you are dealing with a security-sensitive environment and need to make sure that
an intruder cannot exploit this feature, you should disable it by assigning a nondefault value to
the parameter
OS_AUTHENT_PREFIX
.