cancel
Showing results for 
Search instead for 
Did you mean: 

Password Verify Function

SOLVED
Level 2 Adventurer

Password Verify Function

Hi Folks,

             i want to set a profile with password verify function.so now i write code but it fails lot of time.

so please if anyone have the postgresql password verify function please send me this loops.

The Requirement is below:-

16 char(<16 char not accepted)
1 upper(minimum 1 Upper)
1 lower(minimum 1 Lower)
1 special(minimum 1 Special)
1 number(minimum 1 number)
password != usernaem (password not equal to Username)
cannot reuse previous 10 password (not used previous <10 passwords)

 

***************************ADVANCE THANKS**************************************

Thanks

Jithy.

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
EDB Team Member

Re: Password Verify Function

Hi Jithesh, 

 

Glad that function worked and met your requirements. 

 

Thank you. 

 

Have a nice day. 

Regards

Siva.

9 REPLIES 9
EDB Team Member

Re: Password Verify Function

Hi Jithesh, 

 

Hope you are doing good. 

 

I Kindly request you to please let us know which distrubution are you using. is it Postgres Community or EDB Postgres version, If EDB Postgres , Which version it is....?

 

Regards

Siva.

Level 2 Adventurer

Re: Password Verify Function

Hi @sivamekala ,

                         

                                        EDB (9.6.2.7) PostgreSQL .Thanks for your support.please reply the Program thanks.

 

 

Regards

Jithy

EDB Team Member

Re: Password Verify Function

Hi Jithesh, 

 

Thank you for details.

 

As per EDB 9.6, We can set "rules" through profiles. A profile is a small task which will execute on user when "alter/create user" commands fire. 

Default profile name in PPAS is "DEFAULT" which will assign to every user when user created by default. 

 

Example: Below is a small user defined function named "verify_password". 

----------------------------------

CREATE OR REPLACE FUNCTION sys.verify_password(user_name varchar2, new_password varchar2, old_password varchar2)
RETURN boolean IMMUTABLE
IS
BEGIN
IF (length(new_password) < 16)    --------------Minimun 16 is password length.
THEN
raise_application_error(-20001, 'too short');
END IF;


IF substring(new_password FROM old_password) IS NOT NULL
THEN
raise_application_error(-20002, 'includes old password');
END IF;


RETURN true;
END;
 

------------------------------

 

There will a parameter "PASSWORD_VERFIY_FUNCTION" in profile, you can set a function here which will call everytime when "create user/alter user " fires if you assign this profile to user. 

 

Here you go for procedure, But you have to write your own requirements in pl/sql function and that function needs to add to profile that profile should again assign to user. 

 

--------

edb=#
edb=# CREATE OR REPLACE FUNCTION sys.verify_password(user_name varchar2, new_password varchar2, old_password varchar2)
edb-# RETURN boolean IMMUTABLE
edb-# IS
edb$# BEGIN
edb$# IF (length(new_password) < 5)     -----i put password length is 5. 
edb$# THEN
edb$# raise_application_error(-20001, 'too short');
edb$# END IF;
edb$#
edb$# IF substring(new_password FROM old_password) IS NOT NULL
edb$# THEN
edb$# raise_application_error(-20002, 'includes old password');
edb$# END IF;
edb$#
edb$# RETURN true;
edb$# END;
CREATE FUNCTION
edb=# create profile pg_rocks;
CREATE PROFILE
edb=# alter profile pg_rocks limit password_verify_function verify_password ;
ALTER PROFILE
edb=# create user jithesh with password 'jithy' profile pg_rocks;
CREATE ROLE
edb=# create user jithesh1 with password 'jit' profile pg_rocks;
ERROR: EDB-20001: too short
CONTEXT: edb-spl function raise_application_error(numeric,text) line 14 at RAISE
edb-spl function verify_password(character varying,character varying,character varying) line 5 at procedure/function invocation statement
edb=# select version();
version
---------------------------------------------------------------------------------------------------------------
EnterpriseDB 9.6.9.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)

edb=#

 

------------------------------------------

 

Hope this help, But you need write a pl/sql code based on your requirment. (All rules you mentioned in first thread)

 

Thank you.

Regards

Siva.

 

Level 2 Adventurer

Re: Password Verify Function

Hi @sivamekala 

 

              I have completed the program as per my requirement and its working fine

              But now i was tring to d drop to my function  i cant able to do that.tried to if exists function also cannot ..

SO PLEASE ADVICE TO ME HOW  TO DROP MY FUNCTION..

 

see the example's:-

postgres=#
postgres=#
postgres=#
postgres=# select proname,proowner ,prolang from pg_proc where proname like '%password%';
proname | proowner | prolang
------------------------------+----------+---------
edb_get_password_expiry_date | 10 | 13
verify_password | 10 | 11672
verify_password_func | 10 | 11672
verify_password_func | 10 | 11672
verify_password_func | 10 | 11672
verify_password | 10 | 11672
(6 rows)

postgres=# drop function if exists sys.verify_password_func();
NOTICE: function sys.verify_password_func() does not exist, skipping
DROP FUNCTION
postgres=# drop function if exists sys.verify_password();
NOTICE: function sys.verify_password() does not exist, skipping
DROP FUNCTION
postgres=# drop function if exists sys.verify_password();
NOTICE: function sys.verify_password() does not exist, skipping
DROP FUNCTION
postgres=# drop function if exists sys.verify_password();
NOTICE: function sys.verify_password() does not exist, skipping
DROP FUNCTION
postgres=# drop function if exists sys.verify_password();
NOTICE: function sys.verify_password() does not exist, skipping
DROP FUNCTION
postgres=# drop function if exists sys.verify_password_func();
NOTICE: function sys.verify_password_func() does not exist, skipping
DROP FUNCTION
postgres=# drop function if exists sys.verify_password_func();
NOTICE: function sys.verify_password_func() does not exist, skipping
DROP FUNCTION
postgres=# drop function if exists sys.verify_password_func();
NOTICE: function sys.verify_password_func() does not exist, skipping
DROP FUNCTION
postgres=#
postgres=#
postgres=# select proname,proowner ,prolang from pg_proc where proname like '%password%';
proname | proowner | prolang
------------------------------+----------+---------
edb_get_password_expiry_date | 10 | 13
verify_password | 10 | 11672
verify_password_func | 10 | 11672
verify_password_func | 10 | 11672
verify_password_func | 10 | 11672
verify_password | 10 | 11672
(6 rows)

postgres=#

 

 

Regards

Jithy.

EDB Team Member

Re: Password Verify Function

Hi Jithesh, 

 

Thank you for testing, You need to follow method overloading mechanism. i mean you have to provide input parameters along with function name. So it can all exact target function rather than calling other function with same name. 

 

You can follow below if you want to drop function.

 

postgres=# CREATE OR REPLACE FUNCTION sys.verify_password(user_name varchar2, new_password varchar2, old_password varchar2) 

RETURN boolean IMMUTABLE 

IS 

BEGIN 

  IF (length(new_password) < 5) 

  THEN 

    raise_application_error(-20001, 'too short'); 

  END IF;

 

  IF substring(new_password FROM old_password) IS NOT NULL

  THEN 

    raise_application_error(-20002, 'includes old password'); 

  END IF;

 

  RETURN true; 

END; 

CREATE FUNCTION

postgres=# create profile last limit password_verify_function verify_password;

CREATE PROFILE

postgres=# drop function sys.verify_password(user_name varchar2, new_password varchar2, old_password varchar2) ;

ERROR:cannot drop verify_password because it is required by the profile. You cannot drop function if it is dependent other object.

postgres=# drop profile last;

DROP PROFILE

postgres=# drop function sys.verify_password(user_name varchar2, new_password varchar2, old_password varchar2) ;

DROP FUNCTION

postgres=# select version();

                                                    version                                                    

---------------------------------------------------------------------------------------------------------------

 EnterpriseDB 9.6.9.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit

(1 row)

 

postgres=# 

 

 

Hope this helps. Have a nice day

 

Regards

Siva.

 

Level 2 Adventurer

Re: Password Verify Function

@sivamekala 

 Many many thanks siva..can you help me check this program have any problem. Function created successfull but how to check the uppercase and lower case like the part.

i will highlighted the problem happen area.

 

Please run this program your side and try the below  password.

|LMi:Dni=B{%nT%H                                       ---------one
j2#@zgx;/5W#};G=                               -----------------two
_Pt'&>9JX?XHQz=2                    ------------------------Three

 

FYI:-

CREATE OR REPLACE FUNCTION password_verify
(usr_name varchar2, new_passwd varchar2, old_passwd varchar2)
RETURN boolean IMMUTABLE
IS
n boolean;
m integer;
differ integer;
isdigit boolean;
numdigit integer;
ispunct boolean;
numpunct integer;
islowchar boolean;
numlowchar integer;
isupchar boolean;
numupchar integer;
digitarray varchar2(10);
punctarray varchar2(25);
lowchararray varchar2(26);
upchararray varchar2(26);
pw_change_time date;
----------------
BEGIN
digitarray:='0123456789';
lowchararray:='abcdefghijklmnopqrstuvwxyz';
upchararray:='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='@!"#$%&()``*+,-/:;<=>?_';
----------------
IF (length(new_passwd) < 16)
THEN
raise_application_error(-20001, 'too short');
END IF;
-------------------------
IF nls_lower(password)=nls_lower(usr_name)                 -----------------------i think here the problem?
THEN
raise_application_error(-20002, 'Password same as or similar to user');
end if;
------------------------
IF substring(new_passwd FROM old_passwd) IS NOT NULL
THEN
raise_application_error(-20003, 'includes old password');
END IF;
-----------
isdigit:=FALSE;
numdigit:=0;
m:=length(password);
for i in 1..10 loop
for j in 1..m loop
if substr(password,j,1)=substr(digitarray,i,1) then
numdigit:=numdigit + 1;
end if;
if numdigit > 1 then
isdigit:=TRUE;
end if;
end loop;
end loop;
if isdigit=FALSE then
raise_application_error(-20004, 'Password should contain at least two digits');
end if;
--------------
islowchar:=FALSE;
numlowchar:=0;
m:=length(password);
for i in 1..length(lowchararray) loop
for j in 1..m loop
if substr(password,j,1)=substr(lowchararray,i,1) then
numlowchar:=numlowchar + 1;
end if;
if numlowchar > 1 then
islowchar:=TRUE;
end if;
end loop;
end loop;
if islowchar=FALSE then
raise_application_error(-20005, 'Password should contain at least two lowercase characters');
end if;
-----------------------
isupchar:=FALSE;
numupchar:=0;
m:=length(password);
for i in 1..length(upchararray) loop
for j in 1..m loop
if substr(password,j,1)=substr(upchararray,i,1) then
numupchar:=numupchar + 1;
end if;
if numupchar > 1 then
isupchar:=TRUE;
end if;
end loop;
end loop;
if isupchar=FALSE then
raise_application_error(-20006, 'Password should contain at least two uppercase characters');
end if;
---------------------------
ispunct:=FALSE;
numpunct:=0;
m:=length(password);
for i in 1..length(punctarray) loop
for j in 1..m loop
if substr(password,j,1)=substr(punctarray,i,1) then
numpunct:=numpunct + 1;
end if;
if numpunct > 1 then
ispunct:=TRUE;
end if;
end loop;
end loop;
if ispunct=FALSE then
raise_application_error(-20007, 'Password should contain at least two punctuation characters');
end if;
--------------------------
RETURN true;
END;

 

 

Regards

Jithy

 

 

EDB Team Member

Re: Password Verify Function

Hi Jithesh,

 

i see the code, i belive you did not declared variable "password" but you are using in funtion. Can you please use variable "new_password" (We already declared and it is 2nd input parameter) instead of using "password" 

 

previous: IF nls_lower(password)=nls_lower(usr_name) 

Now Ex: IF lower(new_passwd)=lower(usr_name)    ------ Please use lower function instead of nls_lower (please use this if you want to use National Language character set.)

 

Please Replace "password" with "new_password" in function whereever you used "password" variable. 

 

Hope this helps. 

Regards

Siva.

 

 

---------------You can see below for reference , i replaced password with new_password---------------------

CREATE OR REPLACE FUNCTION sys.password_verify
(usr_name varchar2, new_passwd varchar2, old_passwd varchar2)
RETURN boolean IMMUTABLE
IS
n boolean;
m integer;
differ integer;
isdigit boolean;
numdigit integer;
ispunct boolean;
numpunct integer;
islowchar boolean;
numlowchar integer;
isupchar boolean;
numupchar integer;
digitarray varchar2(10);
punctarray varchar2(25);
lowchararray varchar2(26);
upchararray varchar2(26);
pw_change_time date;

----------------
BEGIN
digitarray:='0123456789';
lowchararray:='abcdefghijklmnopqrstuvwxyz';
upchararray:='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='@!"#$%&()``*+,-/:;<=>?_';

----------------
IF (length(new_passwd) < 16)
THEN
raise_application_error(-20001, 'too short');
END IF;
-------------------------
IF lower(new_passwd)=lower(usr_name) -----------------------i think here the problem?
THEN
raise_application_error(-20002, 'Password same as or similar to user');
end if;
------------------------
IF substring(new_passwd FROM old_passwd) IS NOT NULL
THEN
raise_application_error(-20003, 'includes old password');
END IF;
-----------
isdigit:=FALSE;
numdigit:=0;
m:=length(new_passwd);
for i in 1..10 loop
for j in 1..m loop
if substr(new_passwd,j,1)=substr(digitarray,i,1) then
numdigit:=numdigit + 1;
end if;
if numdigit > 1 then
isdigit:=TRUE;
end if;
end loop;
end loop;
if isdigit=FALSE then
raise_application_error(-20004, 'Password should contain at least two digits');
end if;
--------------
islowchar:=FALSE;
numlowchar:=0;
m:=length(new_passwd);
for i in 1..length(lowchararray) loop
for j in 1..m loop
if substr(new_passwd,j,1)=substr(lowchararray,i,1) then
numlowchar:=numlowchar + 1;
end if;
if numlowchar > 1 then
islowchar:=TRUE;
end if;
end loop;
end loop;
if islowchar=FALSE then
raise_application_error(-20005, 'Password should contain at least two lowercase characters');
end if;
-----------------------
isupchar:=FALSE;
numupchar:=0;
m:=length(new_passwd);
for i in 1..length(upchararray) loop
for j in 1..m loop
if substr(new_passwd,j,1)=substr(upchararray,i,1) then
numupchar:=numupchar + 1;
end if;
if numupchar > 1 then
isupchar:=TRUE;
end if;
end loop;
end loop;
if isupchar=FALSE then
raise_application_error(-20006, 'Password should contain at least two uppercase characters');
end if;
---------------------------
ispunct:=FALSE;
numpunct:=0;
m:=length(new_passwd);
for i in 1..length(punctarray) loop
for j in 1..m loop
if substr(new_passwd,j,1)=substr(punctarray,i,1) then
numpunct:=numpunct + 1;
end if;
if numpunct > 1 then
ispunct:=TRUE;
end if;
end loop;
end loop;
if ispunct=FALSE then
raise_application_error(-20007, 'Password should contain at least two punctuation characters');
end if;
--------------------------
RETURN true;
END;

 

Level 2 Adventurer

Re: Password Verify Function

@sivamekala 

                          You are great indicator.thanks for your prompt action..Everything is fine and we did the password verify function very well..

 

THANKS FOR YOUR SUPPORT :smileyhappy  Smiley Very HappySmiley LOL

 

EDB Team Member

Re: Password Verify Function

Hi Jithesh, 

 

Glad that function worked and met your requirements. 

 

Thank you. 

 

Have a nice day. 

Regards

Siva.