Till I read this oracle-l thread, I was thinking pasword file is only updated/touched when sys user password is changed however, it is not the only case. As you can see below it is updated/touched whenever a user password is changed, interestingly password file is not updated/touched for new user creations. My question is why oracle updates/touches the file for regular user passwords and why it doesn’t updates/touches for user creation ? If anybody knows can you please leave a comment.
SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
Volume in drive C is System
Volume Serial Number is 303E-6F73
Directory of C:\oracle\product\product\11.1.0\db_1\database
23/07/2009 00:01 2,560 PWDORACOS.ORA
1 File(s) 2,560 bytes
0 Dir(s) 5,201,625,088 bytes free
SQL> create user passw identified by passw;
User created.
SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
Volume in drive C is System
Volume Serial Number is 303E-6F73
Directory of C:\oracle\product\product\11.1.0\db_1\database
23/07/2009 00:01 2,560 PWDORACOS.ORA
1 File(s) 2,560 bytes
0 Dir(s) 5,201,625,088 bytes free
SQL> alter user passw identified by passw;
User altered.
SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
Volume in drive C is System
Volume Serial Number is 303E-6F73
Directory of C:\oracle\product\product\11.1.0\db_1\database
27/07/2009 16:47 2,560 PWDORACOS.ORA
1 File(s) 2,560 bytes
0 Dir(s) 5,201,625,088 bytes free
Footnote:explanation below came via mail from a reader and looks logical.
—-
Anytime a password is changed Oracle opens the password file to check if the user is a dba user and if so it updates the file. If not, obviously oracle does not update it. When oracle opens the file to do the check, oracle does that in read/write mode rather than in read only mode. This is because if the user does happen to be a DBA user it will be more work by closing the file in read mode and reopening in read/write mode and then perform the update.
Coskan's Approach to Oracle 
The user is inserted into the password file with the GRANT command, not when creating the user.
Perhaps the change password algorithm always prefers to read the password file, to check the consistency, rather than query the dictionary to SYSDBA and only then open the password file.
Comment by Roberto — July 28, 2009 @ 8:04 am
Thanks for the information Roberto,
I wonder why Oracle needs to write regular users in password file when there is always possibility to run the database without a password file.
I think the usage of password file is for control SYSDBA privilege (v$pwfile_users)but why the other users without this privilege effects password fle.
Comment by coskan — July 28, 2009 @ 8:33 am
Hi Coskan,
Thats interesting!!! i tested with 10046 trace level got that it updates USER$ table but then i am not able to link to how and why it updates the passwordfile.Even dropping a user updates the file.
Regards,
Anand
Comment by Anand — July 28, 2009 @ 10:44 am
I traced the server process with “strace” utility. Oracle for security matters opens the password file in r/w mode to force the file permissions (on Linux to: 0640=”rw-r—–“).
Comment by Roberto — July 28, 2009 @ 12:21 pm
Thank you for staying with me on this one guys.
It is still interesting that creating a user doesnt do anything or giving any privilege other than (so far) sysdba but updating user password or dropping it does something with the file.
Comment by coskan — July 28, 2009 @ 12:50 pm
The SYSDBA information is only in the password file, and it is published in V$PWFILE_USERS:
SQL> select grantee from dba_sys_privs where privilege = ‘SYSDBA’
union
select GRANTEE from dba_role_privs where GRANTED_ROLE = ‘SYSDBA’; 2 3
no rows selected
SQL> select * from V$PWFILE_USERS;
USERNAME SYSDB SYSOP
—————————— —– —–
SYS TRUE TRUE
PASSW TRUE FALSE
SQL> host chmod 000 $ORACLE_HOME/dbs/orapw
SQL> select * from V$PWFILE_USERS;
no rows selected
In addition:
SQL> host chmod 400 $ORACLE_HOME/dbs/orapw
SQL> select * from V$PWFILE_USERS;
USERNAME SYSDB SYSOP
—————————— —– —–
SYS TRUE TRUE
PASSW TRUE FALSE
SQL> host ls -l $ORACLE_HOME/dbs/orapw
-r——– 1 oracle oinstall 1536 Jul 28 16:20 /oracle/product/10.2.0/db_1/dbs/orapw
SQL> alter user passw identified by passw;
alter user passw identified by passw
*
ERROR at line 1:
ORA-01993: error writing password file ”
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 3
SQL> host chmod 777 $ORACLE_HOME/dbs/orapw
SQL> alter user passw identified by passw;
User altered.
SQL> host ls -l $ORACLE_HOME/dbs/orapw
-rw-r—– 1 oracle oinstall 1536 Jul 28 16:29 /oracle/product/10.2.0/db_1/dbs/orapw
Comment by Roberto — July 28, 2009 @ 2:57 pm
Thank you so much Roberto your comments they were very helpfull especially your finding about that v$pwfile_users depends on the file.
Comment by coskan — July 28, 2009 @ 6:40 pm
Hi,
I faced this situation today of password file getting updated with every password update and was wondering why!
Thanks to your post and the answers that I could understand the behaviour.
Regards,
Amol.
Comment by Amol — October 8, 2015 @ 6:09 am