[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle 1-g Password Verify script

Posted on 2009-04-21
7
Medium Priority
?
1,238 Views
Last Modified: 2012-06-27
Experts,

       I created a Password Verify function by entering the following:
connect to each db instance as
sqlplus /nolog
sql>conn / as sysdba
sql> @c:\PwdVerify.sql
however I get numbers returned, such as
116 <enter>
117<enter>
Why is this happening? I got this code directly from the Oracle 10g install (utlpwdmg.sql)
Here's my code:
 
-- Complexity Checks performed
-- 1. The password satisfies minimum length requirements
-- 2. The password is not the username
 
 
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 is same as the username 
IF password = username THEN 
   raise_application_error(-20001, 'Password same as user'); 
END IF; 
 
--Check for the minimum length of the password 
IF length(password) < 4 THEN 
   raise_application_error(-20002, 'Password length less than 4'); 
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 
--You may delete 1. and replace with 2. or 3.
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;

Open in new window

0
Comment
Question by:missymadi
  • 4
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24211010
can you attach your pwdverify.sql  file?

maybe you have some extra non-printable characters or something in it.

the function you posted above compiled fine for me
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24211031
or,  your file is missing a /


and the 116 and 117 are line numbers as sqlplus prompts you for more input to end the function creation

try this...


sqlplus /nolog
sql>conn / as sysdba
sql> @c:\PwdVerify.sql
/

and then press <Enter> after you type the "/"  character
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24211095
also, for determining what zones to post to,  if you have a tag that could be a zone,  post to that zone.

For instance,  you have "Oracle 11g" as a tag,  "Oracle 11.x"  would be a good zone to post to.
0
 

Author Comment

by:missymadi
ID: 24215934
Thanks! I will test this out in the lab today.
How do I verify that the function was created? Is there a select statement that will return functions?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24215982
select * from dba_objects where object_type = 'FUNCTION'
and object_name = ''VERIFY_FUNCTION';

to see the code itself...

select * from dba_source where name = 'VERIFY_FUNCTION';


add owner clauses if you happen to have multiple schemas that have a verify_function
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month20 days, 12 hours left to enroll

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question