DBA Tips Archive for Oracle

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC

The following SQL script can be used to create an example Oracle profile used for password security.


connect sys/manager as sysdba Connected. CREATE OR REPLACE FUNCTION verify_user_password ( username VARCHAR2 , password VARCHAR2 , old_password VARCHAR2) RETURN boolean IS passwordMinLength INTEGER; passwordLength INTEGER; differ INTEGER; differMinLength INTEGER; isDigit BOOLEAN; isChar BOOLEAN; isPunct BOOLEAN; digitArray VARCHAR2(20); punctArray VARCHAR2(25); charArray VARCHAR2(52); BEGIN digitArray := '0123456789'; charArray := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; punctArray := '!"#$%&()``*+,-/:;<=>?_'; passwordMinLength := 7; differMinLength := 3; passwordLength := LENGTH(password); isDigit := FALSE; isChar := FALSE; isPunct := FALSE; -- +------------------------------------------------+ -- | Check if the password is same as the username | -- +------------------------------------------------+ IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20001, 'Password same as or similar to user'); END IF; -- +-------------------------------------------------+ -- | Check that password is more than [x] characters | -- | in length. | -- +-------------------------------------------------+ IF (LENGTH(password) < passwordMinLength) THEN raise_application_error(-20002, 'Password must be greater than ' || passwordMinLength || ' characters.'); 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(-20003, '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 | -- +-----------------------------------------------------+ FOR i IN 1..10 LOOP FOR j IN 1..passwordLength 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(-20004, 'Password should contain at least ' || ' one digit,' || ' one character and' || ' one punctuation'); END IF; -- +-----------------------------------------------------+ -- | (2.) Check for the character | -- +-----------------------------------------------------+ <<findchar>> FOR i IN 1..LENGTH(charArray) LOOP FOR j IN 1..passwordLength 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(-20004, 'Password should contain at least ' || ' one digit,' || ' one character and' || ' one punctuation'); END IF; -- +-----------------------------------------------------+ -- | (3.) Check for the punctuation | -- +-----------------------------------------------------+ <<findpunct>> FOR i IN 1..LENGTH(punctArray) LOOP FOR j IN 1..passwordLength 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(-20004, 'Password should contain at least ' || ' one digit,' || ' one character and' || ' one punctuation'); END IF; <<endsearch>> -- +-----------------------------------------------------+ -- | Check that the new password is not null. | -- +-----------------------------------------------------+ IF old_password = '' THEN raise_application_error(-20005, 'Old password is null'); END IF; -- +-----------------------------------------------------+ -- | Check if the password differs from the previous | -- | password by at least [x] letters. | -- +-----------------------------------------------------+ differ := ABS(LENGTH(old_password) - LENGTH(password)); IF differ < differMinLength THEN IF LENGTH(password) < LENGTH(old_password) THEN passwordLength := LENGTH(password); ELSE passwordLength := LENGTH(old_password); END IF; FOR i IN 1..passwordLength LOOP IF SUBSTR(password,i,1) != SUBSTR(old_password,i,1) THEN differ := differ + 1; END IF; END LOOP; IF differ < differMinLength THEN raise_application_error(-20006, 'Password should differ by at least ' || differMinLength || ' characters.'); END IF; END IF; -- +-----------------------------------------------------+ -- | Well, looks like we passed all of the requirements. | -- | Return 'true'. | -- +-----------------------------------------------------+ RETURN(true); END; / Function created. CREATE PROFILE "USER_PROFILE" LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME 5 PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 120 PASSWORD_VERIFY_FUNCTION verify_user_password / Profile created.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 04-Sep-2012 00:24:54 EDT
Page Count: 37872