Avatar of greatmich
greatmich asked on

utlpwdmg.sql Password should differ by at 3 characters doesn't work

I am using the password verification verify_function. But I cannot get the part that oracle provides  to check for  password differing from the previous password by at least 3 letters.

SQL> alter user test01 identified by shawn323;

User altered.

SQL> alter user test01 identified by shawn245;

User altered.

Am I not understanding this correctly?

-- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);
 
     IF abs(differ) < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;
 
       differ := abs(differ);
       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;
 
       IF differ < 3 THEN
         raise_application_error(-20004, 'Password should differ by at \
         least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);

Open in new window

Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
Sean Stuber

change the < 3
 to  <=3
 or
 to <4
ASKER
greatmich

I have tried that, and that does not work.
Sean Stuber

that code works for me...

below I've encapsulated it within a standalone function to test it...

select compare_pwd('shawn323','shawn245') from dual

(returns 1)

3 characters differ  (323 vs 245)  so the passwords are ok


CREATE OR REPLACE FUNCTION compare_pwd(
    password IN varchar2, old_password varchar2
)
    RETURN integer
IS
 
    differ              number;
    m                   number;
BEGIN
 
-- Check if the password differs from the previous password by at least
 
-- 3 letters
    IF old_password IS NOT NULL
    THEN
        differ := length(old_password) - length(password);
 
        IF abs(differ) < 3
        THEN
 
            IF length(password) < length(old_password)
            THEN
                m := length(password);
            ELSE
                m := length(old_password);
            END IF;
 
            differ := abs(differ);
 
            FOR i IN 1 .. m
            LOOP
 
                IF substr(password, i, 1) != substr(old_password, i, 1)
                THEN
                    differ := differ + 1;
                END IF;
 
            END LOOP;
 
            IF differ < 3
            THEN
                raise_application_error
                    (-20004, 'Password should differ by at  least 3 characters');
            END IF;
 
        END IF;
 
    END IF;
 
-- Everything is fine; return TRUE ;
    RETURN (1);
END
 
;

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
greatmich

what am I doing wrong, create public synonym and grant execute to public.

if compare_swr_pwd(password,old_password) > 1 THEN
     raise_application_error(-20004, 'Password should differ by at 4 characters');
   END IF;
SQL> alter user test01 identified by shawn1922;
alter user test01 identified by shawn1922
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-06503: PL/SQL: Function returned without value
Sean Stuber

what does your compare_swr_pwd function look like?

what does your entire verify function look like?
ASKER
greatmich

let me play with it, I am sure I am doing something stupid.
CREATE OR REPLACE FUNCTION compare_swr_pwd(
    password IN varchar2, old_password varchar2
)
    RETURN integer
IS
 
    differ              number;
    m                   number;
BEGIN
 
-- Check if the password differs from the previous password by at least
 
-- 3 letters
    IF old_password IS NOT NULL
    THEN
        differ := length(old_password) - length(password);
 
        IF abs(differ) < 3
        THEN
 
            IF length(password) < length(old_password)
            THEN
                m := length(password);
            ELSE
                m := length(old_password);
            END IF;
 
            differ := abs(differ);
 
            FOR i IN 1 .. m
            LOOP
 
                IF substr(password, i, 1) != substr(old_password, i, 1)
                THEN
                    differ := differ + 1;
                END IF;
 
            END LOOP;
 
            IF differ < 4
            THEN
                raise_application_error
                    (-20004, 'Password should differ by at  least 4 characters');
            END IF;
 
        END IF;
 
    END IF;
 
-- Everything is fine; return TRUE ;
    RETURN (1);
END
 
;
 
 if compare_swr_pwd(password,old_password) > 1 THEN
     raise_application_error(-20004, 'Password should differ by at 4 characters');
   END IF;

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
greatmich

I don't know. I can't get it to work. I am changing 1 character. My understanding is that it has to be different by 3.

SQL> alter user test01 identified by sh25234234;

User altered.

SQL> alter user test01 identified by sh25234235;

User altered.

Yes I put it in  a function and I can get it to return a one and get it to error out when I run via select from dual,

SQL> select compare_swr_pwd('shawn323','shawn325') from dual;
select compare_swr_pwd('shawn323','shawn325') from dual
       *
ERROR at line 1:
ORA-20004: Password should differ by at  least 4 characters
ORA-06512: at "BANINST1.COMPARE_SWR_PWD", line 42


It all begins here new_password=shawn323 old_password=shawn325
It differed less than three differ is =0
differ in loop is =1


 but when I try to call that function in my verify function it says it doesn't return anything.
SQL> alter user test01 identified by shawn930;
alter user test01 identified by shawn930
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-06503: PL/SQL: Function returned without value



This is my whole verify function. This has the original code for the password must be different by three characters. Of course I have changed other things in it. 
 
CREATE OR REPLACE FUNCTION SYS.verify_swr_function
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   two_digit integer;
   differ integer;
   isdigit boolean;
   leadingdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
 
BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_';
 
   -- Check if the password is same as the username
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20001, 'Password same as or similar to user');
   END IF;
 
   -- Check for the minimum length of the password
   IF length(password) < 7 THEN
      raise_application_error(-20002, 'Password length less than 7');
   END IF;
 
   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, 'Password too simple');
   END IF;
 
   -- Check if the password contains at least two numeric digits
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   two_digit:=0;
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            two_digit := two_digit + 1;
            if two_digit = 2 THEN
                isdigit:=TRUE;
            END IF;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least two numeric digits');
   END IF;
   ---------------------------------------------
   --- The following was added by Shawn Raymond
   --------------------------------------------
   leadingdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
         IF substr(password,1,1) = substr(digitarray,i,1) THEN
             leadingdigit:=TRUE;
         END IF;
   END LOOP;
   IF leadingdigit = TRUE THEN
      raise_application_error(-20003, 'Password cannot start with numeric value');
   END IF;
   ----------------------------
   ----- added by Shawn Raymond
   ----------------------------
   if f_swr_repeat_check(password,3) > 0 THEN
      raise_application_error(-20003, 'Password contains too many characters that are the same');
   END IF;
   --------------------------------
   ------ end of shawn like changed
   --------------------------------
--  if compare_swr_pwd(password,old_password) > 1 THEN
--     raise_application_error(-20004, 'Password should differ by at 5 characters');
 -- END IF;
 
    IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);
 
     IF abs(differ) < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;
     
       differ := abs(differ);
       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;
 
       IF differ < 3 THEN
         raise_application_error(-20004, 'Password should differ by at \
         least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/

Open in new window

Sean Stuber

try this instead....

rather than letting the underlying function raise, let it return a value that you can explicitly check for.
CREATE OR REPLACE FUNCTION compare_swr_pwd(
    password IN varchar2, old_password varchar2)
    RETURN integer
IS
 
    differ              number;
    m                   number;
    ok                  integer := 1;
BEGIN
 
-- Check if the password differs from the previous password by at least
 
-- 3 letters
    IF old_password IS NOT NULL
    THEN
        differ := length(old_password) - length(password);
 
        IF abs(differ) < 3
        THEN
 
            IF length(password) < length(old_password)
            THEN
                m := length(password);
            ELSE
                m := length(old_password);
            END IF;
 
            differ := abs(differ);
 
            FOR i IN 1 .. m
            LOOP
 
                IF substr(password, i, 1) != substr(old_password, i, 1)
                THEN
                    differ := differ + 1;
                END IF;
 
            END LOOP;
 
            IF differ < 4
            THEN
                ok := 0;
            END IF;
 
        END IF;
 
    END IF;
 
    RETURN ok;
END;

Open in new window

ASKER
greatmich

Still not working for me. Your code if I return a "1" then we are good

if compare_swr_pwd(password,old_password) <> 1 THEN
    raise_application_error(-20004, 'Password should differ by at 4 characters');
END IF;

and I have tried

if compare_swr_pwd(password,old_password) = 0 THEN
    raise_application_error(-20004, 'Password should differ by at 4 characters');
 END IF;

SQL> alter user test01 identified by lksjdflsj3322;

User altered.
SQL> alter user test01 identified by lksjdflsj3321;

User altered.
SQL> alter user test01 identified by lksjdflsj3320;

User altered.


if I put something nutty in there like <> 9 I can make it trip

SQL> alter user test01 identified by lksjdflsj3324;
alter user test01 identified by lksjdflsj3324
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20004: Password should differ by at 4 characters

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
greatmich

I put the original original original code back in for utlpwdmg.sql  Actually copied from another machine that I have not touched,
and it still doesn't &$*(#$* work.  I know it is the original because it is making me put in a punctuation.

SQL> alter user test01 identified by lksjd1$3;

User altered.

SQL> alter user test01 identified by lksjd1$4;

User altered.
ASKER
greatmich

This is what is in for the verify function, ignore the alter at the I did not run that.


utlpwdmg.txt
Sean Stuber

I'm getting NULL passed in as old_password!  

Add these 4 lines immediately after the BEGIN of the verify function...

    IF old_password IS NULL
    THEN
        raise_application_error(-20001, 'Old password is null');
    END IF;

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
greatmich

In Verify_function or compare_swr_pwd?
Sean Stuber

in verify_function
ASKER
greatmich

SQL> alter user test01 identified by lksjd1$5;
alter user test01 identified by lksjd1$5
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Old password is null

I put it in right at the beginning of Verify function, how do you like those apples
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sean Stuber

found it,  it's documented,  "sort of"  :)

From 8i Admin Guide...


Note: Oracle recommends that you do not change passwords
using the ALTER USER statement because it does not fully support
the password verification function. Instead, you should use
OCIPasswordChange() to change passwords.


From 9i Admin Guide.

Note: Oracle recommends that you do not change passwords
using the ALTER USER statement because it does not fully support
the password verification function. Instead, you should use
LNOCIPasswordChange() to change passwords.


From 10g Security Guide...

Note: The ALTER USER command now has a REPLACE clause by
using which users can change their own unexpired passwords by
supplying the old password to authenticate themselves.
If the password has expired, then the user cannot log in to SQL to
issue the ALTER USER command. Instead, the
OCIPasswordChange() function must be used, which also
requires the old password.
A DBA with ALTER ANY USER privilege can alter any user
password (force a new password) without supplying the old one.


From 11g Security Guide...

Note:
The ALTER USER statement has a REPLACE clause. With this clause, users can change their own unexpired passwords by supplying the old password to authenticate themselves.
If the password has expired, then the user cannot log in to SQL to issue the ALTER USER command. Instead, the OCIPasswordChange() function must be used, which also requires the old password.
A database administrator with ALTER ANY USER privilege can change any user password (force a new password) without supplying the old one.


ASKER
greatmich

But what about if you use the exact password, it does check for old passwords within the last year, must be using some other function for that?

man was I ever led astray by thinking that the code in the file might actual work.   Ok I am 10.2.0.3. I have to figure out this OCIPasswordChange function. The issue is that The ERP system is the one that is doing the changing of passwords, and I would have to figure out if what the heck they are are using. I was using Alter user just to test.

So what do you think you and I finished? you have been absolutely excellent with me.   I think this has morphed into something else and now we know that old_password is not being passed in.

Any last thoughts?

ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
greatmich

He worked with me, and worked with me, and worked with me, until we got it solved. It was not the answer I wanted to hear but it was still very rewarding working with sdstuber. Excellent, Excellent job
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

glad I could help!