procedure to change password [message #425985] |
Tue, 13 October 2009 09:37 |
smunir362
Messages: 310 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 #425991 is a reply to message #425989] |
Tue, 13 October 2009 10:00 |
smunir362
Messages: 310 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 #425995 is a reply to message #425985] |
Tue, 13 October 2009 10:41 |
cookiemonster
Messages: 13920 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 #426004 is a reply to message #426001] |
Tue, 13 October 2009 11:04 |
smunir362
Messages: 310 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 #426009 is a reply to message #425985] |
Tue, 13 October 2009 11:25 |
cookiemonster
Messages: 13920 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 #426159 is a reply to message #426004] |
Wed, 14 October 2009 04:12 |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
smunir362 wrote on Tue, 13 October 2009 11:04One 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 |
Frank
Messages: 7901 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 #426170 is a reply to message #426160] |
Wed, 14 October 2009 04:45 |
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 #426497 is a reply to message #425985] |
Fri, 16 October 2009 00:13 |
smunir362
Messages: 310 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 #426514 is a reply to message #426504] |
Fri, 16 October 2009 02:30 |
smunir362
Messages: 310 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 #427018 is a reply to message #426825] |
Tue, 20 October 2009 11:07 |
smunir362
Messages: 310 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 |
cookiemonster
Messages: 13920 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.
|
|
|
|
|
|
|