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;
Note: When this user is added, the user’s password from the data dictionary is added to the password file.
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.
Note: This must be tested with an os user that is not a member of the dba group to make sure that OS authentication will not be used. Also, you can connect through the listener.
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.