Home » SQL & PL/SQL » SQL & PL/SQL » procedure to change password (Oracle DBMS 9i/10g Linux)
procedure to change password [message #425985] Tue, 13 October 2009 09:37 Go to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
Hi,
I create a procedure like
create procedure xyz.ch_pass
(usr in varchar2,
pass in varchar2,
oldpass in varchar2)
begin
execute immediate 'alter user '||usr||' identified by '||pass||' replace '||oldpass;
end;
User xyz has alter user privs. When I grant execute this procedure to different users. They are able to change their and other user password. I want they be able to chang their own password only. How I do that....
Re: procedure to change password [message #425986 is a reply to message #425985] Tue, 13 October 2009 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
test for when usr equals USER
Re: procedure to change password [message #425987 is a reply to message #425986] Tue, 13 October 2009 09:48 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
what u mean....
usr is a variable while user is a part of command i.e. "alter user"..
Re: procedure to change password [message #425989 is a reply to message #425985] Tue, 13 October 2009 09:52 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
user is also a keyword that gets you the current user.
Re: procedure to change password [message #425990 is a reply to message #425987] Tue, 13 October 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
SQL> select user from dual;

USER
------------------------------
DBADMIN



USER is a function

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


Re: procedure to change password [message #425991 is a reply to message #425989] Tue, 13 October 2009 10:00 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
u mean the command should be
get two values password and old passowd only.
and use this command.
execute immediate 'aLter user '||user|| ' identified by ........
BUT MY question is procedure runs on behafe of the procedure owner which have alter user privs. It can be used to change password for other users as well.
Re: procedure to change password [message #425992 is a reply to message #425985] Tue, 13 October 2009 10:06 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you mean you want certain users to be able to use this procedure to change other users passwords, but everyone else can only use it to change their own password?
Re: procedure to change password [message #425994 is a reply to message #425992] Tue, 13 October 2009 10:35 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
YES.
Re: procedure to change password [message #425995 is a reply to message #425985] Tue, 13 October 2009 10:41 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then create two procedures.
One that can change any users password and one that can only change the current users password. Grant execute on those two procedures to the appropriate users.
Re: procedure to change password [message #425996 is a reply to message #425995] Tue, 13 October 2009 10:45 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
Yes. You are right. Very nice....
pls guide me for writing procedure to change password for current-connected user.....
Re: procedure to change password [message #425999 is a reply to message #425985] Tue, 13 October 2009 10:50 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use user to get the current user.
Re: procedure to change password [message #426000 is a reply to message #425999] Tue, 13 October 2009 10:53 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
U mean

create procedure xyz.ch_pass
(pass in varchar2,oldpass in varchar2)
begin
execute immediate 'alter user '||user||' identified by '||pass||' replace '||oldpass;
end;
Re: procedure to change password [message #426001 is a reply to message #425985] Tue, 13 October 2009 10:58 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
yes
Re: procedure to change password [message #426002 is a reply to message #426000] Tue, 13 October 2009 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A user can always change its own password, why do you want a procedure to do this?

Regards
Michel
Re: procedure to change password [message #426003 is a reply to message #425985] Tue, 13 October 2009 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
smunir362 wrote on Tue, 13 October 2009 16:37
Hi,
I create a procedure like
create procedure xyz.ch_pass
(usr in varchar2,
pass in varchar2,
oldpass in varchar2)
begin
execute immediate 'alter user '||usr||' identified by '||pass||' replace '||oldpass;
end;
User xyz has alter user privs. When I grant execute this procedure to different users. They are able to change their and other user password. I want they be able to chang their own password only. How I do that....

With this procedure I can do what I want in your database.
Use dbms_assert package (or create your own) to verify the parameters.
Search for "sql injection" on Google.

Regards
Michel

Re: procedure to change password [message #426004 is a reply to message #426001] Tue, 13 October 2009 11:04 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
One thing more please.
I have username like 1234/4321 OR abcd/dcba etc..
Now
When I use
execute immediate 'alter user '||user||' identified by '||pass;
it works for character user i.e. abcd and not worked for 1234 user.

When I use
execute immediate 'alter user '||'"'||user||'"'||' identified by '||'"'||pass'"';
it works for numaric users i.e. 1234 and not worked for character user i.e. abcd.

How to do it for both type of users with this procedure.
Re: procedure to change password [message #426005 is a reply to message #426004] Tue, 13 October 2009 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you use " the statement is case sensitive.
Add a check in your procedure to verify the parameters and for instance check the user you give exists in dba_users.

Regards
Michel
Re: procedure to change password [message #426006 is a reply to message #426004] Tue, 13 October 2009 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>How to do it for both type of users with this procedure.
CaSeMaTTerS; even more so with V11
Re: procedure to change password [message #426007 is a reply to message #426006] Tue, 13 October 2009 11:13 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
OK I will check for case senstivity for usernmae.
Pls tell me How to..

Use dbms_assert package (or create your own) to verify the parameters
Re: procedure to change password [message #426009 is a reply to message #425985] Tue, 13 October 2009 11:25 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since the user function returns the user in the correct case I'm not really sure what the problem is.
This should work in all cases:
execute immediate 'alter user "'||user||'" identified by "'||pass||'"';


If it doesn't then you're going to have to do some debugging. I.e work out what value user returns (case included) and what the equivalent value in dba_users.

I would point out that if you've been creating users using double quotes it's going to give you other issues. Though I suspect it's a bit late to fix that now if this is a live system.
Re: procedure to change password [message #426010 is a reply to message #426007] Tue, 13 October 2009 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/print_hit_summary?search_string=dbms_assert

Regards
Michel
Re: procedure to change password [message #426154 is a reply to message #426010] Wed, 14 October 2009 03:58 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
Yes,
user return the username which is same as in dba_user. It is ok now I m not facing any problem now regarding this...
Thanks a lot to everybody who help me.
Re: procedure to change password [message #426159 is a reply to message #426004] Wed, 14 October 2009 04:12 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
smunir362 wrote on Tue, 13 October 2009 11:04
One thing more please.
I have username like 1234/4321 OR abcd/dcba etc..
Now
When I use
execute immediate 'alter user '||user||' identified by '||pass;
it works for character user i.e. abcd and not worked for 1234 user.

When I use
execute immediate 'alter user '||'"'||user||'"'||' identified by '||'"'||pass'"';
it works for numaric users i.e. 1234 and not worked for character user i.e. abcd.

How to do it for both type of users with this procedure.



When setting a password: its length must be between 1 and 30 characters, the first character must be one of A-Z or 0-9. Other password characters may be A-Z (a-z), 0-9, underscore (_), dollar ($) or hash (#).
In Oracle 11g passwords are case sensitive, in older versions passwords are not case sensitive.

Re: procedure to change password [message #426160 is a reply to message #426159] Wed, 14 October 2009 04:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Its_me_ved wrote on Wed, 14 October 2009 11:12

When setting a password: its length must be between 1 and 30 characters, the first character must be one of A-Z or 0-9. Other password characters may be A-Z (a-z), 0-9, underscore (_), dollar ($) or hash (#).
In Oracle 11g passwords are case sensitive, in older versions passwords are not case sensitive.



Not sure where you got your information from, but in my 10g XE version username nor password can start with a digit as it seems:
SQL> create user 1234 identified by 1234;
create user 1234 identified by 1234
            *
ERROR at line 1:
ORA-01935: missing user or role name


SQL> create user abcd identified by 1234;
create user abcd identified by 1234
                               *
ERROR at line 1:
ORA-00988: missing or invalid password(s)


SQL> create user abcd identified by abcd;

User created.

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Hmm, did some extra testing:
SQL> drop user abcd cascade;

User dropped.

SQL> create user abcd identified by '1234';
create user abcd identified by '1234'
                               *
ERROR at line 1:
ORA-00988: missing or invalid password(s)


SQL> create user abcd identified by "1234";

User created.

SQL> drop user abcd cascade;

User dropped.

SQL> create user abcd identified by "_1234";

User created.

SQL> drop user abcd cascade;

User dropped.

SQL> create user abcd identified by "@_1234";

User created.

[Updated on: Wed, 14 October 2009 04:27]

Report message to a moderator

Re: procedure to change password [message #426164 is a reply to message #426159] Wed, 14 October 2009 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
its length must be between 1 and 30 characters, the first character must be one of A-Z or 0-9. Other password characters may be A-Z (a-z), 0-9, underscore (_), dollar ($) or hash (#).

Apart from the length it is wrong, password can be any character but chr(0) and ".

SQL> grant create session to test identified by "123#àç%ù";

Grant succeeded.

SQL> connect test/"123#àç%ù"
Connected.


Regards
Michel
Re: procedure to change password [message #426170 is a reply to message #426160] Wed, 14 October 2009 04:45 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
My apologies.

What I wanted to mean is that the password can not start with a digit and the reason why I quoted OP's statement.

But copied and pasted wrong info from ss64.com/ora/user_a.html

I looked at the additional info about 11g only and pasted the lines. I should have read the entire statement before posting!

Quote:

In Oracle 11g passwords are case sensitive, in older versions passwords are not case sensitive.


Re: procedure to change password [message #426181 is a reply to message #426170] Wed, 14 October 2009 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What I wanted to mean is that the password can not start with a digit

And I showed it can (in any version).

Regards
Michel
Re: procedure to change password [message #426190 is a reply to message #426181] Wed, 14 October 2009 06:05 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
You are correct.Thanks for pointing out the mistake I have made!
Re: procedure to change password [message #426497 is a reply to message #425985] Fri, 16 October 2009 00:13 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
One thing more.
I have implementd password policy through verification function.
Now
How can I message to user on success "Password changed uccessfully.
On failure Messages are already passed to user which are generated from verify function.
Can I trim messages.
Re: procedure to change password [message #426504 is a reply to message #426497] Fri, 16 October 2009 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can I message to user on success "Password changed uccessfully.

When you do what in which tool?

Regards
Michel
Re: procedure to change password [message #426514 is a reply to message #426504] Fri, 16 October 2009 02:30 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
Tool is application developled in Developer 9i and SQL plus.
Detail is as follows:-
User=1234 Password=12345
1- execute abc.change_password('123','12345');
ORA-28003: Password verfication for the specified fail.
ORA-20004: Password length is less than 4.
ORA-06512: at "abc.change_password" line 5.
ORA-06512: at line 2.

2- execute abc.change_password('1234','12345');
ORA-28003: Password verfication for the specified fail.
ORA-20004: Password same as username.
ORA-06512: at "abc.change_password" line 5.
ORA-06512: at line 2.

I want to display only the line.
ORA-20004: Password same as username.
How can I do it.


Re: procedure to change password [message #426515 is a reply to message #426514] Fri, 16 October 2009 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In sql*plus, you can't - sql*plus shows you the whole error stack.
Re: procedure to change password [message #426817 is a reply to message #426515] Mon, 19 October 2009 23:27 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
OK whole error stach is return.
But on success no information is return i.e. "password changed successfully"
How to do it....
Re: procedure to change password [message #426825 is a reply to message #426817] Tue, 20 October 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use dbms_output but you are not sure the caller willl enable it.
So you can't be sure. No way. Remember PL/SQL is NOT for displaying message.

Regards
Michel
Re: procedure to change password [message #427018 is a reply to message #426825] Tue, 20 October 2009 11:07 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
DBMS_OUTPUT work when We use SQLPlus, TOAD, Worksheet etc.
AND run set serveroutput on;
But I think there is a way for that....
Because when error stack is displayed then seccess message should also be displayed. But I am unable to find that way....
Re: procedure to change password [message #427020 is a reply to message #425985] Tue, 20 October 2009 11:12 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
With those tools dbms_output is your only option.
Otherwise you need an application to call your procedure - a web page written in java for example.
Then you can write code to display a success message.
Re: procedure to change password [message #427022 is a reply to message #427020] Tue, 20 October 2009 11:17 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
how can i write code for oracle developer9i application.
Re: procedure to change password [message #427023 is a reply to message #425985] Tue, 20 October 2009 11:31 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you mean oracle sqldeveloper - oracles equivalent of toad?
Re: procedure to change password [message #427025 is a reply to message #427023] Tue, 20 October 2009 11:37 Go to previous messageGo to next message
smunir362
Messages: 294
Registered: September 2007
Senior Member
NO.
Oralce 9i Developer /IDS9i OR Internet Developer Suit 9i.
AND
Oracle Developer 2000 rel 1.

Both haveing forms exetention .fmp Or .fmx
and having reports extention .rdf Or .rep
Re: procedure to change password [message #427026 is a reply to message #427025] Tue, 20 October 2009 11:42 Go to previous messageGo to previous message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From an application you can return what you want to your user when there is an exception as well as when everything goes fine.

Regards
Michel
Previous Topic: DOS command output in pl/sql
Next Topic: ORA-02291: integrity constraint violated - parent key not found
Goto Forum:
  


Current Time: Sat Oct 01 11:02:34 CDT 2016

Total time taken to generate the page: 0.12260 seconds