cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle : Changed Password

SOLVED
Go to solution
malay boy
Trusted Contributor

Oracle : Changed Password

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

There are three person in my team-Me ,myself and I.
9 REPLIES
Steven E. Protter
Exalted Contributor

Re: Oracle : Changed Password

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.
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Brian Crabtree
Honored Contributor

Re: Oracle : Changed Password

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
Honored Contributor

Re: Oracle : Changed Password

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
Trusted Contributor

Re: Oracle : Changed Password

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
There are three person in my team-Me ,myself and I.
Yogeeraj_1
Honored Contributor

Re: Oracle : Changed Password

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
T G Manikandan
Honored Contributor

Re: Oracle : Changed Password

This is a bug with 8.0.5.

check the attachment
malay boy
Trusted Contributor

Re: Oracle : Changed Password

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

regards
mB
There are three person in my team-Me ,myself and I.
Yogeeraj_1
Honored Contributor
Solution

Re: Oracle : Changed Password

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
malay boy
Trusted Contributor

Re: Oracle : Changed Password

10 point for cifu yogeraj...

Thanks...Closing the thread...

regards
mB

p/s: cifu mean guru in Chinese...
There are three person in my team-Me ,myself and I.