Oracle account password strength policy restrictions

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

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