This table contains 1lack records.
here user passwords contain alpha numeric.
we like to change all old users passwords
and new users as 4 digit numeric password.
So,I need a stord procedure by check the old passwords when
they login and convert that one into numeric.
details of stored procedure
inputparameter mobno
outputparameter : password
: status
if the password not existed(new users) for a particuler user, new random 4 digit password
will be returned.
if the password existed and in the form of alpha-numeric, new random 4 digit
password will be returned.
if 4 digit password existed for that user, initial password will be returned.
create or replace
function pwd_change(p_MOBNO varchar2) return boolean is
rm MOBUSER%rowtype;
n number;
begin
begin
select * into rm from MOBUSER where mobno = p_mobno;
exception when others then
-- some error handling
return false;
end;
if rm.password is null then
--
-- work with new user
-- (n new digit password)
--
n := dbms_utility.get_hash_value( p_MOBNO || to_char(sysdate), 8999, power(2,30)) + 1000;
update MOBUSER set password = to_char(n),
password1 = to_char(n),
status = 'NEW'
where mobno = p_mobno;
else
n := dbms_utility.get_hash_value( rm.password || to_char(sysdate), 8999, power(2,30)) + 1000;
update MOBUSER set password1 = to_char(n),
status = 'CHANGE'
where mobno = p_mobno;
end if;
return true;
end;
/
create or replace
trigger cng_password
after logon on database
begin
if user not in ('SYS', 'SYSTEM') then
if not pwd_change(user) then
-- this in not modno user
null;
else
-- this in mobno user (password changed)
null;
end if;
end;
/
hiii,thanks
we have mobile website.
So all users password Authentication through web to database.
But they are asking Stored procedure?
Is it possible?
plss reply .
thanks
kavitha
Heloooo,my company people want modifications by using stored procedure only.
So it can possible?
if yes,
how? any one can help me? my developers waiting for this procedure.
thanks
kavitha
Quite a few changes in Shestakov 's post will get you the required.
create or replace procedure pwd_change(p_MOBNO varchar2 in, mob_pwd out,mob_status out )
as
rm MOBUSER%rowtype;
n number(20);
begin
begin
select * into rm from MOBUSER where mobno = p_mobno;
exception when others then
-- some error handling
return false;
end;
if rm.password is null then
--
-- work with new user
-- (n new digit password)
--
n := dbms_utility.get_hash_value( p_MOBNO || to_char(sysdate), 8999, power(2,30)) + 1000;
/*both the password fields for the newuser will be filled with the new password*/
update MOBUSER set password = substr(to_char(n),1,4)
password1 = substr(to_char(n),1,4) ,
status = 'NEW' where mobno = p_mobno;
/* i do know what exactly the status is if required change it*/
else
if length(rtrim(rm.password))>4 then
n := dbms_utility.get_hash_value( rm.password || to_char(sysdate), 8999, power(2,30)) + 1000;
update MOBUSER set password1 = substr(to_char(n),1,4)
status = 'CHANGE' where mobno = p_mobno;
/*if you want update the same new password for firstpassword field also or else the old password will be maintained inthe first password field*/
set mob_pwd: =substr(to_char(n),1,4) ;
set status='CHANGE';
end if;
if length(rtrim(rm.password))=4 then
set mob_pwd: =rm.password;/*same password is returned*/
set status='NoCHANGE';
end if
end if
end;
Good Judgement comes from Experience.
Experience comes from Bad Judgement
I had written this function around midnight and fogot some comments:
1. about db security:
U must grant permitions select and UPDATE to table MOBUSER all users,
who work with this application !!! BUT THIS IS TABLE WITH OPEN PASSWORDS !!!
Do u sure, that u want it?
2. this procedure/function sould have :
PRAGMA AUTONONIMOUS_TRANSACTION and
sould have commit statment.
=============================================
create or replace procedure pwd_change(p_MOBNO varchar2 in, mob_pwd out,mob_status out )
as
rm MOBUSER%rowtype;
n number(20);
PRAGMA AUTONONIMOUS_TRANSACTION;
begin
...
...
commit;
...
end;
/
=============================================
3. about changing by sona:
update MOBUSER set password = substr(to_char(n),1,4)
password1 = substr(to_char(n),1,4) ,
status = 'NEW' where mobno = p_mobno;
substr(to_char(n),1,4) <-- absolutly not need because:
n := dbms_utility.get_hash_value( p_MOBNO || to_char(sysdate), 8999, power(2,30)) + 1000;
generate exactly 4 digit key between 1001 and 9999,
may be we have to change power(2,30) to --> power(2,31) - 1
this koefficient can generate little bit more random sequence of numbers.
(see D. Knut "Art of programming Vol 2. "Random sequences"".)
Reply With Quote
Bookmarks