Coskan’s Approach to Oracle

July 27, 2009

When is my password file gets updated?

Filed under: Basics, Tips — coskan @ 3:51 pm

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.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers