Operating System Authentication

In the previous lesson, you created a user account that requires the user to supply a password in order to connect to the database. You also can create an account that relies on the operating system to authenticate the user. The user is accepted as a valid database user by virtue of the fact that he or she was able to log into the computer.

To create an operating system authenticated user, you issue a CREATE USER statement that includes the keywords

IDENTIFIED EXTERNALLY

These replace the IDENTIFIED BY clause where the password is usually set. You also need to give the username a prefix of "OPS$." So if you had an operating system user named COIN_ADMIN, you would create an operating system authenticated account like this:

CREATE USER OPS$COIN_ADMIN IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users
 ...

If you were a user logged into the operating system as COIN_ADMIN, and an externally authenticated database user named OPS$COIN_ADMIN existed, you could connect to the database by supplying a forward-slash instead of a username and password. For example:

sqlplus /

Because a password is not needed, OPS$ accounts are often used in UNIX environments where cron jobs^[1] are used to run database related scripts. They free you from having to write database passwords into UNIX shell scripts, thus enhancing security.

The OS_AUTHENT_PREFIX initialization parameter controls the prefix used for operating system authentication. You set this parameter in the database initialization parameter file. The default value, if the parameter is not set in the parameter file, is "OPS$". You can specify OS_AUTHENT_PREFIX="" if you do not want a prefix, or you can specify any other prefix that you like.

[1]cron jobs: Unattended batch jobs that are scheduled with the Unix cron utility.