How to create Oracle password verify function.
How To Create Oracle Password Verify Function.
1. create password_verify function by sys
CREATE OR REPLACE FUNCTION toa_pass_verify (
username VARCHAR2,
PASSWORD VARCHAR2,
old_password VARCHAR2
)
RETURN BOOLEAN
IS
n BOOLEAN;
m INTEGER;
differ INTEGER;
isdigit BOOLEAN;
ischarlower BOOLEAN;
ischarupper BOOLEAN;
ispunct BOOLEAN;
digitarray VARCHAR2 (20);
punctarray VARCHAR2 (25);
chararraylower VARCHAR2 (52);
chararrayupper VARCHAR2 (52);
BEGIN
digitarray := '0123456789';
chararraylower := 'abcdefghijklmnopqrstuvwxyz';
chararrayupper := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
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) < 8
THEN
raise_application_error (-20002, 'Password length less than 8');
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 findcharlower;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE
THEN
raise_application_error
(-20003,
'Password should contain at least one digit'
);
END IF;
-- 2. Check for the lowwer case character
ischarlower := FALSE;
FOR i IN 1 .. LENGTH (chararraylower)
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (chararraylower, i, 1)
THEN
ischarlower := TRUE;
--GOTO findcharupper;
END IF;
END LOOP;
END LOOP;
IF ischarlower = FALSE
THEN
raise_application_error
(-20003,
'Password should contain at least one lower case character and one punctuation'
);
END IF;
-- 3. Check for the upper case character
ischarupper := FALSE;
FOR i IN 1 .. LENGTH (chararrayupper)
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (chararrayupper, i, 1)
THEN
ischarupper := TRUE;
--GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischarupper = FALSE
THEN
raise_application_error
(-20003,
'Password should contain at least one upper character and one punctuation'
);
END IF;
-- 4. Check for the punctuation
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;
-- Everything is fine; return TRUE ;
RETURN (TRUE);
END;
/
2. create profile:
CREATE PROFILE MY_PROFILE LIMIT
FAILED_LOGIN_ATTEMPTS 3 -- Account locked after 3 failed logins.
PASSWORD_LOCK_TIME unlimited -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
PASSWORD_LIFE_TIME 60 -- Password expires after 90 days.
PASSWORD_GRACE_TIME 6 -- Grace period for password expiration.
PASSWORD_REUSE_TIME 360 -- Number of days until a specific password can be reused. UNLIMITED means never.
PASSWORD_REUSE_MAX 6 -- The number of changes required before a password can be reused. UNLIMITED means never.
PASSWORD_VERIFY_FUNCTION PASSWORD_VERIFY
3. create users:
create user TEST
identified by password4TEST#
profile MY_PROFILE
CREATE OR REPLACE FUNCTION toa_pass_verify (
username VARCHAR2,
PASSWORD VARCHAR2,
old_password VARCHAR2
)
RETURN BOOLEAN
IS
n BOOLEAN;
m INTEGER;
differ INTEGER;
isdigit BOOLEAN;
ischarlower BOOLEAN;
ischarupper BOOLEAN;
ispunct BOOLEAN;
digitarray VARCHAR2 (20);
punctarray VARCHAR2 (25);
chararraylower VARCHAR2 (52);
chararrayupper VARCHAR2 (52);
BEGIN
digitarray := '0123456789';
chararraylower := 'abcdefghijklmnopqrstuvwxyz';
chararrayupper := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
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) < 8
THEN
raise_application_error (-20002, 'Password length less than 8');
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 findcharlower;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE
THEN
raise_application_error
(-20003,
'Password should contain at least one digit'
);
END IF;
-- 2. Check for the lowwer case character
ischarlower := FALSE;
FOR i IN 1 .. LENGTH (chararraylower)
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (chararraylower, i, 1)
THEN
ischarlower := TRUE;
--GOTO findcharupper;
END IF;
END LOOP;
END LOOP;
IF ischarlower = FALSE
THEN
raise_application_error
(-20003,
'Password should contain at least one lower case character and one punctuation'
);
END IF;
-- 3. Check for the upper case character
ischarupper := FALSE;
FOR i IN 1 .. LENGTH (chararrayupper)
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (chararrayupper, i, 1)
THEN
ischarupper := TRUE;
--GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischarupper = FALSE
THEN
raise_application_error
(-20003,
'Password should contain at least one upper character and one punctuation'
);
END IF;
-- 4. Check for the punctuation
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;
-- Everything is fine; return TRUE ;
RETURN (TRUE);
END;
/
2. create profile:
CREATE PROFILE MY_PROFILE LIMIT
FAILED_LOGIN_ATTEMPTS 3 -- Account locked after 3 failed logins.
PASSWORD_LOCK_TIME unlimited -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
PASSWORD_LIFE_TIME 60 -- Password expires after 90 days.
PASSWORD_GRACE_TIME 6 -- Grace period for password expiration.
PASSWORD_REUSE_TIME 360 -- Number of days until a specific password can be reused. UNLIMITED means never.
PASSWORD_REUSE_MAX 6 -- The number of changes required before a password can be reused. UNLIMITED means never.
PASSWORD_VERIFY_FUNCTION PASSWORD_VERIFY
3. create users:
create user TEST
identified by password4TEST#
profile MY_PROFILE
