Why can hr create an Oracle DB session though not using PWFILE


I have created a database. HR schema has been enabled.

connect / as sysdba is known as operating system authentication.

connect hr/password is not operating system authentication right ? If yes, I guess that authentication is possible only with the oracle password file located at ORACLE_HOME/database for Windows.

I have queried V$PWFILE_USERS but it only showed me sys… Other users (such as hr, system, …) are not in that view and I guess not in the password file.

Why ? What type of authentication is used by connect hr/password ? Athentication by OS o by Password ?

Best Answer

Only user having sysdba and sysoper privilege are connected to database using password file authentication. Other users such as HR are authenticated using data dictionary.

Password file is required for SYSDBA and SYSOPER privileged users to make remote connection. As I said earlier other users are authenticated using data dictionary, password file is not required for them to make remote connection.

The password is stored in encrypted form user undocumented table called USER$.

SQL> select password from user$ where name='HR';