OraFAQ Forum: SQL & PL/SQL » Custom password verify function

Home » SQL & PL/SQL » SQL & PL/SQL » Custom password verify function (Oracle DB 12c,19c, Exadata RHEL 7.9)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Custom password verify function [message #687627]

Thu, 20 April 2023 18:43

Nagesh1985
Messages: 10
Registered: April 2016

Junior Member

I have a custom password verify function to enforce password policies to my DB users. There is a part in the code that I need help with. A snippet from the code is enclosed below

if not ora_complexity_check(password, chars => 8, upper => 1, lower => 1, digit => 1, special => 1) then
return(false);

As per the code, it checks for the password complexity, where the code checks for a password which should contain a number AND special character.
My requirement is, the code should check for a password which should contain a number OR a special character.
It should be a 'OR' and not an 'AND'.

Attaching the full function as well.

Attachment: verify_pwd_general.txt
(Size: 2.27KB, Downloaded 1697 times)

Report message to a moderator

Re: Custom password verify function [message #687628 is a reply to message #687627]

Thu, 20 April 2023 23:47

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Try this instead. It assumes that you still want at least 8 characters with at least 1 upper case and at least 1 lower case, but only require either at least 1 digit or at least 1 special character, where a special character is anything other than a digit or letter or space.

  if not ora_complexity_check(password, chars => 8, upper => 1, lower => 1, digit => 0, special => 0)
    or not (regexp_like(password, '[0-9]+') or regexp_like(password, '[^0-9a-zA-Z ]+'))
  then
    return(false);

[Updated on: Thu, 20 April 2023 23:52]

Report message to a moderator

Re: Custom password verify function [message #687629 is a reply to message #687628]

Fri, 21 April 2023 00:24

Nagesh1985
Messages: 10
Registered: April 2016

Junior Member

Thanks Barbara, that worked !!

Report message to a moderator

Re: Custom password verify function [message #687632 is a reply to message #687629]

Fri, 21 April 2023 06:28

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Nagesh1985 wrote on Fri, 21 April 2023 01:24

Thanks Barbara, that worked !!

Well, not exactly:

declare
    v_password varchar2(20) := unistr('ABCdef\00e5\00f1\00f6');
begin
    dbms_output.put('Password "' || v_password || '" ');
    if    not sys.ora_complexity_check(v_password, chars => 8, uppercase => 1, lowercase => 1, digit => 0, special => 0)
       or not (regexp_like(v_password, '[0-9]+') or regexp_like(v_password, '[^0-9a-zA-Z ]+'))
      then
        dbms_output.put_line('failed complexity check.');
      else
        dbms_output.put_line('passed complexity check.');
    end if;
end;
/
Password "ABCdefåñö" passed complexity check.

PL/SQL procedure successfully completed.

SQL>

As you can see password has no digits and no special characters but it passed complexity test. Use alnum:

declare
    v_password varchar2(20) := unistr('ABCdef\00e5\00f1\00f6');
begin
    dbms_output.put('Password "' || v_password || '" ');
    if    not sys.ora_complexity_check(v_password, chars => 8, uppercase => 1, lowercase => 1, digit => 0, special => 0)
       or not (regexp_like(v_password, '\d') or regexp_like(v_password, '[^[:alnum:] ]'))
      then
        dbms_output.put_line('failed complexity check.');
      else
        dbms_output.put_line('passed complexity check.');
    end if;
end;
/
Password "ABCdefåñö" failed complexity check.

PL/SQL procedure successfully completed.

SQL>

SY.

Report message to a moderator

Re: Custom password verify function [message #687633 is a reply to message #687632]

Fri, 21 April 2023 11:00

mathguy
Messages: 108
Registered: January 2023

Senior Member

I would do something like this, mirroring your requirement word for word:

if not ( ora_complexity_check(password, chars => 8, upper => 1, lower => 1, digit => 1)
         or
         ora_complexity_check(password, chars => 8, upper => 1, lower => 1, special => 1)
       )
then
  return(false);
...........................

It's checking a few things twice, but I assume "speed" is your least concern with this. People shouldn't be allowed to change passwords too often, so this can't be a frequently used process.

Report message to a moderator

Re: Custom password verify function [message #687634 is a reply to message #687633]

Fri, 21 April 2023 14:04

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

This will not work since ora_complexity_check raises ORA-20000 if complexity isn't met:

declare
    v_password varchar2(20) := 'ABC##def';
begin
    dbms_output.put('Password "' || v_password || '" ');
    if ( sys.ora_complexity_check(v_password, chars => 8, uppercase => 1, lowercase => 1, digit => 1)
         or
         sys.ora_complexity_check(v_password, chars => 8, uppercase => 1, lowercase => 1, special => 1)
       )
      then
        dbms_output.put_line('failed complexity check.');
      else
        dbms_output.put_line('passed complexity check.');
    end if;
end;
/
declare
*
ERROR at line 1:
ORA-20000: password must contain 1 or more digits
ORA-06512: at "SYS.ORA_COMPLEXITY_CHECK", line 87
ORA-06512: at line 5


SQL>

SY.

Report message to a moderator

Re: Custom password verify function [message #687635 is a reply to message #687634]

Fri, 21 April 2023 18:21

mathguy
Messages: 108
Registered: January 2023

Senior Member

Quote:

ora_complexity_check raises ORA-20000 if complexity isn't met:

Brilliant! I hadn't looked too closely into this function; in any case, this means that the OP's code wouldn't work either, and for the same reason. (His code is faulty anyway - for example using parameter names upper and lower when the function parameters are uppercase and lowercase, etc.)

This means that errors at different checkpoints must be caught and handled to meet the OP's needs. One way is like this:

create or replace function verify_password (password varchar2)
  return boolean
as
  check_failed exception;
  pragma       exception_init(check_failed, -20000);
  b            boolean;
begin
  b := sys.ora_complexity_check(password, chars => 8, uppercase => 1, lowercase => 1);
  begin
    b := sys.ora_complexity_check(password, digit => 1);
  exception
    when check_failed then
      begin
        b := sys.ora_complexity_check(password, special => 1);
      exception
        when check_failed then
          raise_application_error(-20000, 'password must contain 1 or more digits OR 1 or more special characters');
      end;
  end;
  return true;
end;
/

The function will return TRUE if the password satisfies the OP's condition. It will return the "usual" error (and error message) when the password is too short, or it doesn't include at least one uppercase and one lowercase letter, and it will return ORA-20000 with the message "password must contain 1 or more digits OR 1 or more special characters" when that condition isn't met.

Here I took the view that the function is a black box and we want to leverage all its power. As I understand it, the function code is exposed so that users can modify it themselves; I ignored that approach on principle.

Here's one way to confirm what the function does:

declare
  check_failed exception;
  pragma       exception_init(check_failed, -20000);
  passwords    sys.odcivarchar2list;
begin
  passwords := sys.odcivarchar2list
               ( 'abc'
               , '0123456789'
               , 'ABC456789'
               , 'abcdEFGH'
               , 'aaBB23456'
               , 'abcDE....'
               , 'abCD3+45'
               );
  for i in 1 .. passwords.count loop
    if i > 1 then dbms_output.put_line(chr(10)); end if;
    dbms_output.put_line('Password sample: ' || passwords(i));
    dbms_output.put_line('Verification:');
    begin
      if verify_password(passwords(i)) then
        dbms_output.put_line('PASSED');
      end if;
    exception
      when check_failed then
        dbms_output.put_line(sqlerrm);
    end;
  end loop;
end;
/

PL/SQL procedure successfully completed.

Password sample: abc
Verification:
ORA-20000: password length less than 8 bytes


Password sample: 0123456789
Verification:
ORA-20000: password must contain 1 or more uppercase characters


Password sample: ABC456789
Verification:
ORA-20000: password must contain 1 or more lowercase characters


Password sample: abcdEFGH
Verification:
ORA-20000: password must contain 1 or more digits OR 1 or more special characters


Password sample: aaBB23456
Verification:
PASSED


Password sample: abcDE....
Verification:
PASSED


Password sample: abCD3+45
Verification:
PASSED


PL/SQL procedure successfully completed.

Report message to a moderator

Re: Custom password verify function [message #687637 is a reply to message #687635]

Sat, 22 April 2023 06:18

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, OP's code would work. OP just needs to change dbms_output in code I posted with raise_application_error:

declare
    v_password varchar2(20) := unistr('ABCdef\00e5\00f1\00f6');
begin
    dbms_output.put('Password "' || v_password || '" ');
    if    not sys.ora_complexity_check(v_password, chars => 8, uppercase => 1, lowercase => 1, digit => 0, special => 0)
       or not (regexp_like(v_password, '\d') or regexp_like(v_password, '[^[:alnum:] ]'))
      then
--        dbms_output.put_line('failed complexity check.');
          raise_application_error(-20000,'Password must contain at least 1 digit or special character.');
      else
        dbms_output.put_line('passed complexity check.');
    end if;
end;
/
declare
*
ERROR at line 1:
ORA-20000: Password must contain at least 1 digit or special character.
ORA-06512: at line 9


SQL>

SY.

Report message to a moderator

Previous Topic:	SQL Query help
Next Topic:	LOB Locator

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 25 00:05:54 CDT 2025