This month we will discuss about operating system (OS)
authentication. This is real useful for DBA especially when you have scripts
monitoring your database which log in periodically, so that you need not hard
code the database password inside the scripts. Oracle provides this (OPS$
accounts) in a very neat way on all UNIX platforms. Let us see what are the
components to operating system authentication.
1. Meaning You will be able to connect to
Oracle database using Operating System authentication, that is, without
supplying an username and password once you have logged on to OS.
2. To which account will I be
connected in the database
To enable operating system authentication, you need to have a
database account created as the same username in the OS, prefixed by a keyword.
This keyword is specified in the init.ora file parameter OS_AUTHENT_PREFIX. By
default the value is OPS$. The value of this parameter is concatenated to the
beginning of every user's operating system account. The prefixed username is
compared with the Oracle usernames in the database when a connection request is
attempted. For example, if you have an OS username "oracle" and you
need to connect to the database using OS authentication, create a database user
by name OPS$ORACLE and grant the necessary privileges to this account. So
whenever you connect to the database using OS authentication after succesfully
logging in as "oracle" OS user, you will be connected to the user
account OPS$ORACLE in the database. To connect to the database using command
line, just specify "/" (no quotes) instead of username/password.
Example:- "sqlplus /". The ORACLE_SID environment variable should be
set to the proper database where you wish to connect.
3. How do I create OPS$ user account
OPS$ user account creation is same as creating a normal user.
Here you have the option of specifying a password or "IDENTIFIED
EXTERNALLY". If you chose the latter, you can connect to this account only
using OS authentication. In Oracle7.3 database, if you create an account same
as OS login, you can connect to this account either using OS authentication or
by specifying the username and password. In Oracle8i and higher, the OS
authentication works only if the account is created with "IDENTIFIED
EXTERNALLY".
4. Can I connect to a database using SQL*Net with OS
authentication
By default, you cannot connect. Setting the init.ora file
parameter REMOTE_OS_AUTHENT=TRUE (at the remote db) will allow authentication
of remote clients with the value of OS_AUTHENT_PREFIX. So, if you have an
account OPS$DBA in the remote database, you will be able to connect using
SQL*Net by specifying "/@REMOTE_DB_NAME" if your current login id is
DBA.
A word of caution from Thomas
Kyte of Oracle Government on using REMOTE_OS_AUTHENT:
You might want to caution that *only people who do not care about
their data* would use this (especially with SYS or SYSTEM or DBA accounts)....
The reason is that with remote_os_authent, all I need to do is plop a machine
down on your network, create a named account on this machine and I'm in.
So root users on all machines in your network can become your dba. Any
joker with a win95 machine can as well.
We use remote_os_authent here in Oracle but the way we set it up
is:
Ø
only low
level user accounts use it.
Ø
we use
the protcol.ora file to restrict inbound IP connects to the database to come
only from specified data center machines.
Ø
you must
run the application from the data center machine if you want to use os authent.
that way, they must trust N data center admins -- not everyone in the
network....
For information on protocol.ora read Understanding SQL*Net
Release 2.3 Part No. A424841. In appendix A they describe the protcol.ora file
and tell how to set up Validnode Verification. This allows you to specify
from which hosts a listener will accept inbound connections from. This
allows to you say "sql*net from x.y.z is OK but not from anywhere
else".