dbms_random exclude special characters-CodePudding

I have this code, and it is working. But the issue is that I need to exclude the rest of the special characters like '!"#$%&()''* ,-/:;<=>?_'; and I need only one special character in the password. So I just need Uppercase and lowercase and one of the 3 special characters (@$&)

the result of this like \v:BX$5{

The result I need like

N5$aKtw6 Km&r2xF5 sZ8Q@thy fH57$ymc

create or replace function p_random_string return varchar2 is
pwd varchar2(8);
begin
loop
pwd:=dbms_random.string('p',8);
exit when regexp_instr(pwd,'[a-z]')>0  -- at least one lower case letter
and regexp_instr(pwd,'[A-Z]')>0  -- at least one upper case letter
and regexp_instr(pwd,'[1-9]')>0  -- at least one number
and (instr(pwd,'@')>0 or instr(pwd,'$')>0 or instr(pwd,'&')>0) -- at least one spcecial char in a list
and instr(pwd,'O')=0 -- not O (o)
and instr(pwd,'L')=0 -- not L (l)
and instr(pwd,'I')=0 -- not I (i)
and instr(pwd,0)=0 ; -- not 0 (zero)
end loop;
return pwd;
end;
/

CodePudding user response：

A simple option is to translate offending characters to something else (e.g. into letters; see line #8):

SQL> CREATE OR REPLACE FUNCTION p_random_string
  2     RETURN VARCHAR2
  3  IS
  4     pwd  VARCHAR2 (8);
  5  BEGIN
  6     LOOP
  7        pwd := DBMS_RANDOM.string ('p', 8);
  8        pwd := TRANSLATE (pwd, '!"#$%&()''* ,-/:;<=>?_',
  9                               'abcdefghijklmnopqrstuv');
 10        EXIT WHEN     REGEXP_INSTR (pwd, '[a-z]') > 0 -- at least one lower case letter
 11                  AND REGEXP_INSTR (pwd, '[A-Z]') > 0 -- at least one upper case letter
 12                  AND REGEXP_INSTR (pwd, '[1-9]') > 0     -- at least one number
 13                  AND (   INSTR (pwd, '@') > 0
 14                       OR INSTR (pwd, '$') > 0
 15                       OR INSTR (pwd, '&') > 0) -- at least one spcecial char in a list
 16                  AND INSTR (pwd, 'O') = 0                          -- not O (o)
 17                  AND INSTR (pwd, 'L') = 0                          -- not L (l)
 18                  AND INSTR (pwd, 'I') = 0                          -- not I (i)
 19                  AND INSTR (pwd, 0) = 0;                        -- not 0 (zero)
 20     END LOOP;
 21
 22     RETURN pwd;
 23  END;
 24  /

Function created.

Testing:

SQL> SELECT p_random_string FROM DUAL;

P_RANDOM_STRING
--------------------------------------------------------------------------------
8H[KdY`@

SQL> SELECT p_random_string FROM DUAL;

P_RANDOM_STRING
--------------------------------------------------------------------------------
^@qPa6Q3

SQL> SELECT p_random_string FROM DUAL;

P_RANDOM_STRING
--------------------------------------------------------------------------------
sspV@q9E

SQL>

Though, looks like you'd want to remove some other "special" characters as well ...

CodePudding user response：

Rather than using DBMS_RANDOM.STRING, you can work on a string with allowed characters and then loop eight times to get the eight random character of the list.

After producing such a string, check the counts and exit when all count conditions are met.

CREATE OR REPLACE FUNCTION p_random_string RETURN VARCHAR2 IS
  v_allowed_chars VARCHAR2(100) := 'abcdefghijklmnopqrstuvwxyzABCDEFGHJKMNPQRSTUVWXYZ123456789$&@';
  v_pwd           VARCHAR2(8 CHARS);
  v_char          CHAR;
  v_rnd           INTEGER;
BEGIN
  LOOP
    v_pwd := '';
    FOR POS IN 1..8 LOOP
      v_rnd := ROUND(DBMS_RANDOM.value(1, LENGTH(v_allowed_chars)));
      v_char := SUBSTR(v_allowed_chars, v_rnd, 1);
      v_pwd := v_pwd || v_char;
    END LOOP;

    EXIT WHEN REGEXP_COUNT (v_pwd, '[a-z]') > 0   -- at least one lower case letter
          AND REGEXP_COUNT (v_pwd, '[A-Z]') > 0   -- at least one upper case letter
          AND REGEXP_COUNT (v_pwd, '[1-9]') > 0   -- at least one digit
          AND REGEXP_COUNT (v_pwd, '[\$@&]') = 1; -- exactly one spcecial char in a list
  END LOOP;

  RETURN v_pwd;
END p_random_string;
/