CODEWITHSUNDEEP
sqloracle-database
LAXO
LAXO

Reputation: 59

ORACLE SQL PASSWORD FUNCTION

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;$

Upvotes: 1

Views: 1229

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

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.

Upvotes: 2

Oto Shavadze
Oto Shavadze

Reputation: 42793

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

Upvotes: 2

Related Questions