topic Re: Oracle : Changed Password in Operating System - HP-UX

Oracle : Changed Password

malay boy — Wed, 21 May 2003 23:24:59 GMT

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

Re: Oracle : Changed Password

Steven E. Protter — Thu, 22 May 2003 00:25:03 GMT

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.

Re: Oracle : Changed Password

Brian Crabtree — Thu, 22 May 2003 00:28:47 GMT

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

Re: Oracle : Changed Password

Brian Crabtree — Thu, 22 May 2003 00:38:59 GMT

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

Re: Oracle : Changed Password

malay boy — Thu, 22 May 2003 00:42:37 GMT

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

Re: Oracle : Changed Password

Yogeeraj_1 — Thu, 22 May 2003 03:13:16 GMT

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

Re: Oracle : Changed Password

T G Manikandan — Thu, 22 May 2003 03:32:05 GMT

This is a bug with 8.0.5.

check the attachment

Re: Oracle : Changed Password

malay boy — Thu, 22 May 2003 04:10:16 GMT

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

regards
mB

Re: Oracle : Changed Password

Yogeeraj_1 — Thu, 22 May 2003 04:19:30 GMT

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

Re: Oracle : Changed Password

malay boy — Thu, 22 May 2003 04:26:18 GMT

10 point for cifu yogeraj...

Thanks...Closing the thread...

regards
mB

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