Oracle account password strength policy restrictions
Oracle account password strength policy restrictions
1. the user password must contain letters, data, and special characters.
(1) create a password check function
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: = '000000 ';
Chararray: = 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy ';
Punctarray: = '! "# $ % & () ''' * +,-/:; <=>? _';
-- 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 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
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 shoshould 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 shoshould 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 shoshould 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 IS NOT NULL THEN
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 shocould differ by \
Least 3 characters ');
End if;
End if;
End if;
-- Everything is fine; return TRUE;
RETURN (TRUE );
END;
/
(2) create a password check profile and apply it to business users
Create profile profile_pwd LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Alter user ndmc profile profile_pwd;
2. the user password must contain numbers and letters. special characters are not required.
(1) create a password check function
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;
Db_name varchar2 (40 );
Digitarray varchar2 (20 );
Punctarray varchar2 (25 );
Chararray varchar2 (52 );
I _char varchar2 (10 );
Simple_password varchar2 (10 );
Reverse_user varchar2 (32 );
BEGIN
Digitarray: = '000000 ';
Chararray: = 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy ';
-- Check for the minimum length of the password
IF length (password) <8 THEN
Raise_application_error (-20001, 'password length less than 8 ');
End if;
-- Check if the password is same as the username or username (1-100)
IF NLS_LOWER (password) = NLS_LOWER (username) THEN
Raise_application_error (-20002, 'password same as or similar to user ');
End if;
FOR I IN 1 .. 100 LOOP
I _char: = to_char (I );
If NLS_LOWER (username) | I _char = NLS_LOWER (password) THEN
Raise_application_error (-20005, 'password same as or similar to user name ');
End if;
End loop;
-- Check if the password is same as the username reversed
FOR I in REVERSE 1 .. length (username) LOOP
Reverse_user: = reverse_user | substr (username, I, 1 );
End loop;
IF NLS_LOWER (password) = NLS_LOWER (reverse_user) THEN
Raise_application_error (-20003, 'password same as username reversed ');
End if;
-- Check if the password is the same as server name and or servername (1-100)
Select name into db_name from sys. v $ database;
If NLS_LOWER (db_name) = NLS_LOWER (password) THEN
Raise_application_error (-20004, 'password same as or similar to server name ');
End if;
FOR I IN 1 .. 100 LOOP
I _char: = to_char (I );
If NLS_LOWER (db_name) | I _char = NLS_LOWER (password) THEN
Raise_application_error (-20005, 'password same as or similar to server name ');
End if;
End loop;
-- 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 ('welcome1', 'database1 ', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1 ', 'Change _ on_install ') THEN
Raise_application_error (-20006, 'password too simple ');
End if;
-- Check if the password is the same as oracle (1-100)
Simple_password: = 'oracle ';
FOR I IN 1 .. 100 LOOP
I _char: = to_char (I );
If simple_password | I _char = NLS_LOWER (password) THEN
Raise_application_error (-20007, 'password too simple ');
End if;
End loop;
-- Check if the password contains at least one letter, one digit
-- 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 (-20008, 'password must contain at least one digit, one character ');
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 endsearch;
End if;
End loop;
End loop;
IF ischar = FALSE THEN
Raise_application_error (-20009, 'password must contain at least one \
Digit, and one character ');
End if;
<Endsearch>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
Differ: = length (old_password)-length (password );
Differ: = abs (differ );
IF differ <3 THEN
IF length (password) <length (old_password) THEN
M: = length (password );
ELSE
M: = length (old_password );
End if;
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 (-20011, 'password shocould differ from \
Old password by at least 3 characters ');
End if;
End if;
End if;
-- Everything is fine; return TRUE;
RETURN (TRUE );
END;
/
(2) create a password check profile and apply it to business users
Create profile profile_pwd LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Alter user ndmc profile profile_pwd;
--------------------------------------------------------------------------------
Installing Oracle 12C in Linux-6-64
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian
Oracle 11g how to force password change ORA-28001
--------------------------------------------------------------------------------