02 November, 2011

An ALTER USER to change password updates the timestamp of the password file

Apparently, upto 11.2.0.1, the password file is opened and verified and then closed when an ALTER USER ... IDENTIFIED BY ... is issued for any database account.
EDIT :  According to Support Document 312093.1 this behavior is present till 11.2.0.4

Here's a demo :
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Jun 27 23:53 orapworcl
[oracle@localhost dbs]$ date
Wed Nov  2 23:12:27 SGT 2011
[oracle@localhost dbs]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 2 23:12:47 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user TEST_A identified by TEST_A;

User created.

SQL> !date
Wed Nov  2 23:13:04 SGT 2011

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Jun 27 23:53 orapworcl

SQL> alter user TEST_A identified by TEST_A_PWD;

User altered.

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl

SQL>

Thus, a CREATE USER did not change the timestamp of the password file. But an ALTER USER ... IDENTIFIED BY did change the timestamp. However, the file doesn't really get updated because this user (TEST_A) is not a SYSDBA or SYSOPER user.
SQL> !date
Wed Nov  2 23:16:59 SGT 2011

SQL> !cp -p orapworcl orapworcl.keep

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl.keep

SQL> alter user TEST_A identified by TEST_A_NEW;

User altered.

SQL> !ls -l orapw*
-rw-r----- 1 oracle oracle 1536 Nov  2 23:17 orapworcl
-rw-r----- 1 oracle oracle 1536 Nov  2 23:13 orapworcl.keep

SQL> !diff orapworcl  orapworcl.keep

SQL>

Thus, although the timestamp of the password file did get updated, there was no real change to the file.

See Oracle Support article "Timestamp on ORAPWD File Updated When Users' Password Changed [ID 312093.1]" for a description.

.
.
.

No comments: