Home » RDBMS Server » Security » password verify function (Oracle 11gr2 linux 6.4)
password verify function [message #604143] Tue, 24 December 2013 16:23 Go to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Team,

1. I am using below password verify function "VERIFY_FUNCTION" in my profile "privilged_profile".max. password length allowed is 10 char. But it accpets less than 10 char when i create new user / alter the existing password.

2. Below are the complexity i would like to enforce:

/* Password Complexity:
Minimum password length: 10 characters - not working
Allowed : 0 -9 ( Numerics ) - this works
Allowed : a-z & A-Z ( Alphabets ) - this works
Allowed : [ !#$`*+,-:;<=>?_ ] ( special characters ) - accepts only #, $, _ ( rest i need to enclose "" it seems )
New password must differ by atleast 3 characters of old password - not working
Password should not be same as username - this working
Password should not be reused - this working
*/

could you please guide me on the "not working parts in the above list ?

-- below is VERIFY_FUNCTION CONTENT

CREATE OR REPLACE FUNCTION VERIFY_FUNCTION   (username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);

BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_';

   -- Check if the password contains username
   IF NLS_LOWER(password) like '%'||NLS_LOWER(username)||'%' THEN
     raise_application_error(-20001, 'Password contains username');
   END IF;

   -- Check for the minimum length of the password
   IF length(password) < 10 THEN
      raise_application_error(-20002, 'Password length less than 10');
   END IF;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, 'Password too simple');
   END IF;

   -- Check if the password contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   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
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;
   -- 3. Check for the punctuation
   <<findpunct>>
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;

   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password = '' THEN
      raise_application_error(-20004, 'Old password is null');
   END IF;
   -- Everything is fine; return TRUE ;
   differ := length(old_password) - length(password);

   IF abs(differ) < 3 THEN
      IF length(password) < length(old_password) THEN
         m := length(password);
      ELSE
         m := length(old_password);
      END IF;
      differ := abs(differ);
      FOR i IN 1..m LOOP
          IF substr(password,i,1) != substr(old_password,i,1) THEN
             differ := differ + 1;
          END IF;
      END LOOP;
      IF differ < 3 THEN
          raise_application_error(-20004, 'Password should differ by at \
            least 3 characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/



---profile details:


CREATE PROFILE PRIVILEGED_PROFILE LIMIT
    COMPOSITE_LIMIT UNLIMITED
    CONNECT_TIME UNLIMITED
    CPU_PER_CALL UNLIMITED
    CPU_PER_SESSION UNLIMITED
    FAILED_LOGIN_ATTEMPTS 5
    IDLE_TIME UNLIMITED
    LOGICAL_READS_PER_CALL UNLIMITED
    LOGICAL_READS_PER_SESSION UNLIMITED
    PASSWORD_GRACE_TIME 3
    PASSWORD_LIFE_TIME  90
    PASSWORD_LOCK_TIME 1
    PASSWORD_REUSE_MAX UNLIMITED
    PASSWORD_REUSE_TIME 365
    SESSIONS_PER_USER UNLIMITED
    PRIVATE_SGA UNLIMITED
    PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION
/


SQL> alter user test_user1 profile PRIVILEGED_PROFILE;

User altered.



SQL> alter user test_user1 identified by maa3#;

User altered.

SQL>




whenever i alter the existing password or create a new user, the password which iam trying to set must be scanned by this verify function. will it not happen ?

as per the profile setting, password will get expire in 90 days. once expired, then it will ask us to enter the new password.
this verify function will work only by this time or ?

i did expire the password for this user account manaully. H'ever iam able to set the password less than 10 char.

SQL> alter user TEST_USER1 password expire;

User altered.


C:\Users\user101>sqlplus test_user1/vik12#@testdb

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 24 16:41:29 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-28001: the password has expired


Changing password for test_user1 
New password: *****
Retype new password:
Password changed

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "TEST_USER1"



Please guide me. i need to have a password function which should check when create user / alter the existing user.

Thank you
kesavan

[Updated on: Tue, 24 December 2013 16:26]

Report message to a moderator

Re: password verify function [message #604146 is a reply to message #604143] Tue, 24 December 2013 20:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
a simple test may be appropriate.
deploy test function that only RAISES User Exception ALWAYS
what results when this function is supposedly in place?

I am going to post a number of questions that may or may not be relevant or get you closer to solving your mystery.

>C:\Users\user101>sqlplus test_user1/vik12#@testdb
are you sure that the same & only 1 DB is involved?
which user owns the VERIFY_FUNCTION?
does the user invoking the function have EXECUTE privilege on this function.
was COMMIT issued at appropriate time?
what proof exists that PRIVILEGED_PROFILE LIMIT profile exists with desired values?
what proof exists that the function ever gets invoked & started?
does any EXCEPTION handler code exist? if so, please post it.

I would rather ask lazy questions than try to replicate what you are attempting to create.
Re: password verify function [message #604871 is a reply to message #604143] Sun, 05 January 2014 00:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I tested your function and the minimum 10 characters and minimum 3 character difference seem to be working. Can you provide examples of combinations of new password and old password that cause it to not work properly?
Re: password verify function [message #605703 is a reply to message #604871] Mon, 13 January 2014 13:36 Go to previous message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Hi Barbara,

I am sorry for late reply. Yes, I was wrong previously. Considering secuirty, Oracle has put a restriction that password verify function must be created only by SYS, and it will not accept if we try to create by any other db user other than SYS.

I was checking this on Amazon Oralce RDS instance previously and hence not able to achieve. I tried the same in a physical db env, and I am able to achieve that. I am able to get error if password length is set to < 10 char.

Thank you very much Barbara for your time

Thank you all

kesavan
Previous Topic: McAfee Database Security for 'Data Loss Prevention (DLP)'
Next Topic: how to grant role using EXECUTE IMMEDIATE
Goto Forum:
  


Current Time: Thu Mar 28 03:52:32 CDT 2024