DB Connection Using Operating System Authentication

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:

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".

The utilities and unix scripts shown elsewhere on Biju's Oracle DBA Page use OS authentication to connect to the database.