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;
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.
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.