Oracle Windows External User Authentication Oracle Database Tips by Donald Burleson

Oracle Windows uses external authentication (ops$) such that the Operating System user ID manages the authentication.  Once logged-on to the OS, you can enter Oracle directly without additional authentication.

When using external users, SQL*Net bypasses all operating system connections when it connects to a database. All user accounts that are identified externally (that is, without an Oracle password) will not be allowed in SQL*Net transactions unless the init.ora parameter is changed. The "identified externally" clause (ops$) in Oracle version 6 allowed the operating system to manage passwords, but because SQL*Net bypasses the operating system, impostor accounts could be created from other platforms, thereby bypassing security. Consequently, Oracle recommends that "identified externally" accounts be forbidden for distributed connections

Creating External users in Oracle

For example, consider the following user definition:

CREATE USER OPS$SCOTT IDENTIFIED BY TIGER;

Assuming that Scott has logged onto the operating system, Scott could enter SQL*Plus with or without a password:

sqlplus /
sqlplus scott/tiger

You can also create the user with the "identified externally" clause:

CREATE USER OPS$SCOTT IDENTIFIED EXTERNALLY;

Create Oracle External users in Windows

External users are easy to create in Linux because you need only create the user in /etc/passwd.  It's a bit trickier in Windows:

1. Create the Windows user:

start --> settings --> control panel --> administrative tools --> computer management --> user

2. Add the new user to the Oracle group:

start --> settings --> control panel --> administrative tools --> computer management --> groups

3. Add user to OS Database Administrator:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS database administrator 

4. Add user to OS Database Operators:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS database operators

5. Add user to OS Administrator:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS administrator

6. Add user to OS Operators:

start --> programs --> oracle home --> configuration and migration tools --> administration for windows NT --> OS operators

7. Add os_authent_prefix=OPS$ to your initialization parameters (pfile or spfile) and bounce Oracle database, if necessary.