Thursday, June 29, 2006
Does Oracle cache the SYSDBA password?
I was just having a play around on one of my test/sandpit databases ("small database on a low power machine that I use for testing out scripts &c, no live data and if the database gets destroyed I just have to wipe out the files and create a new one" type of thing). I'd forgotten the SYSDBA password (for sandpit systems I use the SID as the SYSDBA password so don't bother to write it down, for some reason this time I didn't) but needed it (I'm installing HTMLDB to try it out on that machine) so wanted to change it. I had been getting1:
It's a good thing you can still use OS authentication to log in as SYSDBA, I'd have been really sunk if I hadn't had that.
1I've made it habit to always run SQL*Plus with the /nolog option then use connect so that the password won't appear in the process list if someone does a ps. Making good/safe practice habit can save a lot of trouble. ¶ 7:51 AM
I was just having a play around on one of my test/sandpit databases ("small database on a low power machine that I use for testing out scripts &c, no live data and if the database gets destroyed I just have to wipe out the files and create a new one" type of thing). I'd forgotten the SYSDBA password (for sandpit systems I use the SID as the SYSDBA password so don't bother to write it down, for some reason this time I didn't) but needed it (I'm installing HTMLDB to try it out on that machine) so wanted to change it. I had been getting1:
as you might expect. I used orapwd to change the passwordSQL> conn sys/sbtest@sbtest as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
and thought I would be good to go. So, I try logging in as sys from another window:$ cp orapwsbtest orapwsbtest.20060629.bak
$ orapwd file=orapwsbtest password=sbtest entries=16
OPW-00005: File with same name exists - please delete or rename
$ rm orapwsbtest
$ orapwd file=orapwsbtest password=sbtest entries=16
Not so good to go! I looked up the error message:SQL> conn sys/sbtest@sbtest as sysdba
ERROR:
ORA-01031: insufficient privileges
not very useful. I was getting the same error message if I attempted from the Oracle software owner user, a different user in the the dba UNIX group on the same machine or a remote SQL*Plus session on my desktop. Wondering if maybe Oracle was caching the password file, and therefore the SYSDBA password, I logged in as SYSDBA using OS authentication and forced a restart of the database:$ oerr ora 01031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.
Success! The password now worked from the Oracle software owner account, the other account in the dba UNIX group and the remote SQL*Plus session.SQL> conn / as sysdba
Connected.
SQL> startup force
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> conn sys/sbtest@sbtest as sysdba
Connected.
SQL>
It's a good thing you can still use OS authentication to log in as SYSDBA, I'd have been really sunk if I hadn't had that.
1I've made it habit to always run SQL*Plus with the /nolog option then use connect so that the password won't appear in the process list if someone does a ps. Making good/safe practice habit can save a lot of trouble. ¶ 7:51 AM
Comments:
<< Home
I remember reading somewhere that one shouldn't recreate the orapw file while the database is open. Whether this is a caching mechanism or a way to avoid inconsistencies I don't know.
# posted by 
: Friday, June 30, 2006 2:48:00 PM
: Friday, June 30, 2006 2:48:00 PM
I don't recall ever reading or hearing that you shouldn't recreate the password file with the database up. I'm sure it's never been mentioned on a training course I've been on when they've talked about recreating the password file.
Recently I had to increase the entries in our password files. The only way was to recreate every password file for each db.
See Metalink Note:1029539.6
Unfortunately it's a HOWTO not a WHYTO.
dberg
See Metalink Note:1029539.6
Unfortunately it's a HOWTO not a WHYTO.
dberg
# posted by : Sunday, July 02, 2006 6:58:00 PM
: Sunday, July 02, 2006 6:58:00 PM
dberg,
I read that note, it's pretty much verbatim what the example on orafaq (mentioned in my followup to this entry) says. The difficulty is that it's not explicit that you need to bounce the database for the changed SYSDBA password to take effect. Look at the last two steps:
5. Add, in the init<SID>.ora file, the following:
REMOTE_LOGIN_PASSWORDFILE=exclusive
6. Restart the database for this to take affect.
I read that as saying that the database restart is required for the change to the init.ora file to take affect, it doesn't imply that the restart is needed for the change to the password file to take affect.
I read that note, it's pretty much verbatim what the example on orafaq (mentioned in my followup to this entry) says. The difficulty is that it's not explicit that you need to bounce the database for the changed SYSDBA password to take effect. Look at the last two steps:
5. Add, in the init<SID>.ora file, the following:
REMOTE_LOGIN_PASSWORDFILE=exclusive
6. Restart the database for this to take affect.
I read that as saying that the database restart is required for the change to the init.ora file to take affect, it doesn't imply that the restart is needed for the change to the password file to take affect.
If the remote_login_passwordfile parameter is set to exclusive, you cannot re-create the password file with the database open. If however, you have it set to shared you can.
Post a Comment
# posted by : Friday, January 25, 2008 8:52:00 AM
: Friday, January 25, 2008 8:52:00 AM<< Home
The above is the work and opinions of Stephen Booth as a private individual. © Stephen Booth 2004, 2005 &c.
