Oracle : Changed Password

[malay boy]

Senario :

Database :Oracle Database 8.1.7
o/S : AIX 4
Client :SQL Plus 8.0.5 running on window NT.

Problem :

A user connected to database using SQL Plus 8.0.5 and want to change password.

Below are how user changed password :

SQL> password
Changing password for AHFELDMA
Old password: **********
Operating System error occurred
Password unchanged
SQL>

uhm!! Got error.

I do some testing I installed a client SQLPLUS 8.1.7 and do the same:


SQL> password
Changing password for xxxx
SQL>

Amazingly no problem.

I so further testing.I installed SQLPLUS 3.3 . And try to changed my password.Even worst,I got error


UNKNOWN COMMAND...

Question 1)
use password command to change user password?Thisis weird...I never use this , normally we use " alter user xxx identified by "xxx" ".Using password this is something new.Is it a default function from oracle 8.1.7 OR some smart guy(who already left the company) create a procedure(or whatever ) to enable user to changed password using password.

Question 2)
What???? What is the different using SQLPLUS 8.1.7 or 8.0.5 ... I'm really confuse...Being a good DBA,I strongly told my user that SQL PLUS client nothing to do with this problem.Now obviously I'm wrong...heheheh ashamed...

Shed some light please.




still learning,
mB

[Steven E. Protter]

As far as I know you can not change the OS password from sqlplus. An OS error such as the database connection for the sqlplus session most likely caused that error.

Your questions.

1) The prompt could have been caused by the expiration of the ORACLE password, not the OS password. The OS command to change passwords is passwd, not password. It is possible that the user shelled out of sqlplus and typed passwd. I know its possible to shell out to the OS in sqlplus.

2) sqlplus 8.0.5 is fine for connecting. It would be better if you had a standardized client distributed. This issue was not caused by the OS and I doubt the sqlplus client was involved, unless thats why the database session terminated.

There should be something in the alert logs in this case.

[Brian Crabtree]

Ok, I tested this on a few systems. For me an 8.0.6 client connecting to a 8.1.7 database (both HP-UX) works fine, and the "password" function works normally.

There is a note in Metalink that says that 8.0.5 and 8.1.5 have bug associated with the password command.

I saw your note in Metalink. I think that the Doc ID you should look at is 85228.999. This appears to run parallel to your problem. From what I can tell, the "password" function is used for when the password security has been enabled with the utlpwdmg.sql script. Otherwise, as far as I can tell, the users can use "alter user" to change passwords.

A simple fix to get around this is to do one of two things to the script. You can change the "old_password varchar2" to "old_password varchar2 default ''" or set the "IS NULL" that is talked about in the Doc ID above. This will force the users to use password to change it, although it will disallow clients on 8.0.5 and 8.1.5 from working due to the bugs associated with those versions. (This was supposed to be fixed in 8.0.6, 8.1.6, 8.1.7, and 9i).

Hope this helps. Post back if you have any questions.

Thanks,

Brian

[Brian Crabtree]

Ignore the simple fix. I tested it on 8.0.6, and it did not work.

The next simple fix (tested, I swear) is:

change "IF old_password = '' THEN" to
"IF old_password = '' or old_password is null THEN"

This will make it so that "alter user" cannot be used at all (DBA's as well unless you want to code a workaround into the script (not hard)). All users will be forced to use the password function (note the warning about 8.0.5 above).

Sorry for the mixup.

Brian

[malay boy]

SEP-Thanks for the posting.The password is for Oracle not for Unix.And yes Unix is using passwd.APpreciate your reply.

Brian-I try to look for the Doc ID 85228.999.
Is it in Metalink or ITResourcecenter.Appreciate if you could give me the link.

regards
mB

[Yogeeraj_1]

hi mb,

1. 85228.999 is a Metalink document.
[Brian, please allow me answer this question]


2. You cannot run a procedure "directly" using SQLPLUS. You need to "PLSQL" it or use "exec"

e.g.

begin
password;
end;

or
exec password



3. The may be effectively some changes with the different releases of SQLPLUS.
If you see the SQLPLUS Quick Reference Guide, you will find that password is a SQLPLUS reserved word.

SYNTAX:

PASSW[ORD] [username]
Allows you to change a password without echoing the password on the terminal.


hope this helps!
Regards
Yogeeraj

[T G Manikandan]

This is a bug with 8.0.5.

check the attachment

[malay boy]

Sifu Yoogeraj,
I do a search on metalink using 85228.999 .But no result.APpreciate if you could sent me the link.

regards
mB

[Yogeeraj_1 · Accepted solution]

ok,

let me help you find it rather than foolishly sending you this note!

Steps:
1. login to metalink.oracle.com
2. click on the "advanced"
3. Keywords, enter: "85228.999"
4. Click on the radio button "Doc ID"
5. Click "Search"

You should get the document!

hope this helps!

Please revert if you still don't get it. (i will have to post it then)

cheers
Yogeeraj

[malay boy]

10 point for cifu yogeraj...

Thanks...Closing the thread...

regards
mB

p/s: cifu mean guru in Chinese...