Adding Users to Oracle Passwordfile (Oracle Passwordfile Authentication)

In a Unix environment you can use the following command to create a password file and to add user SYS and it’s password to the file:

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret entries=20

On Windows you can use the same command. The only differences are:

• The password file should be created in %ORACLE_HOME%\database.
• The password file’s name should be pwd%oracle_sid%.ora.

As soon as the password file is created you can add privileged users by granting the roles SYSDBA or SYSOPER to those users:

SQL> create user test identified by test; 
SQL> grant sysdba to test;

SQL> create user test2 identified by test2;
SQL> grant sysoper to test2;

Verify

1. Check if user is added to the password file:

SQL> select * from V$pwfile_users;

USERNAME                       SYSDB      SYSOP
------------------------------ -------    --------
INTERNAL                       TRUE       TRUE
SYS                            TRUE       TRUE
test                           TRUE       FALSE
test2                          FALSE      TRUE

2. Check if the user can connect as sysdba:

SQL> connect test/secret@ as sysdba
ORA-01031: insufficient privileges

SQL> connect test/test as sysdba
Connected.

Conclusion

When OS authentication is enabled (the os user is a member of the dba group) the following commands executed on the RDBMS Server host machine will work:

SQL> connect / as sysdba
Connected.
SQL> connect who_am_i/does_not_matter as sysdba
Connected.

This is because OS authentication takes precedence over passwordfile authentication.