DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: urgent procedure

Hybrid View

  1. #1
    Join Date
    May 2002
    Posts
    232
    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

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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;
    /

  3. #3
    Join Date
    May 2002
    Posts
    232
    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

  4. #4
    Join Date
    May 2002
    Posts
    232
    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

  5. #5
    Join Date
    Apr 2002
    Posts
    135
    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

  6. #6
    Join Date
    May 2002
    Posts
    232
    thanks sona.
    kavitha

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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"".)

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    this is a duplicate post

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width