Another SYSDBA password entry

Simon Kelsey commeted on one of my earlier entries on this subject that x$kzsrt may be involved:

SQL> select * from v$fixed_view_definition where view_name='GV$PWFILE_USERS';

VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
GV$PWFILE_USERS
select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE
','FALSE') from x$kzsrt where valid=1  and username != 'INTERNAL'

I then got to thinking, does the 'alter user...;' just alter the copy of the password in memory or does it update the password file as well?

I constructed a little test case. First I opened two sessions on the server as the Oracle software owner user (i.e. 'oracle'). In the first I connected to the database as 'sys' using the existing SYSDBA password and then changed the password using the 'alter user...;' method:

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 6 14:30:23 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn sys/sbtest@sbtest as sysdba
Connected.
SQL> alter user sys identified by foobar;

User altered.

In the second session I attempted to connect to the database with the old password and with the new:

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 6 14:31:21 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn sys/sbtest@sbtest as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn sys/foobar@sbtest as sysdba 
Connected.

This confirms that 'alter user...;' does indeed change the password. I then quit SQL*Plus in that session and back in the first session shutdown and attempted to restart the database:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
ORA-01017: invalid username/password; logon denied

Note that the instance appears to be started (i.e. the database is in no mount state) before the credentials of the user are checked.

I then attempted to shutdown the instance and, when that failed, I tried to start it up (to see if I'd get a insufficient priviledge error or a cannot start already-running ORACLE error):

ORA-01031: insufficient privileges
SQL> startup
ORA-01031: insufficient privileges

. In the second session I recreated the password file with the new password:

$ cp orapwsbtest orapwsbtest.20060706.bak
$ rm orapwsbtest
$ orapwd file=orapwsbtest password=foobar entries=16

and back in the first session tried to connect with the new password and start up the database:

SQL> conn sys/foobar@sbtest as sysdba
Connected.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Note that this time I get the "cannot start already-running ORACLE" error, demonstrating that the instance is at least partly up. I shutdown the instrance then restarted it and changed the SYSDBA password back to the original value using the 'alter user...;' method:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  555451816 bytes
Fixed Size                   730536 bytes
Variable Size             285212672 bytes
Database Buffers          268435456 bytes
Redo Buffers                1073152 bytes
Database mounted.
Database opened.
SQL> alter user sys identified by sbtest;

User altered.

I then bounced the instance again (using shutdown abort to avoid any possibility of a normal shutdown updating the password file, reconnecting with the changed password to start up). I then went to the second session and tried to connect with the old password, this failed but connecting with the new password worked.

From this it does look like changing the sys password with 'alter user...;' does indeed update the password file.