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.

About these ads

7 Comments »

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

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

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

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

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

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: