ORACLE SQL PASSWORD FUNCTION

Question

Need to create a function that verifies a password (more than 5 characters, different from the previous one, it isn't neither "password" nor "123456") I tried this but i get errors

CREATE OR REPLACE FUNCTION my_verification_function (
    username VARCHAR2, 
    password VARCHAR2, 
    old_password VARCHAR2)
RETURN BOOLEAN IS 
BEGIN 
    IF LENGTH(password) < 6 THEN RETURN FALSE; 
    ELSE IF (password = "password" OR password = '123' OR password = old_password) THEN RETURN FALSE                                 
    ELSE RETURN TRUE;
    END IF;
END my_verification_function;$

OR password = ?? There is also no else if. See the manual for details: docs.oracle.com/cd/E11882_01/appdev.112/e25519/… and "password"`` refers to a column or variable. String constants need to be put in single quotes. — user330315
– user330315, Commented Apr 1, 2017 at 15:19
Why do you need to do this in database when it can directly be done at the UI/front-end level? — Techie
– Techie, Commented Apr 1, 2017 at 15:21

Oto Shavadze · Accepted Answer · 2017-04-01 15:30:14Z

2

CREATE OR REPLACE FUNCTION my_verification_function (
    username VARCHAR2, 
    pass VARCHAR2, 
    old_password VARCHAR2)
RETURN varchar2   IS 
BEGIN 
    IF LENGTH(pass) < 6 THEN RETURN 'FALSE'; 
    ELSIF (pass = 'password' OR pass = '123456' OR pass = old_password) THEN RETURN 'FALSE';
    ELSE RETURN 'TRUE';
    END IF;
END;

Several notes:

1) You should use ELSIF instead of ELSE IF

2) If you need call this function in "pure" SQL, BOOLEAN type will give you error, so you can use VARCHAR2 instead.

3) Don't use reserved words like PASSWORD

edited Apr 1, 2017 at 15:30

answered Apr 1, 2017 at 15:22

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LAXO Over a year ago

Thanks! I now need to create a PROFILE using this function, so i use PASSWORD_VERIFY_FUNCTION my_verification_function; But i keep getting this error: ORA-28004: invalid argument for function specified in my_verification_function

Oto Shavadze Over a year ago

I never created PROFILE, I don't have any experience in that way, sorry but I can't give any advice in this case

Wernfried Domscheit · Accepted Answer · 2017-04-01 17:35:24Z

2

It should be done like this:

CREATE OR REPLACE FUNCTION my_verification_function (
   username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
RETURN BOOLEAN AS
BEGIN

   IF LENGTH(password) < 6 THEN 
      RETURN FALSE; 
   ELSIF (password = "password" OR password = '123' OR password = old_password) THEN 
      RETURN FALSE                                 
   ELSE 
      RETURN TRUE;
   END IF;
END my_verification_function;
/

alter profile default limit password_verify_function my_verification_function;

Parameter name USERNAEM and PASSWORD seems to be no problem, since Oracle provided default function (located in ORACLE_BASE/ORACLE_HOME/RDBMS/ADMIN/UTLPWDMG.SQL) uses them also.

answered Apr 1, 2017 at 17:35

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

ORACLE SQL PASSWORD FUNCTION

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related