-
CREATE TABLE MOBUSER(
MOBNO VARCHAR2(20) PRIMARY KEY,
PASSWORD VARCHAR2(20),
STATUS VARCHAR2(1));
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.
MODIFIED TABLE
CREATE TABLE MOBUSER(
MOBNO VARCHAR2(20) PRIMARY KEY,
PASSWORD VARCHAR2(20),
STATUS VARCHAR2(1),
PASSWORD1 NUMBER(4));
here password and password1 are nulls for new users registration.
many thanks,i need this procedure soon,
any one can send me soon.
thanks
kavitha
kavitha
-
This is one of possible ways:
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
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
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;
mob_pwd:= substr(to_char(n),1,4) ;
mob_status:='NEW';
/* 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"".)
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|